Grouping Data

You can use the Group function in Web Intelligence to group values returned by an object into categories. These categories can then be used as the basis for aggregating measures at higher levels.

For example, the eFashion.unx universe contains Quarter and Quantity sold objects, which allow you to analyze the quantity sold totals for each quarter. However, if your reporting requirement is to display the quantity sold measure aggregated at the half-year of details, then the universe does not provide the objects that you need to meet this requirement.

By creating a Half Year variable based on the Quarter object, you can aggregate quantity sold totals for each half of the year. This data can then be presented in the same report with sold totals for each quarter.

Sometimes the Group function is not suitable for grouping data values, such as when you have too many values to select manually. In this situation, you can group by using the If() formula function.

For example, if you need to display revenue totals in both a table and a 3D pie chart aggregated for each of the eFashion performance types. The three performance types are Top Performers, Average Performers, and Low Performers.

Each store is assigned to the appropriate category based on the following business rules:

You need to display revenue figures including subtotals in the table and revenue breakdowns in the chart.

Begin by creating a table using the State and Sales revenue objects.

Next, create a Sales Revenue Performance dimension variable using one of the following formulas:

12
=If([Sales revenue] > 7500000 ; "Top Performers" ;
If([Sales revenue] Between (2000000 ; 7500000) ; "Average Performers" ; "Low Performers"))

Or

1234
=If [Sales revenue] > 7500000 Then "Top Performers"
ElseIf [Sales revenue] Between (2000000 ; 7500000)
Then "Average Performers"
Else "Low Performers"

If you add Sales Revenue Performance to the report, apply a break on Sales Revenue Performance, and insert Sum on the Sales revenue column, you create the first block.

To insert the chart, insert the 3D Doughnut chart into the report, and insert the Sales revenue measure on the Y-axis and the Sales Revenue Performance dimension on the X-axis.