Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Calendar Date Picker for MDX based SQL Server Reporting Services Reports


By:   |   Read Comments (4)   |   Related Tips: > Reporting Services Analysis Services

Attend a SQL Server Conference for FREE >> click to learn more


Problem

Most SSRS reports contain at least one date parameter and users prefer to use a Calendar Date Picker. It is very easy to use a calendar date picker when we create a report against a relational database, but when we create a SSRS report against SQL Server Analysis Services then we can't directly feed the Calendar Date Picker value into a MDX query. This tip will demonstrate how to feed a calendar date picker value into a MDX query, how to use a calendar date picker for MDX based reports and the advantages of a calendar date picker over a list of values.

Solution

This tip assumes that you have previous experience building a simple SSRS report against an Analysis Services Data Source. In this tip we will first look at the problem with a simple example and later I will demonstrate the steps to solve the problem.

Lets understand the problem with a simple example

Step 1

I am creating a simple SSRS report against MDXStepByStep SQL Server Analysis Services database. I have already created the data source connection for the MDXStepByStep database. After creating the data source connection I am creating my dataset which gives product Sales Amount information at the day level. My dataset contains one Date Parameter; you can refer to the below image.

Report DataSet

Step 2

At the time of the dataset creation if you declare a query parameter then SSRS automatically creates the report parameter along with its parameter dataset. As you can see from the below image @OrderDateDate report parameter has been created automatically. By default all Parameter datasets are hidden, if you want to see Hidden Datasets then right click on Datasets and click on "Show Hidden Datasets".

Report Parameter

Once you click on "Show Hidden Datasets", all hidden datasets will be visible. As you can see from the below image dataset OrderDateDate is now visible.

Report Parameter Hidden Dataset

Step 3

I am inserting a Tablix for data viewing. I have chosen all the data fields from DataSet1 in the Tablix. After this modification my report looks like the below image.

Tablix in Report

Now if I preview the report, I get all the date values in a parameter dropdown as a list of values. All these date values are coming from the OrderDateDate dataset. You can refer to the below image.

List Of Values For Date Parameter

If I choose July 1, 2001 from the parameter list and click on "View Report". The image shown below is the output of the report for July 1, 2001 parameter value.

Report Output using List Of Values Parameter

Using a Calendar Date Picker for the Parameter Value

Now I want to use a Calendar Date Picker in place of this list of values. You might be thinking why do I need a Calendar Date Picker if a list of values already fills the requirement. Actually there are couples of advantages of using calendar date picker:

  • Report execution time will be faster; this is because the Calendar Date Picker parameter doesn't need a separate dataset to get date values from the database. Executing a separate query to get all date values from the database is an extra burden on SSRS. The Calendar Date Picker is a SSRS built-in feature which gives all date values without hitting your database.
  • Navigation will be very easy. You can easily select any date from the calendar date picker, but you struggle to find a particular date from the list of values.
  • It can save a lot of time for the user.

The following steps will show how you can set this up.

Step 1

Right click on @OrderDateDate report parameter and click on "Parameter Properties", it will open a Report Parameter Properties window. In the General tab change the parameter Data Type to Date/Time from Text and make sure "Allow Null Value" and "Allow Multiple Values" check boxes are unchecked. Your changes should look like the below image.

Report Parameter Properties General Tab

Step 2

Now click on the Available Values tab; here you can find that for the @OrderDateDate parameter, values are coming from the OrderDateDate dataset. You can refer to the below image.

Report Parameter Properties Available Values Tab

Choose None as shown below and click OK.

Report Parameter Properties Available Values Tab After Changes

Step 3

Right click on the OrderDateDate dataset and click on "Query". It will open the query designer window. We need to examine the OrderDateDate dataset MDX query. Let's execute the MDX query by clicking on the Execute button. On successful execution your MDX Query will return 4 columns. You can refer to the below image.

Query Designer Window For OrderDateDate Dataset

If you look at the Query execution result then you will notice that the ParameterCaption column data was used as a Label Field for the @OrderDateDate parameter and the ParameterValue column data was used as the Value Field for the @OrderDateDate parameter. You can confirm this from Step 2. When I selected July 1, 2001 as the parameter value behind the scenes the [Order Date].[Date].&[20010701] was passed into the MDX Query.

At this stage if you preview the report for July 1, 2001 parameter value then it will throw an error. This is because the parameter value 7/1/2001 12:00:20 AM will be passed into the MDX query. If we convert the parameter value in this [Order Date].[Date].&[20010701] format then the MDX query will accept the parameter value and the report will execute successfully. So our ultimate task is to generate the parameter value in MDX format. In the next step I will build the expression which will generate the parameter value in MDX format.

Step 4

Right click on Dataset1 and click on "Dataset Properties". It will open the Dataset Properties window. Click on the Parameters tab and click on the Parameter Value Expression button. You can refer to the below image.

Dataset Properties Window

Once you click on the Parameter Value Expression button, it will open an Expression window. Here we have to build an expression that will be generate the parameter value in MDX format, so my ultimate task is to get the parameter value in this format [Order Date].[Date].&[20010701].

The below Expression will return the parameter value in the required MDX format. In the below expression I am using format(Parameters!OrderDateDate.Value,"yyyyMMdd") which will return a value like 20010701. This expression may differ in your case, so make the appropriate changes in your expression.

="[Order Date].[Date].&["+format(Parameters!OrderDateDate.Value,"yyyyMMdd")+"]"

My Expression window looks like the below image.

Expression Window

Step 5

We have done all required changes, let's preview the report. I will select July 1, 2001 from the calendar date picker and click on "View Report". You can refer to the below image.

Report with Calendar Date Picker Parameter

Congratulations! Now the report is using a Calendar Date Picker parameter. You can verify the results from the below image.

Report Output using Calendar Date Picker

Step 6

You can now delete the OrderDateDate dataset because it is not required anymore.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, May 13, 2017 - 5:51:03 AM - Ankur Khaparde Back To Top

Hi Thanks for the tips!

it is very helpfull but i am getting an error saying "String can not be converted to the date type" 

 


Saturday, January 07, 2017 - 7:33:53 AM - Serg Back To Top

Thank you very much!

It is a good document, it is really convenient!


Thursday, July 02, 2015 - 11:13:09 AM - Ghanesh Back To Top

Thank you Liza for your comments.

Regards,

Ghanesh


Wednesday, May 20, 2015 - 4:06:31 AM - Liza Back To Top

thank you for this tutorial , very intéréssant very well explained and above all it allowed me to save time


Learn more about SQL Server tools