MDX Query with Date Range Filter

3
votes

I am new to the MDX queries. I am writing a MDX query to select a Measure value across months and I am putting date Range as filter here just to restrict no of Months returned. For eg I want Sales Revenue for each month in Date Range of 01-Jan-2014 to 30-Jun-2014. Ideally, it should give me sales value for six months i.e Jan, Feb, Mar, Apr, May and June. However when i write below query, I get error. PFB the below enter code here`ow query.

Select NON EMPTY {[Measures].[Target Plan Value]} ON COLUMNS,
NON EMPTY {[Realization Date].[Hierarchy].[Month Year].Members} ON ROWS
From [Cube_BCG_OLAP] 
( { [Realization Date].[Hierarchy].[Date].&[20140101] : 
[Realization Date].[Hierarchy].[Date].&[20141231] })

The error I get is The Hierarchy hierarchy already appears in the Axis1 axis. Here Date and Month Year belong to same dimension table named as Realization Date. Please help me. Thanks in advance.

4
datemdx

4 Answers

5
votes

You were missing the WHERE clause but I guess that was a typo. As your error message tells, you can't have members of the same hierarchy on two or more axes. In situations like this, you can use something like below which in MDX terminology is called Subselect.

Select NON EMPTY {[Measures].[Target Plan Value]} ON COLUMNS,
NON EMPTY {[Realization Date].[Hierarchy].[Month Year].Members} ON ROWS
From (
        SELECT 
        [Realization Date].[Hierarchy].[Date].&[20140101] : 
        [Realization Date].[Hierarchy].[Date].&[20141231] ON COLUMNS
        FROM [Cube_BCG_OLAP]
    )
2
votes

I like the exists function in this situation:

SELECT 
  NON EMPTY {[Measures].[Target Plan Value]} 
  ON COLUMNS,
  NON EMPTY 
    EXISTS(
      [Realization Date].[Hierarchy].[Month Year].Members
      , {
         [Realization Date].[Hierarchy].[Date].&[20140101] : 
         [Realization Date].[Hierarchy].[Date].&[20141231]
        }
    ) 
  ON ROWS
FROM [Cube_BCG_OLAP]
0
votes
Select 
[Measures].[Target Plan Value]} On Columns
{
[Realization Date].[Hierarchy].[Date].&[20140101].Parent : 
[Realization Date].[Hierarchy].[Date].&[20140631].Parent
} 
On Rows
From [Cube_BCG_OLAP]
0
votes

You need to create this same dimension only for filter in the cube, for example, dimension_filter -> hierarchy_filter -> level_filter