A custom group is used to group attribute elements and aggregate results in a report. This report is made up of custom group elements that consist of a header and filter condition. Each custom group element can have its own banding or filtering condition. A custom group allows us to group elements with the same or different attributes and metrics.
In the following example, if a data warehouse contain different countries but you want to group those countries by region, you can use custom groups; the American region custom group element displays countries such as Canada, the USA, and so on as other custom group elements:
A custom group definition is made up of:
Custom group options are:
To enable advanced qualification:
Attribute qualification: Show the revenue generated between 2005 and 2009, so the qualification is based on the attribute Calendar Year:
Based on the selection, the data on the report will be displayed as follows:
Set qualification: Show the top three organizations with the highest revenue, so the qualification is based on the metric Total Sales:
Based on the selection, the data on the report will be displayed as follows:
Custom group banding qualification: Rank and group the organizations, according to the revenue generated:
Based on the selection, the data on the report will be displayed as follows:
We can have different attributes in the same custom group; the following is an example.
Custom groups generate at least one query per element. Many temporary tables may be created to hold intermediate data. They are SQL-intensive and generate many passes. So, if we see the SQL for the previously mentioned custom group, we will see multiple passes and three different queries for each element.
The user can change the way MicroStrategy treats SQL using the intermediate table type. For that, in SQL view, go to Data | VLDB properties | Tables | Intermediate table types.
Select Temporary View to make SQL more presentable and understandable.
We can modify the display of custom group at different levels, as follows:
Individual element of the custom group:
In the custom group editor, right-click on the custom group element and select a display option:
All elements of the custom group:
In the custom group editor, select Custom Group | Options:
All custom groups on the report:
In the report editor, select Data | Report Data Options | Object display:
All custom groups in the project for a specific user:
Within the developer, select Tools | My Preferences | Editors | Custom Group:
When we create a custom group using attribute qualification, a report filter will be applied to the custom group element. Whereas, in the case of a custom group with metric qualification, a report filter will not be applied to the custom group element causing inaccurate data on the report. For example, if the custom group is created to display a product with sales greater than 50,000 and the report filter is created to display the sales for the year 2005. Here, even if the sales of the product are 25,000 in 2005, the product will still be displayed on the report, which means the report filter is evaluated after the custom group. We can avoid the previously mentioned scenario and define the interaction between the report filter and custom group by using the custom group interaction with report filter property, which is available under database instance level VLDB properties and is accessed by taking the following steps: