MDX query not giving right answer

Question

Now i am having different problem with different MDX qry.

For the current month details I am getting details of the last month also.. The MDX query used by me is:

/*
SELECT 
NON EMPTY 
    { Hierarchize ( { [Offer].[GrandTotal], [Offer].[GrandTotal].Children }) } 
        ON COLUMNS, 
NON EMPTY 
    { Hierarchize ( { [Circle].[GrandTotal], [Circle].[GrandTotal].Children }) } 
        ON ROWS 
FROM 
    [SCMAircel_ActiveBase] 
WHERE 
  ( [Measures].[TotalCount], 
    [Subscriber State].[GrandTotal], 
    [Time].[2012].[${curMonth}].[${curDay}])
*/

I am using two parameter that is giving correct month and date value in the Pentaho Report Designer. But the problem is explained with the example below.

i want circlewise offerwise count for a day.for eg: for the date 04/01/2012. But i am getting the values of last month same day too..(01-12-2011) because that value is available on the table. But i need the 01-01-2012 data only..

The [Subscriber State].[GrandTotal] contains values like ACTIVE,GRACE,SUSPEND which i am filtering,

I dont know how that value TOTAL_COUNT is adding up eventhough the month and date is passing properly....

Pls tell any solution if anyone knows about this

Chris Chris · Accepted Answer · 2012-01-05T13:20:13

I am not familiar with Pentaho but with SQL Server Analysis Services. I assume that since MDX is a standard, there shouldn't be (m)any differences ;)

I assume that the result is flattened to an 2 dimensional result set and data is cross joined accordingly and later grouped by report controls.

So what I suggest you should try is (even though it is hard to understand your problem without further investigation):

/*
SELECT 
    {
        // it is best practice to put measures on columns
        [Measures].[TotalCount]  
    }
    ON COLUMNS, 
    NON EMPTY 
    {
        // if you want multiple dimension to be aggregated, you should cross join these 
        Hierarchize ( { [Offer].[GrandTotal], [Offer].[GrandTotal].Children }) } 
        *    // cross join here
        Hierarchize ( { [Circle].[GrandTotal], [Circle].[GrandTotal].Children }) 
    } 
    ON ROWS 
FROM 
    [SCMAircel_ActiveBase] 
WHERE 
  (
    //[Subscriber State].[GrandTotal], // i don't get this. You either need to filter for certain members, or you want members to be crossjoined. But putting a member hierarchy in filter doesn't make sense to me
    {[Subscriber State].[GrandTotal].[ACTIVE], [Subscriber State].[GrandTotal].[Grace] } // like this for example
    ,[Time].[2012].[${curMonth}].[${curDay}]
   )
*/

MDX query not giving right answer

1 Answers