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:
- A name, which we define while creating a custom group element. Under this name, we group different attribute elements. This is displayed as a row in the report.
- Qualification, which is a condition that should be met to display data on the report. There are different qualification types and we can combine them in the same custom group. Qualification types include:
Custom group options are:
- Attribute qualification: Displays data based on a value of an attribute
- Set qualification: Displays data based on the value, rank, or percentage of a metric
- Shortcut to a Report: Displays data based on existing reports
- Shortcut to a Filter: Displays data by using existing filters
- Custom Group Banding qualification: Displays data into multiple ranges; bands are based on the metric selected
- Advanced qualification: Allows the user to use pass-through functions
To enable advanced qualification:
- Select Tools | My preferences.
- Select the Editor tab and then Custom Group Options.
- Check the Show advanced filter qualification checkbox.
- Browse the folder in which you want to create a new custom group; best practice is to create a custom group under public objects and a custom group folder.
- Select File | New | Custom Group | Empty custom group, and OK. The custom group editor is displayed.
- Under custom group definition, create the custom group element either by double-clicking or by dragging the object onto the editor.
- Give a name to the group element and then define conditions to filter data by selecting an attribute, metric, report, or filter, based on the options provided.
- Repeat steps 3 and 4 to define additional custom group elements.
- Click Save and Close and type the name of a custom group.
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 group: See the following screenshot:
- Report: See the following screenshot:
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:
- Right-click the project.
- Select Project Configuration | Database Instances | VLDB Properties:
