This document helps you to understand Dynamic Visibility of blocks on selection of Input controls with Ranking applied….

Hope it helps… 😉

Dynamic Visibility – Input Controls with Ranking.

Imagine that, you have only one Dimension and Measure in your report but you need to display measures with different ranking options. Let us consider an example, like I require dynamic selection for Top 5, Top 10, Bottom 5 and Bottom 10 sales of products where Net Value is measure and Product is dimension object. For this to achieve, follow the below mentioned steps:


Step 1: Create a Variable called Select Rank of type Dimension with value of “ ”  and click OK


Step 2: Create Input control on SELECT RANK variable. And while adding input control pass custom values (Top 5, Top 10, Bottom 5, and Bottom 10) into it and click OK.


Step 3: Create the following Variables

Rank: = Rank ([Net Value];[Product];Top)

Top 5: = [Rank] Where ([Rank] <=5)

Top 10: = [Rank] Where ([Rank] <=10)

Bottom 5: = [Rank] Where ([Rank]>=16 And [Rank] <=20)

Bottom 10: = [Rank] Where ([Rank]>=11 And [Rank] <=20)


Step 4: Add a measure variable which will return the appropriate measure, based on user selection.

Rank Selection = If ReportFilter ([Select Rank]) =”Top 5″ Then [Top 5]

Else If ReportFilter ([Select Rank]) =”Top 10″ Then [Top 10]

Else If ReportFilter ([Select Rank]) =”Bottom 5″ Then [Bottom 5]

Else If ReportFilter ([Select Rank]) =”Bottom 10″ Then [Bottom 10]

Else [Rank]


Step 5: Consider Five Blocks. Each block holds three objects Product, Net Value and Rank. Apply filter on each block along with using Hide dimension and place them relatively in same position.

Block 1: Add filter – Rank Selection – Inlist – 1,2,3,4,5

             Hide – Hide when — =Not([Select Rank] InList (“Top 5”)).

Block 2: Add filter – Rank Selection – Inlist – 1,2,3,4,5,6,7,8,9,10

             Hide – Hide when — =Not([Select Rank] InList (“Top 10”)).

Block 3: Add filter – Rank Selection – Inlist – 1,2,3,4,5

             Hide – Hide when — =Not([Select Rank] InList (“Bottom 5”)).

Block 4: Add filter – Rank Selection – Inlist – 1,2,3,4,5,6,7,8,9,10

             Hide – Hide when — =Not([Select Rank] InList (“Bottom 10”)).

Block 5: To represent all sales, so no need of having filter

             Hide – Hide when — =Not([Select Rank] = “”)

If you want graphical presentation of data, have copy of each block and turn that copied block to chart.


Step 6:  Now your report is ready to play.

Top 5:

Bottom 5:

All Sales:

To report this post you need to login first.

6 Comments

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

  1. FORAM SHAH

    Hi Sujitha,

    Thanks for sharing very helpful document.

    In the above example you mentioned about the Selected Rank Measure values for Top 5 as 1,2,3,4,5 but how these values are populated? And are the Select Rank and Selected Rank variables one and the same? If not please mention the definition of Selected Rank.

    Regards,

    Foram Shah

    (0) 
      1. FORAM SHAH

        Hi Sujitha,

        Thanks for your reply, the Rank Selection filter also did not work. So I filtered it wirh Rank for Top 5,and in Bottom 5, I filtered the table with rank as 16,17,18,19,20 and then it finally worked. So I want to know for the Bottom 5  result can I filter the table with Rank 1,2,3,4,5, and get my results for “Bottom 5”.

        Regards,

        Foram Shah

        (0) 

Leave a Reply