Dynamic selection of multiple objects
In a report I have 5 measures and 7 prompts and .In that 4 are mandatory and 3 are optional prompts and these 3 optional prompts pointed to derived table and 5 Measures(A,B,C,D,E) are in the select class of the result panel from the main Fact table.
If user select’s any one of the 3 Optional prompts (D1,D2,D3),we have same 5 Measure Objects(DT_M1,DT_M2,DT_M3,DT_M4,DT_M5) in the Derived table .These objects (DT_M1,DT_M2,DT_M3,DT_M4,DT_M5) should come in to the report,Instead of showing the main measures objects(A,B,C,D,E).
Where as the user doesnt select the Optional Prompts(D1,D2,D3), the report should show the measures from Table (A,B,C,D,E).
Table Structure MainFactTable has columns(M1,M2,M3,M4,M5) and
Derived table has the below columns(DT_M1,DT_M2,DT_M3,DT_M4,DT_M5,D1,D2,D3)
Optional Prompts are D1,D2,D3.
These 2 tables has InnerJoin
Hi,
Try like below.
1. Create a Variables based on user selection of Optional prompts as
Var 1 = UserResponse(" Optionalprompt name1),
Var 2 = UserResponse(" Optionalprompt name2),
Var 3 = UserResponse(" Optionalprompt name2),
2. Now check whether above created Variables are null or not and based on that we can display the corresponding measures in the report as
If (IsNull(Var1) or IsNull(Var2) or IsNull(Var3) then M1 else DT_M1
Replicate above formula for all measure objects.
Cheers,
Suresh Babu Aluri
Hi balu,
First and foremost, please post such thing as forum discussion and not as blog post. Coming to the approach please find below.
Create Variables as below using IF and IspromptAnswered functions as below
D1FLAG=If(IspromptAnswered("D1");"1";"0")
D2FLAG=If(IspromptAnswered("D2");"1";"0")
D3FLAG=If(IspromptAnswered("D3");"1";"0")
ConcatenatedD1D2D3flags=D1FLAG+D2FLAG+D3FLAG
=If(ConcatenatedD1D2D3flags="000";M1;DT_M1)
I hope your requirement is to show the derived table measures even if any of the optional prompts is selected. let me know if otherwise
Replicate the same for other measures as well.
Thanks
Mallik
Please move your post to discussion, Blogs are for learning, tips & tricks etc.,