When dealing with report parameters that involve time, I usually like to default the report to the current unit of time used by the report, often month. When a user views the report in a particular month the parameter will always default to that month, saving the user from having to select it every time.
There are two elements to making this happen. One is to define the current month in your Time dimension, and the other is to build the dataset into your report to feed the default for the Time parameters. 1. Define current month in the SSAS Time dimension
I know there is Time functionality built into SSAS cubes, but I still like the flexibility of building attributes into my DIM_Date table in the data warehouse. In this way it can be used in both cube and SQL queries alike. I won’t go into details about how the Dim_Date table is created as that is another subject. The DIM_Date table is rebuilt nightly and an attribute called MonthPeriod identifies whether the date is in Current Month, Previous Month, etc. You can label any months you like, with your own descriptions. Some examples are below.
The important thing about this attribute is to ensure that MonthPeriodID for the current month is always 0, and the MonthPeriodID for other months is relative to that. So ‘Current Month –1’ is 1, ‘Current Month –2’ will be 2, etc. This way you can build logic into your reports which relies on the fact that, for example, last month has a MonthPeriodID of 1. It is critically important that the underlying ids belonging to each description are always the same. If Current Month is 0 today and changes to 10 tomorrow, the defaults in your reports will not work. SSRS stores the MDX tuple associated with the description and not the actual description. So when you select ‘Current Month’ in step 3 below, SSRS is storing [TIME].[Month Period].&[0] and not the ‘Current Month’ label.
Once you have this attribute defined in your DIM_Date table and it is being updated nightly, add it to the SSAS cube for use in your reports.
2. Build the default dataset into your report to feed the SSRS Parameter.
Background:
When you build the main dataset for the report and add parameters in your dataset,
SSRS automatically creates the dataset for the Available Values for the parameter. You can view these hidden datasets used to feed Available Values by right clicking on [Datasets] in the [Report Data] window in SSRS and selecting [Show Hidden Datasets].
You can view and/or edit any of these hidden datasets.
I don’t recommend editing the hidden datasets, since they are created and used by SSRS by default and your changes can sometimes be overwritten if you add additional datasets using the same parameters. If you wish to customize the Available Values you are better off creating a new dataset with a different name specific to that purpose and then changing Report Parameter Properties to use the new dataset to retrieve the Available Values.
Building the Default Dataset
For this particular report the user can choose Year and then Month Of Year. The same dataset can be used to feed both the default current year and the current month.1. Right click on Datasets in the Report Data window and select Add Dataset.
2. Select the Radio Button ‘Use a dataset embedded in my report’. I like to use Shared Datasets for this type of thing, but for some reason shared datasets used for defaults with an SSAS data source don’t seem to work when published to SharePoint. So for now just embed it in the report. Select (or define) the cube you are using as your Data source, and click on the Query Designer button.
3. From the TIME dimension drag the ‘Month Period’ attribute defined in Part 1 to the filter area of the Query Designer. Click in the Filter Expression and select ‘Current Month’. Click OK.
4. Drag Year and Month of Year onto the Query design page. Right click on the design page and select ‘Include Empty Cells’ so the current attributes will appear when the query is executed.
5. Right click in the Calculated Members area and select ‘New Calculated Member’. From the Metadata pane drag the Year attribute from the TIME dimension into the Expression pane. Add to the end of the expression “.CurrentMember.UniqueName”. Give the Calculated Member a name of ‘YearValue’. Click OK.
6. Right click on the new calculated member called YearValue and select Add to Query.
7. Add another calculated member following steps 5 & 6 called MonthOfYearValue.
8. Your dataset now contains the Label and the underlying MDX tuple needed to feed the default time values to your report parameter.
9. Click OK to close out of the Query Designer. Give the dataset a name of DefaultYearMonth. Click OK to save the dataset.
10. Double click the TIMEYear parameter in the Paramaters folder of the Report Data window to edit the Report Parameter Properties. Select the Default Values option. Choose the ‘Get values from a query’ radio button. In the dataset drop down choose the DefaultYearMonth dataset you created. In the Value field dropdown select YearValue.
11. Do the same as Step 10 for the TIMEMonthOfYear parameter. Select DefaultYearMonth as the dataset, and MonthOfYearValue for the Value field.
When you preview your report, the parameters will now default to the current year and month. Going forward the default for the parameter will roll over to whichever month is current. Users appreciate not having to choose the current month every time they look at a report.
No comments:
Post a Comment