Analysis Services: MDX - Date ranges with 2 Time Dimensions

Hi,

I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.

Is there any way that I can have a range of dates?

Is it possible to have 2 time Dimensions for example which did the following:

a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )

Thus I would be able to select a range of dates/months.

Do you know if this is possible to write this inot the dimension?

Thanks,

David

The short answer is yes. However, MDX is not the most user friendly language so it us not intuitively obvious.

Have a look at the syntax used in the following statements. The first filters on 1997.Q1 by placing it in the WHERE clause. The second gets the same result but by specifying the "filter" as part of the "WITH MEMBER". The third extends this so that a range of quarters is used. The values returned by the third query are a bit weird - I don't have time at present to analyse why that is happening.

Using the syntax similar to the third example, you can also specify a list of members if you so desire.

with
member [Product].[All Products].[Drink].[Percent of Alcoholic Drinks] as '[Product].[All Products].[Drink].[Alcoholic Beverages]/[Product].[All Products].[Drink]', format = '#.00%'
select
{ [Product].[All Products].[Drink].[Percent of Alcoholic Drinks] } on columns,
order([Customers].[All Customers].[USA].[WA].Children, [Product].[All Products].[Drink].[Percent of Alcoholic Drinks],BDESC ) on rows
from Sales
where ( [Measures].[Unit Sales],[Time].[1997].[Q1] )




with
member [Product].[All Products].[Drink].[Percent of Alcoholic Drinks] as 'sum ( {[Time].[1997].[Q1] } , [Product].[All Products].[Drink].[Alcoholic Beverages] / [Product].[All Products].[Drink] ) ', format = '#.00%'
select
{ [Product].[All Products].[Drink].[Percent of Alcoholic Drinks] } on columns,
order([Customers].[All Customers].[USA].[WA].Children, [Product].[All Products].[Drink].[Percent of Alcoholic Drinks],BDESC ) on rows
from Sales
where ( [Measures].[Unit Sales] )

with
member [Product].[All Products].[Drink].[Percent of Alcoholic Drinks] as 'sum ( {[Time].[1997].[Q1] : [Time].[1997].[Q4] } , [Product].[All Products].[Drink].[Alcoholic Beverages] / [Product].[All Products].[Drink] ) ', format = '#.00%'
select
{ [Product].[All Products].[Drink].[Percent of Alcoholic Drinks] } on columns,
order([Customers].[All Customers].[USA].[WA].Children, [Product].[All Products].[Drink].[Percent of Alcoholic Drinks],BDESC ) on rows
from Sales
where ( [Measures].[Unit Sales] )

Thanks steven. I see how you have incorporated date ranges in your query. However I do not understand how to incorporate this into my cube.

Do i create a Time dimension and then put a smiliar query to yours (using the date range) in the Time Dimensions "All Member Formula" in the dimensions property box?

or do you use a seperate interface to query your cube?

please ignore last reply. I now know how to query the cube through the analysis server Client interface (mdxquery.mdx)

However.....

..eventually I need to be able to use Crystal Reports to be the front end of my cube so that I can put a report on the intranet for users to query the cube using the Dimensions created in the cubes as Parameters in Crystal.

Therefore I really need to be able to write some code into my Time dimension in the cube (if possible) that says >= the date the user selects.

And another Time dimension in the cube that says <= the date the user selects.

Therefore achieving a Date range.

Is that possible?

Querying the cube directly defeats my aim of having to manully query the cube myself (as it is labour intensive)and guess I could do this directly from the db.

Any suggestions are much appreciated. I dont know if this is possible. THanks.