Measure Selection in SSRS and SSAS


Pivoting around the measure improves report usability by adding an additional dimension that can be utilised in reports. A common example of this is a trend report that allows the user to select the measure that they wish to see. There are a few ways to accomplish this in your solution in this post looks at some of them. The methods examined are as a static parameter within the report definition, as a flexible parameter within the report definition, and finally, as a utility dimension.

In this example, we will look at pivoting a monthly trend report around two measures (actual and budget). The output for our reports will look like a screenshot below. Notice that the measure is selectable by the user.

In these reports we have created a measure parameter (named @measure_name) with two possible values as follows;

Parameter Value Parameter Label
=“[Measures].[Native Amt ACT]” Native Amt ACT
=“[Measures].[Native Amt BGT]” Native Amt BGT

Note that the value of the parameter should be included as an expression(otherwise an error will occur).

Static Parameters

Static Parameters transform pivot the data in the data set result. All possible measures I return by the query, and the dataset determines the pivotal value based on a formula. Therefore, in the base query looks like the one below, notice that both [Native Amt ACT] and [Native Amt BGT] are returned;

with
member measures.month_key as
[Dates].[Fin Years].currentmember.member_key
select
{
measures.month_key
,[Measures].[Native Amt ACT]
, [Measures].[Native Amt BGT]
} on 0,
strtomember(@year).children
on 1
from ….

Our data set includes an additional, added field that determines what measure to show (as an iif a function)

=iif(Parameters!measure_name.Label=“Native Amt ACT”
, Fields!Native_Amt_ACT.Value
,Fields!Native_Amt_BGT.Value
   )

In order to have the data set fields visible in the expression editor of the query must be saved and then reopened. Of course our report pivots around the Measure_Val amount.

Flexible Parameters

Flexible parameters are transformed within the query, that is, the parameter is defined as a member (measure) within the query. That is, at query now converts the parameter value @measure_name to a measure which is defined in the query.

with
member measures.month_key as
[Dates].[Fin Years].currentmember.member_key
member measures.measure_val as
strtomember(@measure_name)
select
{
measures.month_key
, Measures.measure_val
} on 0,
strtomember(@year).children
on 1
from
The @measure_name parameter must be included in the query parameters (as below);

Utility Dimensions

Utility dimensions are probably best known for their use in time intelligence and the by-passing the SSAS wizard for time intelligence (see here). Generally, unsophisticated users find individual measures relatively easy to use in their reports or pivots because it allows them to select a measure without thinking about the ‘time dimension’. You can simply stack measures and create calculations by referring to individual measures rather than the tuples created by a measure and a dimension combination.

A measure utility dimension can be created in the following manner;

1. Add a table to the database (or view with data) to produce the desired characteristics (ie, those measure names that we want to allow users to select). As an advanced option, we can also include additional properties to allow the MDX query to return a subset of dimension members.

2. Add the table to the DSV

3. Create a dimension based on the table so that the measure_name field appear as captions for members. There may be a logical case for setting the ‘IsAggregatable’ property to false and set a default member however, there is no need to do so because the user will select the member (or measure value) that they require and the default value for the Measure_Val measure is null.

4. Add the measure (Measure_Val) to cube and scope the intersects for selected [Reporting Measures] definitions.

create
member  currentcube.Measures.[Measure_Val] as null
, visible=0;
scope(Measures.[ Measure_Val]);
scope([Reporting Measures].[Measure Name].&[Actual TM]);
 this = [Measures].[Native Amt ACT];
end scope;
scope( [Reporting Measures].[Measure Name].&[Budget TM]);
 this=[Measures].[Native Amt BGT];
end scope;
end scope;

Now in the report is created, the [Reporting Measures] dimension can be used as a parameter in the report. In fact, we can specify the member values as the code for the ‘measure’ parameter selection. In this case, our @measure_name would have the available value set to query, on the query would return the members of the [Reporting Measures] dimension

with

member [measures].[measure_label] as

[Reporting Measures].[Measure Name].member_caption

member [measures].[measure_value] as
[Reporting Measures].[Measure Name].member_unique_name
select
{
[measures].[measure_label]
, [measures].[measure_value]
} on 0,
[Reporting Measures].[Measure Name].[Measure Name] on 1
From …

The report data set uses the [reporting measures] dimension is a parameter in the query is changed to use the dimension as below. Note that in this situation the Measures.Measure_Val member is a measure within the cube and not a definition within the MDX.

with
member measures.month_key as
[Dates].[Fin Years].currentmember.member_key
select
{
measures.month_key
, Measures.measure_val
} on 0,
strtomember(@year).children
on 1
from
where
( strtomember(@measure_name)
, …
)

The method of creating utility dimension is quite flexible for the SSRS query designer because all values and dimensions can be selected from the within the SSRS query designer without the need to write MDX.

Conclusion

There are a number of options for allowing the user to select measures their reports. The method chosen may depend on the uses ability right custom MDX and the need to duplicate measure selection throughout the reports (or the reporting environment). Additionally the method chosen may depend on the rate of change for the ‘reporting measure’ selection . When utility dimension is used, production versions the cube can easily hide the measure [measure_value] and [Reporting Measure] dimension in this may remove confusion the cube is browsed ad hoc manner.

About these ads

One thought on “Measure Selection in SSRS and SSAS

  1. Pingback: Measure Selection in SSRS and SSAS | Microsoft ...

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s