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

“-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

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