Calvin Ferns' Blog

KPI’s in Power View and DAX Query for KPI’s

I was recently approached by a client where we have assisted in implementing a Microsoft Analysis Services tabular solution and a supporting management pack of SSRS reports.  They were asking about utilising some of the KPI’s defined in their tabular cube in the SSRS reports using DAX queries.  I was at a bit of a loss and most web help seemed to suggest it couldn’t be done:

Blog comment from 2012

However, with a bit of time to investigate it appears Power View does now support Tabular KPI’s and as such a DAX query should be able to pull them out. 


My KPI base measure is named “Highest Ranking” and I am able to view its value, status or goal in Power View.  A little work with every SQL developer’s best friend Profiler and I had what I was looking for.  It appears that when you create a KPI SSAS is defining some measures in the background which do some of the work of the MDX KPI functions.

Therefore the following DAX query against my cycling Demo cube contains the expected results and could therefore be used in SSRS reporting.



"Highest_Ranking", 'FactRanking'[Highest Ranking],

"Highest_Ranking_Goal", 'FactRanking'[_Highest Ranking Goal],

"Highest_Ranking_Status", 'FactRanking'[_Highest Ranking Status]


ORDER BY 'DimRider'[Name]


I also tested for the existence of other KPI functions such as Trend and Weight but these do not appear to be present.  It is also interesting that the use of a KPI over a measure does not change the measure name but just group it in the field list and as such there is no need for a value function.


For more info on KPI’s in Power View there is additional documentation here.  I am currently unsure of the required versions for this functionality so if it is missing from your environment I would love to hear from you.

SSRS Language parameter using SSAS Translations


SSAS translations are known to deal with warehousing language requirements well.  We recently had a requirement for a multi-language report suite on top of a user browsed cube. First thought was SSAS translations, these will pick up the users local language settings and allow an elegant solution to the problem. 

However a requirement was put forward for a language picker in the SSRS report suite to over-ride the users locale if they so desire.  This was not so obvious.

Data source

We started with a list of languages to build a picker from, this was built by importing the table available here to a SQL Server and building it as a dataset in our report:

It is important to bring through the decimal value in column 4 and the short string in column 2 as we will see later.

Parameter Build

Use the dataset on MSDN to build a language select parameter.  Use the language name in the label field and the decimal locale identifier in the value field.  The setup should look something like this:


Default value setup

If you just need English as the default use the UK code 2057 as your default value, however a more elegant final solution is a little more complex. 

Reporting services provides the built in variable User!Language which holds the users language in the short string ‘en-gb’ style format.  So although we can access the users language it needs translating into a localeId before we can pass it to SSAS.  Fortunately for us the same table we used above can be used to translate between short string and locale.

I achieved this using a further hidden parameter defaulted to the built in User!Language variable, the parameter was set up as detailed below:


Using the dataset where LanguageCode = short string in our source table


If this parameter is run first (it must be earlier in the report parameters list) it will contain the users language in the form ‘en-gb’ in the value and ‘2057’ in the label field.  It is then simple to set the default of the first dropdown parameter to the value of the hidden parameter’s label using the following syntax.


I am open to suggestions of a way to get the default without a hidden parameter or a large switch statement so if you find something more elegant please let me know!  It is also worth noting that if a user has a language that your source table does not have an entry for you are obviously going to lose the defaulting behaviour. 

Parameter Ordering

It is essential to make sure that the parameters are ordered in your parameter list in the sequence they need to be evaluated in.  In this case the hidden user language parameter needs to be first, followed by the visible language drop down parameter.  Only then should other report parameters be added.

Passing Parameter value to SSAS

In order for SSAS to provide us the data in the language requested we need to pass the locale to analysis services.  We can do this by over-riding the locale in the connection string.  Go to your report data source and double click for properties.  Then we need to edit the connection string expression as circled.


Here we need to make sure our connection to SSAS is provided with the locale from the parameter.  We use the following expression to build our connections string.

="Data Source=localhost;Initial Catalog=" & CHR(34) & "Adventure Works DW 2008R2" & CHR(34) & ";Locale Identifier=" & Parameters!Language.Value

(CHR(34) is the “ character that we wanted to escape properly.)

This builds the string detailed below for the parameter selection Spanish.

=Data Source=localhost;Initial Catalog="Adventure Works DW 2008R2";Locale Identifier=3082

This should now preview and allow you to select the language and your data sets will return any translations you have present in the cube.


If you are using a shared data source reference this is not going to work for you as it is only possible to expression a data source held in the report.  In this case perhaps you can use a solution along the lines of the one detailed by Mosha here:

Another warning is to finish your report design first because you cant refresh datasets and metadata once the expression is set

Tested on 2008R2 only.


Hope this helps everyone and allows you to select your existing translations in a flexible way through SSRS.  Your comments are welcome.