Date Picker for MDX/AS query

Question

Date Picker for MDX/AS query

Sam Peascod

SSCrazy

Points: 2497
More actions
October 19, 2007 at 12:50 am

#176397

I have a report which requires the user to pick a certain date from a parameter. I've setup all my parameters using MDX and separate data sets for parameter values.
The parameters created by default are all strings in the form of an MDX tuple.
Can I convert these parameters into a date picker, which would nicely convert the date into MDX syntax?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dJeePe Say Hey Kid Points: 712 More actions · Answer 1

I have used following Query

SELECT NON EMPTY { [Measures].[InventoryQty] } ON COLUMNS, NON EMPTY { ([Branch].[Branch Code].[Branch Code].ALLMEMBERS * [Currency].[Currency].[Currency].ALLMEMBERS * [Branch].[Branch Name].[Branch Name].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [XPDW]

WHERE Filter([Date].[Full Date].[Full Date].Members,[Date].[Full Date].CurrentMember.MemberValue=CDate(@dateParam))

@dateParam is DateTime parameter Of type DateTime in Reporting Services.

and the ValueColumn Property of [Full Date] Attribute of Date Dimension is binded to datetime column

Nasir.

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 2

before running the SP/QRY try to figure out the local datetime setting of your server. And then use the following query ;_

1) SET DATEFORMAT mdy or

2) SET DATEFORMAT ydm or

3) SET DATEFORMAT ymd

Sam Peascod SSCrazy Points: 2497 More actions · Answer 3

Thanks for the help people. I tried using Nasir's solution (and it still seems the most elegant) but I kept getting a type mismatch.

I ended up creating a DateTime variable, then defining a VB function to convert a date into an MDX string to match my time dimension (using the Parameter relations section in the data source link).

shari-509367 SSC Veteran Points: 272 More actions · Answer 4

Can you share a sample of how you made this work using a function? I too am getting a date type conversion error and have to use a Date Picker in my Reporting Services report against a cube. Any help is appeciated. I have already spent a day trying to get this to work.

~Shari