Zoom, highlight data in tables

Zoom, highlight data in tables

The purpose of this document is to show how we can highlight or zoom part of a table using input controls.

We often have dimensions with a lot of values that we want to display in tables or crosstabs. The idea is to limit the page scrolling and have the table visible on one page or a limited number of pages.

To do so, we can display the labels in vertical mode (see SCN publication Display vertical labels in tables)

And last we use the smallest font possible like the below screenshot.

The idea is to play with input controls and select dimension members to highlight the values and the measure in the table or crosstab like the below screenshot.

To obtain this result I created 2 hidden blocks: 1 for the months and 1 for the subcategories.

Then I create 1 input control linked to the month block and 1 input control linked to the subcategory block.

Then I created several variables.

The first variable named “Current Report Filter” contains the following formula:

=Replace(Replace(ReportFilterSummary(ReportName());Char(13);””);Char(10);””)

The formula takes the data from the ReportFilterSummary() function and remove all line feeds. This variable is used by all other derived variables.

Now let’s have a look on the variables used for the subcategory dimension:

1. Variable “Block ‘Subcategory Filter’”: =Substr([Current Report Filter];Pos([Current Report Filter];”Filter on Block Subcategory Filter”); Length([Current Report Filter]))
   The formula extracts the filter information concerning the hidden block for Subcategories named “Subcategory Filter”
2. Variable “‘Subcategory’ In List”: =Substr([Block ‘Subcategory Filter’];Pos([Block ‘Subcategory Filter’];NameOf([Subcategory]) + ” In List { “);Length([Block ‘Subcategory Filter’]))
   The formula extracts the filter information concerning the dimension Subcategory in the hidden block for Subcategories. This variable is based on the previous variable.
3. Variable “Filtered ‘Subcategory’ In List”: =Char(34) + Replace(Left(Substr([‘Subcategory’ In List];Pos([‘Subcategory’ In List];”{“)+2;Length([‘Subcategory’ In List]));Pos(Substr([‘Subcategory’ In List];Pos([‘Subcategory’ In List];”{“)+2;Length([‘Subcategory’ In List]));” }”)-1); “, “; Char(34) +”,” + Char(34)) + Char(34)
   The formula extracts the subcategories values and add a double-quote as prefix and suffix for each value. This variable is based on the previous variable.
   Here is a result for this variable: “Caps”,”Pumps”,”Bike Stands”
4. Variable “Is Subcategory In List”: =If Match([Filtered ‘Subcategory’ In List]; “*” + Char(34)+ [Subcategory] + Char(34) + “*”) Then 1 Else 0
   The formula returns a Boolean if the subcategory is in the filtered subcategories. This variable is based on the previous variable.

I created 4 other similar variables for the months.

Now I created 1 formatting rule for subcategory, 1 for month and 1 for the measure.

The formatting rule for subcategory is based on the variable “Is Subcategory In List”. Font size, background color and foreground color are changed if the subcategory is in the filter.

The formatting rule for the measure is based on the variables “Is Subcategory In List” and “Is Month In List”. Font size, background color and foreground color are change if the subcategory is in the filter or if the month is in the filter of if both are in the filter.

Now I can play with the input controls and highlight different parts of the crosstab.

In the attached document I also did a similar exercise with input controls using equal operator on a second report.

You can download the Web Intelligence report attached to that publication.

Didier MAZOUE