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.,