Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MS BI-Passing date from Date picker (Reports ) to MDX query using SSAS Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2008 2:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 11, 2012 1:52 AM
Points: 15, Visits: 97
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
Post #445773
Posted Tuesday, January 22, 2008 2:30 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 3:22 AM
Points: 3,906, Visits: 1,098
What is the error you are getting?


Post #445780
Posted Tuesday, January 22, 2008 10:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 11, 2012 1:52 AM
Points: 15, Visits: 97
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.
Post #446238
Posted Monday, March 24, 2008 11:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 9, 2011 5:09 AM
Points: 6, Visits: 42
Hi

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

Thanks
Post #473599
Posted Thursday, May 22, 2008 4:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:58 PM
Points: 224, Visits: 748
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
Post #505529
Posted Friday, May 23, 2008 8:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 1:47 PM
Points: 215, Visits: 415
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.
Post #505938
Posted Wednesday, November 5, 2008 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 22, 2009 12:43 AM
Points: 2, Visits: 18
Is this working????
Post #597527
Posted Wednesday, May 20, 2009 1:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 16, 2010 1:57 AM
Points: 1, Visits: 5
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
Post #720346
Posted Friday, December 4, 2009 6:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 12:12 PM
Points: 1, Visits: 31
Has there been a resolution to this issue?
Post #829325
Posted Monday, April 5, 2010 10:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 2, 2010 4:01 PM
Points: 5, Visits: 8
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.
Post #896828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse