Chapter 3 The Tariscope program
3.5 Working with views
3.5.15 Grouping function
The grouping function allows you to aggregate rows displayed in a view using the specified parameters, summing up the data contained in other fields or to perform another aggregate function.
Aggregate functions can be used to the following actions:
- To sum data.
- To calculate a number of items in a group.
- To define a maximum value in a group.
- To define a minimum value in a group.
- To calculate an average value in a group.
Thus, in columns that were selected for grouping, you will see the unique data values in these columns, for example, types of calls. In all other columns the sums or values of another aggregate function will be displayed, for example, a total duration of calls. That is, you can calculate, for example, a total duration of calls for a certain period for each subscriber, or the number of calls for each of the categories, etc. You can group data on one or more fields. For each of the fields, you can choose an aggregate function separately.
The grouping feature is accessible for a view for calls, services or IP traffic.
Open or create a desired view in the Tariscope program. Right-click on the table title. In the appeared menu, select the Select fields item.
Define columns that are necessary in the aggregate report. Take in the mind that the more columns you will select, the longer period of the report generation will be. In addition, most of the fields does not often make sense in an aggregated form. Therefore, we recommend leaving only those columns that are needed for you.
Then you can go to the grouping. Right-click on the title of a desired column, and in the appeared menu, select Group by → By this field. The data will be grouped.
To undo the grouping function, right-click on the title of the column and in the appeared menu, select the Cancel group by item.
Consider the example of a rapid creation of a report with using the grouping function.
Example
Let's create a report on the costs of calls of each subscriber for the previous month.
We open a new view. In the appeared Filter window we set the following filter options. In the Period list, we select the Previous month item. In the Call type box we select the following types: Local, National, International, Mobile. This example of the Filter window is shown in Figure 3.5.38.
Figure 3.5.38
Click OK.
In the view, we choose only two fields for display: From subscriber and Cost as shown in Figure 3.5.39.
Figure 3.5.39
Click OK. The view will contain only these two fields.
Next, we group data by the From subscriber field. For the Cost field the aggregate function is used as shown in Figure 3.5.40.
Figure 3.5.40
If necessary, you can sort the data in the table.
The result of grouping can be printed or exported to an external file.
You can use similar actions to create a wide range of different reports. But it should be borne in mind that Tariscope already contains a large number of ready-made forms of reports, so you can use these reports instead of the grouping function.
In conclusion, the knowledge of the SQL language is not required to use the grouping function. However, if you need to create more complex reports, you can use the Report Designer program, whose work is based on SQL queries. Therefore, we recall that the grouping function corresponds to the next expression of SQL: GROUP BY.
Aggregate functions used in the grouping function are translated into T-SQL functions:
- Sum: SUM(),
- Number of items in a group: COUNT(),
- Maximum value: MAX(),
- Minimum value: MIN(),
- Average value: AVG().
