MDX script - mix empty and non empty on the same axis

Question

I would like to ask your help to understand the main logic of MDX query. I would like to query different dimensions from the cube. But: in some case I would like to hide the NULL values, but in other dimension it is still needed. In my case I would like to provide date weeks and it dates for every day within the time period. (Even if it has no data) The content would be order number, position number and company and of course the KPI: OTD_customer. I tried many times to fix the null value issue, but I'm beginner on MDX. I tried to use NON EMPTY keyword and also Nonempty function, but still don't have the wished result. One more complicated thing is to filter the date for time period: based on actual day the last 15 weeks. (this part is working fine)

>    SELECT NON EMPTY 
>    {  [OTD_customer_new] }  ON COLUMNS,   NON EMPTY 
>    { 
>    (
>    ((  strtoset("LASTPERIODS(15,([Date - Plan Delivery Date].[Calendar Week].&[" + left(ltrim("2017 KW 10"),4) +" KW " +  right(ltrim("2017 KW 10"),2) + "]) )     ") 
>    , [Order Number].[Order Number].[Order Number].ALLMEMBERS 
>    , [Position Number].[Position Number].[Position Number].ALLMEMBERS
>    ,[Date - Plan Delivery Date].[Day of the Week].ALLMEMBERS
>    ,[Group Structure].[Group Structure].ALLMEMBERS
>    )  )  
>    *
>    ([Date - Plan Delivery Date].[Date].[All].FirstChild:
>    tail
>     ( filter
>       ( [Date - Plan Delivery Date].[Date].[All].Children,
>         [Date - Plan Delivery Date].[Date].CurrentMember.Name <= Format(Now(),    "yyyyMMdd")
>         ), 1
>       ).item(0)
>     ))
>    } 
>     ON ROWS 
>     FROM [ProductionCube]

Shall I use the filter in where condition? what is the best way to query the result from the cube? When it will be in normal relation database I would use a simple joins for this purpose.. But here in MDX I don't see the possibility with left and inner joins in script. Based on my understanding the hole axis may have the possibility to filter the null values. Do you have any idea? (thank you for reading) Current result example can be found here

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-03-09T21:30:02

The Non Empty function designed to filter empty axes (when your output is calculated). The NonEmpty function does the calculation of set. So, if you run:

Non Empty {Set1} * {Set2}

You'll get only crossjoined non-empty records. If you run:

NonEmpty({Set1} * {Set2}, [Measures].[NonEmptyMeasure])

You'll get the same result, but the mechanic is a bit different. If you want to filter only specific sets you may use NonEmpty around these sets:

{Set1} * NonEmpty({Set2}, [Measures].[NonEmptyMeasure])

Set1 won't be filtered. Nonetheless, all members of Set1 will crossjoined with all non-empty members of Set2. Most likely that's not what you want. You have many extra attributes joined to your date. What do expect them to return for empty records? I may guess you want to return All member, say, Set1 is a date set and Set2 is a company set:

NonEmpty({Set1} * {Set2}, [Measures].[NonEmptyMeasure]) + { {Set1} - NonEmpty({Set1}, [Measures].[NonEmptyMeasure])} * {Set2}.Item(0).Parent

Explanation:

1. NonEmpty({Set1} * {Set2}, [Measures].[NonEmptyMeasure]) -- returns non-empty records
2. {Set1} - NonEmpty({Set1}, [Measures].[NonEmptyMeasure]) -- returns empty dates
3. {Set2}.Item(0).Parent -- returns All member

The result will look like the following:

|=====================================| 
| 2016 week 51 | Company1      | 1    |
| 2016 week 51 | Company2      | 1    | 
| 2016 week 51 | Company3      | 1    | 
| 2016 week 52 | All companies | NULL |
=======================================

Is it the wished result?

MDX script - mix empty and non empty on the same axis

1 Answers