Web Intelligence – Multi-Level Dynamic Break/Sort using Prompt

In this blog you will learn how to design and configure dynamic breaks and sort in web intelligence. This is a typical challenge that we face in most of the web-intelligence reporting tool as this is not something that the tool can offer directly. This would help a developer / power user trained in web intelligence / IDT (Information Design Tool) to create an effective web-intelligence reporting as breaks and sorts play a vital role in any reporting.

Problem Statement: Consider a scenario where you want to build a web-intelligence report with a requirement to break or sort dynamically and should also be able to prompt the user to select object to break/sort at user prompt. For Example, I want to build a web intelligence report which I should be able to select 2 objects ie., Service Area & Billing Provider for the report break or Sort at the prompt (leverage to use the option for scheduling as well). How do I go about accomplish this?

Following steps would exactly explain how to do this.

Note:This can be easily extended to any number of objects based on individual reporting needs.

Solution: This is a multi-step process that I layout below step by step in both Information Design Tool (IDT) as well as in web-intelligence tool.

IDT Steps: Open IDT Tool and create below static LOV/Parameters/Variables & Filters using below steps.

1. Create a static LOV named “Grouping by” at Business Layer (see below). This where you list out all the names of objects user is familiar with for dynamic break/sort or even can be used for scheduling.

2) Create parameters. In this example, I am going to create 2 parameters Group1 & Group2 as I am going to give 2 levels of break/sort. See below screen shot

Give appropriate Prompt text how you want the prompt to display. Here I am using the Prompt texts as “i. First item to group by:” for Group1 and “ii. Second item to group by:” for Group2 parameter.

3) Create Filter: Create a “Group” filter

The SQL expression for the above Group filter is as below:

@Prompt(Group1) IN (‘None’,’Service Area’,’Billing Provider’)

AND @Prompt(Group2) IN (‘None’,’Service Area’,’Billing Provider’)

Note: If you want to give more than 2 levels of breaks, you need to add that many number parameters and add that many number of ‘AND’ conditions here.

Caution: You won’t be able to parse Group filter as there is NO table associated to it

Once the universe is published to the repository, follow below Web Intelligence steps.

Web Intelligence Steps:

1. Open web intelligence tool, using the universe published to the repository, fetch the necessary objects needed for reporting along with a Break filter i.e. “Group” in this example dragged into “Query Filters” (see below)

2) Make sure to add the objects which are going to be mapped to those 2 breaks. See below screen shot.

3) Click “Run Query”. See below screen on what to expect.

4) Select ‘Service Area’ in Group1 prompt and select ‘Billing Provider’ in Group2 prompt (see below)

5) Execute the report and create following 2 variables at report level for each Break. In this example, we need to create 2 sets of variables as we are doing 2 levels of break.

6) Add the above variables to the report. See below.

7) Go to Manage Breaks and add those 2 variables created at reporting layer ie., Group1 and Group2

8) Report should look like below once after the unnecessary break header is cleaned up.

Note: If you are interested in Sorting only, open “Manage Sorts” and add Group1 and Group2 variables you created at report level. (see below)

Conclusion:  

You can use this solution towards any web intelligence reporting which would require dynamic breaks/sorts and to any number of levels. This just a template and it can be extended to any relational database and for any number of breaks and sorting scenarios.