Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using calendar date picker in Reporting Services MDX cube report


Using calendar date picker in Reporting Services MDX cube report

Author
Message
Jaime PR
Jaime PR
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
Hi, to anyone who can help me I would really appreciate it. I have spent several days trying to make a Reporting Services report using Visual Studio 2005 and a cube from SQL Server 2005 Analysis Services. The report needs to use a Date parameter. The cube stores the date field in a string format. After creating the parameter for the report a list of values is presented for the user to select from. I do not wish to use an entire list of available values for the parameter, I need to be able to use a calendar date picker. I am using the Adventure Works sample database.

My MDX query which I have edited manually looks like this:

SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@DateDate) ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I know that I must go to Layout > Report Parameters > DateDate parameter and change the Data type from String to DateTime, deselect Multi-value, put Available values as Non-queried and Default values to Null.

Then in the Dataset click the ... button and go to the Parameters tab. There I must replace the Value for the DateDate parameter with an Expression. I think this is where my problem is. I have tried several expressions and conversion formulas but in the results the field always appears blank.

My DateDate parameter Value expression is:
="[Date].[Date].&[" & Format(CDate(Parameters!DateDate.Value),"MM/dd/yyyy") & "]"

I have encased my MDX query in an expression with ="SELECT etc etc" but have not gotten this to work.

My Text for the query is:
="SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@DateDate) ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

Please help me, I am going crazy with this! Thank you so much in advance.
Jan Kivivuori
Jan Kivivuori
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 161
Hi,

Try this on parameter Value expression:

="[Date].[Date].&[" & Format(CDate(Parameters!DateDate.Value), "s") + "]"
Jaime PR
Jaime PR
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
Hi, thank you for the reply. I managed to solve my issues and posted the solution in the following blog: http://www.bidn.com/blogs/jchacon/bidn-blog/579/reporting-services-cube-report-calendar-date-picker-parameter
killionb12
killionb12
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 43
Do you still have that solution from your blog. I can't access your blog anymore and I'm having the same issue.

Thanks
killiob
Jaime PR
Jaime PR
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
Hi, I don't know what happened to my blog. The BDN site has problems with my account and blog. :-( Oh well here you go, this should help you resolve your problems (my blog post included images).


The following summary details the steps to follow in order to implement a calendar date picker as a parameter of a cube report utilizing Visual Studio 2005 and SQL Server 2005 Service Pack 2 (with Analysis Services and Reporting Services).

It is very important to keep in mind that there are several ways in which cubes store datetime fields. You need to mouse over the member to see the exact syntax of the member.
One of the most common types is the following (string data type) format:
Example: [DATE].[Date].&[2009-12-01T00:00:00]

In order to be able to select the date parameter by calendar date picker instead of by a list of dates:

1) Create Report
Create project, datasource, report (.rdl file), dataset that will have the query that calls the cube and necessary Layout. In the Data tab drag levels or measures to add to the query.

2) Create the Parameter
In the Data tab create Parameters called Date by selecting the checkbox, then press the ! Execute Query button to verify the total.

3) Configure Report Parameters
Go to Layout tab, go to Report Parameters and verify that the Parameter Date is created, change Properties: Data type dropdownlist from String to DateTime, verify that checkboxes for Hidden, Internal, Multi-value, Allow null value and Allow blank value are NOT selected, verify that Available values is Non-queried, change Default values selected radiobutton to Non-queried and with a value of =Today, press OK button.

4) Assign Expression to the Parameter
In the Data tab select the … Edit Selected Dataset button, go to the Parameters tab, verify that the Parameter Name Date appears and in the value change =Parameters!Date.Value to <Expression…>
The expression for the Date parameter in this example is (Note: the expression will vary depending on the format in which your cube stores the date field):
="[DATE].[Date].&[" & Format(CDate(Parameters!Date.Value),"yyyy-MM-dd") + "T00:00:00]"

Press the OK button, then in the Parameters tab press OK button again. Go to the Preview tab and verify that the calendar date picker works as desired and that the totals are correct.

5) Eliminate Unnecessary Datasets
After finishing the Datasets used in the report you can delete the unnecessary additional datasets that are created to present the lists of dates, these are not used.

Notes and Observations
- In this example it is not necessary to encapsulate the query for the Dataset like an expression (=”expression”).

- There is also no need to edit the MDX query manually in Design Mode which is convenient since manually editing a query usually results in not being able to return to the graphical Design Mode without losing the changes that where made manually.

- During my tests I learned that if you are in a development environment that has SQL Server 2005 without Service Pack 2 and have Microsoft Office 2007 the following error (bug) will occur when trying to execute an MDX query or edit it manually in the Design Mode:
Query preparation failed.
-> Error in the application. (msmgdsrv)

If you are using SQL Server 2005 installing Service Pack 2 is highly recommended.

- Please note that in a different case it is also possible that the Analysis Services cube modeller has decided to use a consecutive integer key for the Date dimension table. In such case since date names are unique, try converting to the name of the member, so you get the following (note that there isn't an ampersand):
[Date].[Date].[December 1, 2009]
It is recommended that YYYYMMDD format is used for the integer key so that you can easily convert from date to integer to resolve the member by key. Thanks to Teo Lachev who submitted most of this observation and Dustin Ryan for your assistance.
killionb12
killionb12
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 43
Jamie,

Thanks for getting back to me so quickly. I think your solution got me close, but I'm still missing something for some reason. When I pick a value from the data picker it doesn't return anything. I'm using the AdventureWorks2008 cube in my Report example to get it working first before I try it on my own report. Does your solution work with SSRS 2008 as well?

Thanks
steven.templeton
steven.templeton
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 114
Do you know how to default the date to date -1?
msmithson
msmithson
Mr or Mrs. 500
Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)

Group: General Forum Members
Points: 505 Visits: 578
I just wanted to say thanks to the OP. I've been using SSRS with t-sql for years but SSAS/MDX are new to me. This really helps!w00t
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search