Browsed by
Month: January 2013

Using SSAS dimension data with different labels

Using SSAS dimension data with different labels

In relation to my article about using date pickers to filter SSRS cube data, I have been asked a few times how it would be possible to use one set of parameters to select the date range that points to dimension data from different cubes.
For example, I have a report that pulls data from two different cubes. In the one cube, the main time dimension is called “Date”, and in the other cube there is a time dimension that is calle “Value Date”.

Basically there are two ways to accomplish this. The first one is exactly how I described in my previous post about this topic. For each date dimension you would have to create two extra parameters and use the expression as I mentioned. In the example above, you would have to create four parameters:

Parameter: DateFrom
Default value: =”[Date].[Date].&[” & Format(Parameters!FromDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

Parameter: DateTo
Default value: =”[Date].[Date].&[” & Format(Parameters!ToDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

Parameter: ValueDateFrom
Default value: =”[Value Date].[Date].&[” & Format(Parameters!FromDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

Parameter: ValueDateTo
Default value: =”[Value Date].[Date].&[” & Format(Parameters!ToDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

The second way (I don’t particularly see on beneficial way) is to use the Replace-function. As you can see above, the difference between the “DateFrom” parameter and the “ValueDateFrom” parameter is only in the first couple of characters. As a default value for the “ValueDateFrom” parameter, I also could have put this:

=Replace(Parameters!DateFrom.Value, “[Date].[Date]”, “[Value Date].[Date]”)

One little side note: When you add the date dimensions from different cubes in your report and let SSRS just create the parameters for you, you don’t have to create those parameters additionally. Make sure though that in the above example, there would have been two datasets for the available values for the ValueDateFrom and ValueDateTo parameters. Since we don’t see these parameters in the report anyway, you can set the available values to None and delete the datasets.