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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jason Selburg » MS BI-Passing date from Date picker (Reports...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Shail Shrivastava

Posted Tuesday, January 22, 2008 2:09 AM

Grasshopper

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

vyas

Posted Tuesday, January 22, 2008 2:30 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:02 AM
Points: 3,131, Visits: 1,056

What is the error you are getting?

Post #445780

Shail Shrivastava

Posted Tuesday, January 22, 2008 10:53 PM

Grasshopper

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

Marcus Wilkinson

Posted Monday, March 24, 2008 11:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 09, 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

Marianne L Collins

Posted Thursday, May 22, 2008 4:12 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 8:13 AM
Points: 227, Visits: 738

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

Dave Balsillie

Posted Friday, May 23, 2008 8:58 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 11:11 AM
Points: 215, Visits: 401

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

ponit

Posted Wednesday, November 05, 2008 10:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 22, 2009 12:43 AM
Points: 2, Visits: 18

Is this working????

Post #597527

amit.260183

Posted Wednesday, May 20, 2009 1:27 AM

Forum 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

Danielprosser

Posted Friday, December 04, 2009 6:40 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, July 30, 2012 9:56 AM
Points: 1, Visits: 30

Has there been a resolution to this issue?

Post #829325

Jaime PR

Posted Monday, April 05, 2010 10:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 02, 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 »

Permissions