Monday 14 March 2016

Detect Disappearing Member

Recently I have a requirement to create a report where we need to display either “List Before” or “List After” depending on information status for the month. Upon further checking I realized the information status can be determined depending on whether a member (Before) is present or not in a dimension:

after
Presence of “Before” member means to display “List Before”

before
Later part of the month, member “Before” will disappear and the report should display “List After”

I added the Flag level in the Query, then added a calculation to count how many members are present:

count
Then I added a string variable (BeforeOrAfter) to set a value depending on how many members the level returned. If the level returned three members (‘NA’,’BEFORE’ and ‘AFTER’), the variable will return the value “0″. If the level do not have “BEFORE”, it will return only two members (‘NA’ and ‘AFTER’) and the string variable will return the value “1″.

var

Then I set the BeforeOrAfter as the render variable of my lists. “List Before” will render when BeforeOrAfter = 0 and “List After” will render when BeforeOrAfter = 1.

beforelist
afterlist

When I run my sample report, I get the correct list as the information status for the month is currently “After” .

outputafter
To try to simulate when “BEFORE” member is present, I change the string variable to:

BeforeOrAfter =if([Query1].[Count Flag] > 1) then (’0′) else (’1′)

Go figure it out :)
I get this output:

outputbefore

No comments:

Post a Comment