Thread: How filter a cube with a period?

1. Junior Member
Join Date
Dec 2011
Posts
6

How filter a cube with a period?

I have a problem with a MDX query;
I want to filter a cube with a given period.

I originally created in my cube a dimension having "TimeDimension" type with four levels (Year, Quarter, Month and Day), and I filtered as well:

WITH MEMBER [Date].[Toutes les dates].[annee].[quarter].[mois].[Du \${jourdebut}/\${moisdebut}/\${anneedebut} au \${jourfin}/\${moisfin}/\${anneefin}] AS
Aggregate([Date].[Toutes les dates].[\${anneedebut}].[\${quarterdebut}].[\${moisdebut}].[\${jourdebut}] : [Date].[Toutes les dates].[\${anneefin}].[\${quarterfin}].[\${moisfin}].[\${jourfin}])
select NON EMPTY {[Measures].[Nombre 1], [Measures].[Nombre 2]} ON COLUMNS,
NON EMPTY {[Zone].[Toutes les zones]} ON ROWS
from [MonCube]
where ([Date].[Toutes les dates].[annee].[quarter].[mois].[Du \${jourdebut}/\${moisdebut}/\${anneedebut} au \${jourfin}/\${moisfin}/\${anneefin}])

Each parameter \${x} is the result of a selection of a parameter of my application (SpagoBI).
It worked well.

The problem is that users have to fulfill 8 parameters. So it's much too heavy.
The idea is to select only 2 input parameters (start date and end date).

So I created a new dimension with only the field 'date' which have a String type (Date type doesn't work with my DB - Sybase IQ -). This field is written as "2011-05-30".
So I thought parse the result of the parameter with the 'Mid' function. And I said I would adapt my MDX query as follows:

WITH MEMBER [Date].[Toutes les dates].[annee].[quarter].[mois].[Du \${datedebut} au \${datefin}] AS
Aggregate([Date].[Toutes les dates].['Mid(\${datedebut},1,4)'].[A calculer].['Mid(\${datedebut},6,2)'].['Mid(\${datedebut},9,2)'] : [Date].[Toutes les dates].['Mid(\${datefin},1,4)'].[A calculer].['Mid(\${datefin},6,2)'].['Mid(\${datefin},9,2)]')
select NON EMPTY {[Measures].[Nombre 1], [Measures].[Nombre 2]} ON COLUMNS,
NON EMPTY {[Zone].[Toutes les zones]} ON ROWS
from [MonCube]
where ([Date].[Toutes les dates].[annee].[quarter].[mois].[Du \${datedebut} au \${datefin}])

The 'Mid' function is recovering well the year, month and day (for the quarter, I'll use Iif). However, the MDX query does not retrieve the result of the function, but the entire function.

For example, if my parameter \$datedebut = "2011-05-30" and \$datefin= "2011-06-30", then the MDX query is:
WITH MEMBER [Date].[Toutes les dates].[annee].[quarter].[mois].[Du 2011-05-30 au 2011-06-30}] AS
Aggregate([Date].[Toutes les dates].['Mid(2011-05-30,1,4)'].[A calculer].['Mid(2011-05-30,6,2)'].['Mid(2011-05-30,9,2)'] : [Date].[Toutes les dates].['Mid(2011-06-30,1,4)'].[A calculer].['Mid(2011-06-30,6,2)'].['Mid(2011-06-30,9,2)'])
select NON EMPTY {[Measures].[Nombre 1], [Measures].[Nombre 2]} ON COLUMNS,
NON EMPTY {[Zone].[Toutes les zones]} ON ROWS
from [MonCube]
where ([Date].[Toutes les dates].[annee].[quarter].[mois].[Du \${datedebut} au \${datefin}])

But I have the following error:
Mondrian Error:Failed to parse query 'WITH MEMBER[Date].[Toutes les dates].[annee].[quarter].[mois].[Du 2011-05-30 au 2011-06-30}] as Aggregate([Date].[Toutes les dates].['Mid("2011-05-30",1,4)'].[Trimestre 2].['Mid("2011-05-30",6,2)'].['Mid("2011-05-30",9,2)'])

Obviously the 'Mid("2011-05-30",1,4)' year does not exist in my cube. That's the 2011 year there is.
The 'Mid' function returns the good results ('Mid("2011-05-30",1,4) = 2011, 'Mid("2011-05-30",6,2) = 05, 'Mid("2011-05-30",9,2) = 30).

The result of the MDX query should be:
WITH MEMBER [Date].[Toutes les dates].[annee].[quarter].[mois].[Du 2011-05-30 au 2011-06-30}] AS
Aggregate([Date].[Toutes les dates].[2011].[A calculer].[05].[30] : [Date].[Toutes les dates].[2011].[A calculer].[06].[30])
select NON EMPTY {[Measures].[Nombre 1], [Measures].[Nombre 2]} ON COLUMNS,
NON EMPTY {[Zone].[Toutes les zones]} ON ROWS
from [MonCube]
where ([Date].[Toutes les dates].[annee].[quarter].[mois].[Du \${datedebut} au \${datefin}])

The problem is that my function is inside of a member of my dmension. I think that I get to escape my function in order to execute it before the execution of the MDX query.

What is the correct syntax? I tried with '&':[Date].[Toutes les dates].&['Mid("2011-05-30",1,4)'], with '+', ...
But nothing works.

2. Junior Member
Join Date
Dec 2011
Posts
6
Julian, have you an idea?
Last edited by aolliet; 01-04-2012 at 10:39 AM.

3. Community Guy v2.0
Join Date
Jul 2007
Posts
2,497
Jesus - stop making your own life harder

> So I created a new dimension with only the field 'date' which have a String type (Date type doesn't work with my DB - Sybase IQ -). This field is written as "2011-05-30".

What you need to do is add a new level with the entire date string, below the date level, staying with something like: DateDim -> Year -> Month -> Day -> Date, and then just do [Date].[Date].[\${paramDate}]

4. Junior Member
Join Date
Dec 2011
Posts
6
OMG... It was so simple!!! I was persuaded that my "Date" Dimension must be a "TimeDimension" type in order to filter with a period.

Thanks pmalves!