Post reply

MS BI-Passing date from Date picker (Reports ) to MDX query using SSAS

  • January 22, 2008 at 2:09 am

    #178409

    Hi,

    Currently I am working on SSRS using SSAS for reports. When I pass date from Date picker from SSRS to MDX query. It gives error. Does any one can help me to sort out that issue.

    Thanks in Advance

    Shail Shrivastava

  • January 22, 2008 at 2:30 am

    #770383

    What is the error you are getting?

  • January 22, 2008 at 10:53 pm

    #770745

    The Error is "The Property 'Valid Values' for report parameter does not have expected data type." I think MDX does support the date time data type and accept the string for date. Please let me know.

  • March 24, 2008 at 11:06 am

    #793120

    Hi

    we are having the same issue. Has anyone figured it out or is it a feature of RS with MDX ?

    Thanks

  • May 22, 2008 at 4:12 pm

    #819415

    I believe the MDX query is actually using a formatted version of the date, rather than the datetime value passed via the calendar date-picker. I resolved this in my reportby editing the data set - in the parameters tab, I formatted the date by setting the value of the Parameter to:

    ="[DATE DIMENSION].[Date].&[" & Format(CDate(Parameters!FromDATEDIMENSIONDate.Value),"yyyy-MM-dd") & "T00:00:00]"

    My date dimension is called DATE DIMENSION, and the parameter name is "FromDATEDIMENSIONDate"

    Hope this helps.

    -Marianne

  • May 23, 2008 at 8:58 am

    #819747

    Just to confirm for people:

    MDX indeed DOES NOT support the datetime format in the manner you may be expecting. To understand that, you'll notice that there are MDX fuinctions like StrToMember(@parm) and StrToValue(@parm) but no DateTimetoMember(@parm). (Get it??)

    (BTW, there's no NumToMember(@parm) for similiar reasons.)

    It's helpful to remember that MSRS was perhaps designed first with relatgional concepts in mind. That's evident from a number of perspectives:

    - MSRS deals with flattened result sets. MSAS data has to be flattened before being consumed by MSRS.

    - When using measures, the best reference to use with MSAS is Fields!myField.FormattedValue, as opposed to Fields!myField.Value which is the default.

    - You generally have to override the default behaviors to force RS to use the values returned from the cube (i.e. aggregate values).

    - The MDX designer really isn't up to the task. It really needs to be replaced to implement proper OLAP concepts. (I always hand write the MDX for MSRS.)

    Having said that, MSRS is a great tool for operational style reports. I use it all the time.

  • November 5, 2008 at 10:13 am

    #894395

    Is this working????

  • May 20, 2009 at 1:27 am

    #997417

    Hi Marianne

    ="[DATE DIMENSION].[Date].&[" & Format(CDate(Parameters!FromDATEDIMENSIONDate.Value),"yyyy-MM-dd") & "T00:00:00]"

    My date dimension is called DATE DIMENSION, and the parameter name is "FromDATEDIMENSIONDate"

    This helps when i took Equal but fails in Range inclusive and showing error STRTOMRMBER function is voilated,

    Please help me out..

    Amit

  • December 4, 2009 at 6:40 pm

    #1088095

    Has there been a resolution to this issue?

  • April 5, 2010 at 10:11 am

    #1145828

    Hi, just wanted to point out that it is very important to find out how the members for the date store the value (like [Date].[Date].[12101225] or [Date].[Date].&[1] using key). If it stores by key then parameter expression will be very different.

  • September 27, 2016 at 12:05 pm

    #1903431

    =FORMAT(CDate(Max(IIf(((CSTR(Fields!Planned_End_Date.Value)="2050-01-01 00:00:00.000") or (CSTR(Fields!Planned_End_Date.Value)="")),"1900-01-01",Fields!Planned_End_Date.Value))),"MMM-dd-yyyy")

Viewing 11 posts - 1 through 10 (of 10 total)

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