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?

• dJeePe

  Say Hey Kid

  Points: 712

  More actions

  October 23, 2007 at 5:29 am

  #744438

  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

  October 23, 2007 at 5:47 am

  #744444

  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

  November 2, 2007 at 6:26 pm

  #747949

  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

  November 30, 2007 at 10:22 am

  #756057

  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