Data Grouping on Crosstabs for Studio Reports

Larger the business, more complex becomes the analysis.  You need to have multiple fields on column header and same on row header.  Have a look at the example given below:

Figure 29: Grouping of Data

When to group

When summary field depends on two level of information, you may consider grouping.  Some examples:

• Zone and area expenditure report showing monthly expenditure by expenditure-heads.

• Quarterly flight occupancy chart showing occupancy by sector and flight number.

However, there is a dependency.  Data will appear correctly only if it is grouped logically.  You may not like to group data first by cities and within cities, by states!!

How to group

To do this, select multiple fields for column header or row header in the right sequence.  Selecting in right sequence will decide if the cross-tab will turn out to be logical or a meaningless matrix.

Figure 30: Grouping data on cross-tab

The field that is selected earlier will appear outside (major group).  The field that is selected later will appear inside (minor group).

Other steps in creating a cross-tab remain the same.  You can also create groups based on column headers.  The steps are logically the same as that of creating group by row headers.

Example

To get the cross-tab like the example given above,

Figure 31: Grouping of Data

We need record-set having the following fields:

• ExpenditureType

• DeptCode

• ZoneCode

• BranchCode

• ExpAmt

General steps to get this cross-tab in Intellicus Studio are:

1. Set the appropriate connection, create an SQL and refresh fields.

2. Place Cross-tab component preferably on Report Header.

3. Select ExpenditureType as first row header.

4. Select DeptCode as second row header.

5. Select ZoneCode as first column header.

6. Select BranchCode as second column header.

7. Select ExpAmt as summary field.

8. Apply formatting to row headers, column headers and summary field.