SSAS Date Range Queries

[pjohns]

Hi, me again.

Can someone tell me how to handle SSAS date range queries? SSAS cube? Specifically I'm trying to query an SSAS 2005 cube to get a date range and the associated measure data for that date range using SSRS.

I'm assuming I need to use MDX, but I'm not real proficient with MDX at this point. Is there an easier way to do this? If MDX is the only way, can someone explain to me how to do it - maybe provide sample code?

Thanks again.

...Phillip

 

[FlaBusInt]

Try buiding your SSRS report using the Wizard. It will provide a view of your cube similar to the cube's Browser window. If you put dates in the parameter fields up top and check the Parameter check box, it will auto generate the MDX that you need.

 

[MDXer]

to specify a range use : in your MDX query.

The below queries are against Adventure works. As the samples show you can use between either in your where clause or on rows or columns depending on your desired output.

Select
CrossJOIN
(
{[Ship Date].[Date].[Date].[July 8, 2001]:[Ship Date].[Date].[Date].[July 12, 2001]},
{[Measures].[Order Count]})  On Columns,
{[Product].[Product Categories].[Category]}On Rows
From [Adventure Works]

Select
{[Measures].[Order Count]} On Columns,
{[Product].[Product Categories].[Category]} On Rows
From [Adventure Works] 
Where {[Ship Date].[Date].[Date].[July 8, 2001]:[Ship Date].[Date].[Date].[July 12, 2001]}

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[pjohns]

Thanks, Paul! That's exactly what I needed.

Thanks for your input as well, FlaBusInt. Unfortunately the wizard was not an option since the SSRS report (which is quite complex) has already been created and would be difficult to re-create using the wizard.