Prologika Forums

Teo-

Have you had any luck creating a report param which uses the date picker and feeds info to an MDX query? Here's as far as I got...

1. Using the MDX query designer for your main query, define a date parameter.

2. Edit the report parameter it created. Make it DateTime type. Don't query the database. Uncheck multi-value. Default it to null.

3. Delete the extra dataset it created which would have driven the date parameter.

4. Edit the query parameter and write an expression which converts the DateTime from the parameter into an MDX member name... such as:
="[Date].[Date].&[" & Year(Parameters!DateDate.Value) & Right("0" & Month(Parameters!DateDate.Value),2) & Right("0" & Day(Parameters!DateDate.Value),2) & "]"

All that works fine. However, now go add a field to your main dataset and report designer trashes all the work you've just done. (Don't you hate it when tools think they're smarter than you!)

Is there any way you know of to tell SSRS not to mess with a particular query/report parameter again? Or is there another way you could think of to use the date picker with report on a cube?

Thanks!

I do have a report with a custom dataset for a date parameter. I verified your scenario and the date dataset is not trashed. Most likely, this is because I have SP1 CTP installed. The Report Designer should check only if a parameter with the same name exists and it shouldn't overwrite the parameter dataset. Please re-check once SP1 is out and let me know if you have the same problem.

You can create a hierarchical date picker from your date dimension using a query like the following. you may want to cascade the parameters, picking a year first, then using that result to limit the next parameter query to a manageable number of days.

These examples will work against the AdventureWorks sample cube, and demonstrate how to return a specific subset of levels specific levels of a hierarchy.  The first returns just the available Years, then Years, Semesters, Quarters, then Years, Semesters, Quarters and Months.

-- return level 1 (Calendar years)
WITH
MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption]
 , [Measures].[ParameterValue]
 , [Measures].[ParameterLevel]}
ON COLUMNS ,
  Generate({[Date].[Calendar].[Calendar Year].Members},
  {[Date].[Calendar].CurrentMember}) ON ROWS
  FROM [Adventure Works];

-- return 3 levels, not including [ALL]
WITH
MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption]
 , [Measures].[ParameterValue]
 , [Measures].[ParameterLevel]}
ON COLUMNS ,
  Generate({[Date].[Calendar].[Calendar Year].Members},
  {[Date].[Calendar].CurrentMember,
  Generate([Date].[Calendar].Children,
  {[Date].[Calendar].CurrentMember,
  [Date].[Calendar].Children})}) ON ROWS
  FROM [Adventure Works];

-- return 4 levels, not including [ALL]
WITH
MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption]
 , [Measures].[ParameterValue]
 , [Measures].[ParameterLevel]}
ON COLUMNS ,
  Generate({[Date].[Calendar].[Calendar Year].Members},
  {[Date].[Calendar].CurrentMember,
  Generate([Date].[Calendar].Children,
  {[Date].[Calendar].CurrentMember,
  Generate([Date].[Calendar].Children,
  {[Date].[Calendar].CurrentMember,
  [Date].[Calendar].Children}),
  [Date].[Calendar].Children})}) ON ROWS
  FROM [Adventure Works];

Great! Thanks for sharing.

The original solution works great!!!!!!  

 I've been working with SqlServer since the Sybase days, in .Net since it came out and I really think SSRS has many glaring weaknesses.  This being one of them.  I simply want to write a report against a cube, often those reports involve date ranges.  In fact I would say most of my reports involve a date range. And to jump through this many hoops just to get my date ranges into the MDX is plain silly.  And my users don't want to pick a begin and end date by navigating through a hierarchy of year, quarter, month and day, they want a date picker (calendar) option.  Always have and they always will.  That is like entering a zip code by navigating through state and city first. 

Anyway, it is important to note that to do this you must:

a)  Select the elipsis (...) button next to your main data set. 
b)  Press the "f(x)" button on the dialog box (Query Tab, Query String area)
c) place the entire query string in double quotes and put an equal sign at the front of it (from Select <statement> to ="Select <statement>"

I found it easiest to build the query string normally.  Add a filter (non-parameter) with a date range (or single date if you are using a single date) that will act as place holders in your query string for the places you need to add the dat functionality.  Just for examples I have:

="SELECT NON EMPTY { [Measures].[Ticket Revenue], [Measures].[Tickets Sold] } ON COLUMNS, NON EMPTY { ([dTheatre].[Dim Theatre].[Dim Theatre].ALLMEMBERS * [dFilm].[Dim Film].[Dim Film].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [dDate].[Date Value].&[" + CStr(Year(Parameters!BeginDate.Value)) + "-" + Right("0" + Cstr(Month(Parameters!BeginDate.Value)), 2) + "-"+ Right("0" + Cstr(Day(Parameters!BeginDate.Value)), 2) + "T00:00:00] : [dDate].[Date Value].&["+ CStr(Year(Parameters!EndDate.Value)) + "-" + Right("0" + Cstr(Month(Parameters!EndDate.Value)), 2) + "-"+ Right("0" + Cstr(Day(Parameters!EndDate.Value)), 2) +"T00:00:00] ) ON COLUMNS FROM [FilmSales]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

 

The Month and day must be padded to ensure they are two characters.  By taking the Right 2 of the value prepended with a 0 makes 10 become 10 and 5 become 05.

You should be able to have a DatePicker if you use a query that brings the date value assuming that the MemberValue property of the Date member is bound to a date field in the dimension table. Then, you can set the parameter type as DateTime and bind it to MemberValue while you can pass the CurrentMember.UniqueName to the main query.

hi,

First,Sorry for my english...

I try since 2 days to associate datetime parameter to a MDX query. I'd tryed these examples above. But It doesn't work.

I want to make a reports with a START_DATE and a END_DATE, i don't understand how i should make.

Someone could explain me step by step ?

thanks

The Product Sales by Dates report in chapter 18 demonstrates this scenario.

excuse me, but where is the chapter 18?