MDX - filter empty outside of selected range

Question

Cube is populated with data divided into time dimension ( period ) which represents a month.

Following query:
select non empty {[Measures].[a], [Measures].[b], [Measures].[c]} on columns, {[Period].[Period].ALLMEMEMBERS} on rows from MyCube

returns:
+--------+----+---+--------+ | Period | a | b | c | +--------+----+---+--------+ | 2 | 3 | 2 | (null) | | 3 | 5 | 3 | 1 | | 5 | 23 | 2 | 2 | +--------+----+---+--------+

Removing non empty
select {[Measures].[a], [Measures].[b], [Measures].[c]} on columns, {[Period].[Period].ALLMEMEMBERS} on rows from MyCube
Renders:
+--------+--------+--------+--------+ | Period | a | b | c | +--------+--------+--------+--------+ | 1 | (null) | (null) | (null) | | 2 | 3 | 2 | (null) | | 3 | 5 | 3 | 1 | | 4 | (null) | (null) | (null) | | 5 | 23 | 2 | 2 | | 6 | (null) | (null) | (null) | +--------+--------+--------+--------+

What i would like to get, is all records from period 2 to period 5, first occurance of values in measure "a" denotes start of range, last occurance - end of range.

This works - but i need this to be dynamically calculated during runtime by mdx:
select non empty {[Measures].[a], [Measures].[b], [Measures].[c]} on columns, {[Period].[Period].&[2] :[Period].[Period].&[5]} on rows from MyCube

desired output: +--------+--------+--------+--------+ | Period | a | b | c | +--------+--------+--------+--------+ | 2 | 3 | 2 | (null) | | 3 | 5 | 3 | 1 | | 4 | (null) | (null) | (null) | | 5 | 23 | 2 | 2 | +--------+--------+--------+--------+

I tried looking for first/last values but just couldn't compose them into the query properly. Anyone has this issue before ? This should be pretty common seeing as I want to get a continuous financial report without skipping months where nothing is going on. Thanks.

whytheq whytheq · Accepted Answer · 2017-12-29T18:42:10

Maybe try playing with NonEmpty / Tail function in a WITH clause:

WITH 
SET [First] AS
  {HEAD(NONEMPTY([Period].[Period].MEMBERS, [Measures].[a]))}
SET [Last] AS
  {TAIL(NONEMPTY([Period].[Period].MEMBERS, [Measures].[a]))}
SELECT
  {
   [Measures].[a]
 , [Measures].[b]
 , [Measures].[c]
  } on columns, 
   [First].ITEM(0).ITEM(0) 
  :[Last].ITEM(0).ITEM(0)  on rows 
FROM MyCube;

to debug a custom set, to see what members it is returning you can do something like this:

WITH 
SET [First] AS
  {HEAD(NONEMPTY([Period].[Period].MEMBERS, [Measures].[a]))}
SELECT
  {
   [Measures].[a]
 , [Measures].[b]
 , [Measures].[c]
  } on columns, 
   [First]  on rows 
FROM MyCube;

I think reading your comment about Children means that this is also an alternative - to add an extra [Period]:

WITH 
SET [First] AS
  {HEAD(NONEMPTY([Period].[Period].[Period].MEMBERS
     , [Measures].[a]))}
SET [Last] AS
  {TAIL(NONEMPTY([Period].[Period].[Period].MEMBERS
     , [Measures].[a]))}
SELECT
  {
   [Measures].[a]
 , [Measures].[b]
 , [Measures].[c]
  } on columns, 
   [First].ITEM(0).ITEM(0) 
  :[Last].ITEM(0).ITEM(0)  on rows 
FROM MyCube;

MDX - filter empty outside of selected range

1 Answers