Dynamic sections: objective

Former Member · ‎05-30-2016

The document goal is to help end users to use input controls to change report behavior, design, appearance, etc. using some Web Intelligence functionalities that are only available for report creators.

The objective of this document is to show how can dynamically change the sections content using input controls without asking a report creator.

The main idea is to be able to add any dimension in a section to display the measures total per section and then also change the structure of the table detail.

You can create as many sections as needed and the process is the same for 1 or n sections.

Users can enter text or not for a given section to display total and remove the associated dimension from the detail table.

Dynamic sections: objective

To simplify the usage of input controls, the user is able to enter any text in a variable driven by an input control. The another variable transform the text in an existing dimension of the report.

Here is a screenshot of a report containing 5 empty sections.

A cell is added on top of the report to explain how to use the input controls:

Enter dimension name or part of dimension name in each Section.

Example:

Categ

Dynamic sections: variables definition

In the report there are 5 sections, so there are 5 variables used to enter text and 5 variables to transform the text in valid dimension.

Then in the detail table, each dimension of the body and each dimension name of the header contains a specific formula.

Variables used to enter the text and input controls

These variables (“Section 1” to “Section 5”) are very simple because there are defined as string with empty value à formula: =""

An input control is created and linked to each of these variables:

Type: Entry field
No default value
Operator: Equal to

Here is a screenshot of one of the input controls.

Variables used by the sections

Each section is based on a variable (“Hierarchy 1” to “Hierarchy 5) that interprets and transforms the text entered by the user into a valid dimension or an empty value.

Here is the formula of variable “Hierarchy 1” based on “Section 1”:

=If [Section 1] = "" Then ""

Else

If Pos(Lower(NameOf([Product Family])); Lower([Section 1])) > 0 Then [Product Family]

ElseIf Pos(Lower(NameOf([Product Department])); Lower([Section 1])) > 0 Then [Product Department]

ElseIf Pos(Lower(NameOf([Product Category])); Lower([Section 1])) > 0 Then [Product Category]

ElseIf Pos(Lower(NameOf([Product Subcategory])); Lower([Section 1])) > 0 Then [Product Subcategory]

ElseIf Pos(Lower(NameOf([Brand Name])); Lower([Section 1])) > 0 Then [Brand Name]

ElseIf Pos(Lower(NameOf([Product Name])); Lower([Section 1])) > 0 Then [Product Name]

ElseIf Pos(Lower(NameOf([Country])); Lower([Section 1])) > 0 Then [Country]

ElseIf Pos(Lower(NameOf([State Province])); Lower([Section 1])) > 0 Then [State Province]

ElseIf Pos(Lower(NameOf([City])); Lower([Section 1])) > 0 Then [City]

ElseIf Pos(Lower(NameOf([Customer Name])); Lower([Section 1])) > 0 Then [Customer Name]

ElseIf Pos(Lower(NameOf([Year])); Lower([Section 1])) > 0 Then [Year]

ElseIf Pos(Lower(NameOf([Quarter])); Lower([Section 1])) > 0 Then [Quarter]

ElseIf Pos(Lower(NameOf([Month])); Lower([Section 1])) > 0 Then [Month]

Else ""

It’s important to notice that the user is free to enter the full name of a dimension or a part of it. There is no constraint regarding match case.

So if part of the text entered by the user is contained in a name of the dimension then we transform it into the dimension itself.

So if 2 or more dimensions contain the same part of text, then only the first 1 in the expression is taken into account.

In each section there is a total table that is displayed only if the section is not empty.

Here is a formula for one of the measures used in the table: =If [Hierarchy 1] <> "" Then Sum([Store Cost])

For this table the option “Hide when empty” is checked. It means that if the section is empty, all measures are not filled then the table is hidden.

Detail table formulas

The body

Now for each dimension in the body there is a formula.

Here is the formula for the Country dimension:

=If [Hierarchy 1] <> [Country]

And [Hierarchy 2] <> [Country]

And [Hierarchy 3] <> [Country]

And [Hierarchy 4] <> [Country]

And [Hierarchy 5] <> [Country]

Then [Country]

Here is the formula for the Year dimension:

=If [Hierarchy 1] <> FormatNumber([Year];"#")

And [Hierarchy 2] <> FormatNumber([Year];"#")

And [Hierarchy 3] <> FormatNumber([Year];"#")

And [Hierarchy 4] <> FormatNumber([Year];"#")

And [Hierarchy 5] <> FormatNumber([Year];"#")

Then [Year]

And so on for each dimension.

The idea is to display the dimension only if the dimension is not in a section, otherwise it’s empty.

The header

Now for each dimension in the header there is a formula.

Here is the formula for the Country dimension:

=If [Hierarchy 1] <> [Country]

And [Hierarchy 2] <> [Country]

And [Hierarchy 3] <> [Country]

And [Hierarchy 4] <> [Country]

And [Hierarchy 5] <> [Country]

Then NameOf([Country])

Here is the formula for the Year dimension:

=If [Hierarchy 1] <> FormatNumber([Year];"#")

And [Hierarchy 2] <> FormatNumber([Year];"#")

And [Hierarchy 3] <> FormatNumber([Year];"#")

And [Hierarchy 4] <> FormatNumber([Year];"#")

And [Hierarchy 5] <> FormatNumber([Year];"#")

Then NameOf([Year])

And so on for each dimension.

The idea is to display the name of a dimension only if the dimension is not in a section, otherwise it’s empty.

Formatting rules

For each dimension present in a section and therefore removed from the table, I created a formatting rule “Empty cell” with the following parameters:

If Cell contents is null
- Text size: 6
- Border: No border

As the table contains no border, we have the feeling that the column has been removed from the report.

So I can play with the sections, to display different report contents:

You can view the video published on Youtube: https://youtu.be/MrR0i_lOzyY

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

Didier MAZOUE