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:
Presence of “Before” member means to display “List 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:
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″.
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.
When I run my sample report, I get the correct list as the information status for the month is currently “After” .
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:
No comments:
Post a Comment