Skip to Content
Author's profile photo balu polimera
balu polimera
May 8, 2013 Less than a 1 minute read

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

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      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

      Author's profile photo Former Member
      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

      Author's profile photo RUC ..
      RUC ..

      Please move your post to discussion, Blogs are for learning, tips & tricks etc.,