Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Shail Shrivastava
Shail Shrivastava
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
Chandra Sekhara Vyas Dhara
Chandra Sekhara Vyas Dhara
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3963 Visits: 1149
What is the error you are getting?



Shail Shrivastava
Shail Shrivastava
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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.
Marcus Wilkinson
Marcus Wilkinson
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Marianne L Collins
Marianne L Collins
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 751
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
Dave Balsillie
Dave Balsillie
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 430
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.
ponit
ponit
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 18
Is this working????
amit.260183
amit.260183
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
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
Danielprosser
Danielprosser
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 31
Has there been a resolution to this issue?
Jaime PR
Jaime PR
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search