CODEWITHSUNDEEP
datemdx
Vikas Bansal
Vikas Bansal

Reputation: 51

MDX Query with Date Range Filter

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.

Upvotes: 3

Views: 21851

Answers (4)

Alan Jhone
Alan Jhone

Reputation: 1

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

Upvotes: 0

Merin Nakarmi
Merin Nakarmi

Reputation: 3418

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]

Upvotes: 0

whytheq
whytheq

Reputation: 35557

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]

Upvotes: 3

SouravA
SouravA

Reputation: 5243

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]
    )

Upvotes: 5

Related Questions