Jan 182012
 

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.

image

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,

image

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]. 

image

You can view and/or edit any of these hidden datasets.

image

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.

image

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.

image

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.

image

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.

image

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.

image

6. Right click on the new calculated member called YearValue and select Add to Query.

image

7.  Add another calculated member following steps 5 & 6 called MonthOfYearValue. 

 

image

8. Your dataset now contains the Label and the underlying MDX tuple needed to feed the default time values to your report parameter.

image

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.

image

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.

image

  7 Responses to “How To Default SSRS Parameter to Current Month using an SSAS Data Source”

  1. I found this really useful and very well explained but cannot get to work when the date is in a hierachy. Bit new to SSRS!!

    • Hi Chris,

      I’m not sure where your trouble lies. Does your main report data set return results when you include the date from the hierarchy? Does your main dataset have a Dimension Filter parameter on a particular time period? If yes, when you get to the part in the blog post where you set up the Default Dataset, part 4 of this section be sure that the columns you are returning are from the same Dimension/Hierarchy as you are using in your Dimension Filter parameter for the main dataset. For example, if you main dataset is filtering on Dimension: Time, Hierarchy: Month of Year, then be sure your Default Dataset for that parameter also includes the items from the same hierarchy. The parameter will be expecting the value of the underlying MDX to be from the same hierarchy. It’s not ready “2013 January”, it’s reading [TIME].[Month of Year].&[1] (for example).

      I hope that helps.

      Cheers,
      Martina

  2. How do I select month as “December” by default in the above example? In my case, I have two dropdowns From and To which will display the same dropdown list. The need is to display January in From dropdown which will be possible as the default selection is always the first one & The To dropdown should be selected “December” by default. Any help is much appreciated. Thanks.

    • It is similar to the example in this post. Instead of Current Month, you can build two defaults, one for January and one for December. I don’t know your dimensions or attributes, but if you look at point 8 in this post, I would set my defaults to [TIME].[Month Of Year].&[1] for January and [TIME].[Month Of Year].&[12] for December.
      Cheers,
      Martina

  3. Worked Great! Thanks

  4. I’m having an issue with my MDX defaults. The month and year defaults are being set properly, but the default for the week is not being set.

    If you could provide some insight that would be great! What would you need from me along the lines of scripts, etc.?

  5. For getting the current month as the Default parameter, we just have to follow the above steps except “Define current month in the SSAS Time dimension” Step.
    We can get the current or latest member of the dimension through “[Month].[Month Year].LASTCHILD” and then follow the remaining steps.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

Wordpress SEO Plugin by SEOPressor