Skip to Content

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

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member

    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

    (0) 
  2. Former Member

    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

    (0) 

Leave a Reply