April 5, 2010 at 9:40 am
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.
April 28, 2010 at 5:49 am
Hi,
Try this on parameter Value expression:
="[Date].[Date].&[" & Format(CDate(Parameters!DateDate.Value), "s") + "]"
April 29, 2010 at 7:11 pm
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
June 2, 2010 at 12:01 pm
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
June 2, 2010 at 12:28 pm
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.
June 2, 2010 at 1:12 pm
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
October 26, 2012 at 6:58 am
Do you know how to default the date to date -1?
February 12, 2013 at 2:51 pm
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:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply