Simple problem puzzling me - return date value from SSAS query into SSRS report?
Hello,
I am using SQL Server Reporting Services 2008 and SQL Server Analysis Services 2008.
I have a time dimension that has hierarchies with a few variations of annual period for calendar, financial etc. I recently changed an cube query underlying a SSRS report from my basic calendar hierarchy month (e.g. "January 2010") to a variant (e.g.
"Fiscal Jul-Jun January 2010").
To my surprise, SSRS started complaining about not being able to convert a value to a date/time, and I realized that my report was sorting by the month (correctly) but had only been working fine until now because "January 2010" as a string implicitly converts
to a date. When I switched to the other hierarchy, this broke.
My MDX is pretty weak, but knowing that every time dimension member has an actual date/time value attached to it, I assume I could just return this in the MDX query so my SSRS report could properly sort by this. But how?
I don't have such a field exposed in the Time dimension, so I created a calculated member something like:
[Time].[Fiscal Jul-Jun].currentmember.MemberValue
And added this to the Measures hierarchy. Added it to my query and hey presto I get thousands of rows returned. I assume because I've just create a new measure that returns a value for every member of the time dimension, so the cube dutifully
returns each row.
I only need this for rows that would normally have been returned by my query, so I puzzed for a bit about how to do it, and eventually ended up with a clunky statement:
IIF(
ISEMPTY( [Measures].[Foo A])
AND ISEMPTY ([Measures].[Foo B])
AND ISEMPTY([Measures].[Foo C])
, NULL
, [Time].[Fiscal Jul-Jun].currentmember.MemberValue
)
I had many measures, so imagine the above with "Foo D", "Foo E", "Foo F" etc.
It now works, but I am unhappy with how awkward this calculated member has become when all I want to do is return a date so I can properly sort by it. Is there a smarter way e.g.
-some way of getting the raw date/time value out of the dataset when the normal "human readable" time dimension member hits SSRS
-some more concise MDX to only give me the raw date/time value for rows the query would otherwise have returned anyway
-a new attribute/property somewhere on my time dimension to return the raw date/time value if I actually need it as a date/time, not a string?
Thanks in advance,
Michael
December 10th, 2010 2:57am
“-a
new attribute/property somewhere on my time dimension to return the raw date/time value if I actually need it as a date/time, not a string?”
Yes, you can create a property only return date/time for this attribute, then you can use this property for sorting/query:
with member measures.x as
cdate([Date].[Date].Properties("a date/time attribute"))
select measures.x on 0,
[Date].[Date].[Date] on 1
from [Adventure Works];
Hope this helps,
Raymond
Raymond Li - MSFT
Free Windows Admin Tool Kit Click here and download it now
December 14th, 2010 7:31pm
Thanks Raymond - that sounds like exactly what I want to do. I'll mark this as the answer now but will have a go at adding this property next time I am making some adjustments to the cube design.
Regards,
Michael
December 16th, 2010 8:45pm