To create our first cross-tab, let us have a detailed look at the cross-tab illustrated in the 1st chapter.
Figure 4: Cross Tab Arrangement
Generally, a cross-tab data arrangement has following components:
Row Headers (1): This contains field values that will be placed on 1st column of every row.
Column Headers (2): This contains field values that will be placed on 1st row of every column.
Summary fields (3): All the cells covered by row headers and column headers are summary fields. Intellicus supports more than one field for summary. That means, for each intersection of a row and column you can have more than one cells of summary.
Column Summary (4) and row summary: Generally, last row contains summary (sum or any other function specified) of values in the respective column. Similarly, last column would contain summary of values in respective row.
Tip: You can have multiple row-headers, column-headers as well as summaries. For example, row headers may have Region and Sales Executives; column headers may have product line and products.
Placing the cross-tab control on report
A report may have one or more section. The way Intellicus Report Server treats report components depends on the section where they are placed.
For example, a report component placed on detail section will be rendered every time a new record is processed. That would not be the case with a report component placed on page header.
Tip: A cross-tab processes all the records in a record-set before it is rendered. Safest location to place a cross-tab is report header. Do not place a cross-tab in detail section, since detail section is rendered for each record, which results in a wrongly rendered cross-tab.
However, if you have no choice, then an unbound cross-tab can be placed anywhere required.
Here are the steps to place a cross-tab control on report using tool bar button. On Desktop studio, when a report is open,
- Expand the section where you want to place a cross-tab.
- On toolbar, click .
- On the section where you want to place the cross-tab, click and mark top right corner of cross-tab, drag the mouse towards bottom right until you get right size of the cross-tab.
- In Create Bound Cross-tab report? Click Yes to create bound cross-tab and No to create unbound cross-tab.
The cross-tab has been placed.
Building the cross-tab
You will get meaningful information on a cross-tab only if you have selected right fields for row header, column header and summary field. Assuming that you have already placed a cross-tab control on the report and arranged for a data source all the activities related to building a cross-tab is carried out on Cross-tab Properties dialog box. They are:
Placing field for column header, row header and summary field.
Setting group preferences when a numeric type or date type value is selected for column header or row header.
Setting the right summary function for summary field.
Selecting suitable look and feel for the cross-tab.
To get cross-tab properties dialog box,
- Place the mouse pointer anywhere on the cross-tab control and right-click the mouse. A context menu appears.
- Click the option Properties. Crosstab Properties dialog box opens.
Placing field for column header
The figure below and boxes next to it shows how to select a field for column header.
Figure 5: Placing a field for the column header
Placing field for row header
The figure below and boxes next to it shows how to select a field for row header.
Figure 6: Placing a field for the row header
Numeric field as row header or column header
You need to take little care when setting up a numeric field as row header or column header.
If you place a numeric field as it is in a row header, you will get one row for each of the value of that field. For example, if you have a numeric value for Zones, then this may be fine.
If you are looking for departmental expenditure by expenditure range, then this method would not work. You have to set expenditure range, for example, 0 – 1000, 1001 – 2000, etc.
After selecting the right numeric field for column header or row header, do this:
Figure 7: Numeric field as row or column header
Important: When you set Numeric Group By, the cross-tab will display the starting number of the group. It will not indicate starting number and ending number of group. For example, for a group, 0 – 5, it will not show “ 0 – 5” in respective header. It will show “0”.
To get value of your preference in column header / row header which has numeric group, you need to use formula field (calculated field).
If there are no records representing a range, for example there is no data for group 6 – 9, that row / column will not be placed on cross-tab.
Date field as row header or column header
A date type field can be set as row header or column header.
If you place a date field as it is in a row header, you will get one row for each date. It is possible to group date records by:
- Day (Monday, Tuesday, etc)
- Month (Jan, Feb, March, etc)
- Quarter (Jan – March, April – June, July – Sep, Oct – Dec)
- Year (actual year number)
Figure 8: Date type field for row or column header
When date is grouped, the respective row header or column header will show the start date of the group. For example, quarter January 2006 – March 2006 will be represented by “1/1/2006”.
To make the best use of this feature, we suggest using Date Group By along with the most appropriate Date Format. For example, if you have grouped by Month, use Date format as MMM.
Placing the field as summary field
Figure 9: Placing field for summary
Selecting the right summary function
For numeric fields, generally sum function is selected. When you want summary on fields having type other than numeric, you may choose count.
The title set on Settings tab, or on Format tab for a row / column will appear on top – left cell of the cross-tab:
If title on Settings tab is specified, then it will appear on cross-tab. If this is not specified, in that case,
If title is specified after selecting column header, then it will appear on cross-tab. If this is not specified, in that case,
If title is specified after selecting row header, then it will appear on cross-tab.
Setting up cross-tab title (Format tab)
Figure 10: Title for row / column header
Basics of Look and Feel
Appearance of cross-tab makes a lot of difference. You can select specific colors for row header, column header as well as other components of a cross-tab.
You can also select one of the pre-designed color-theme too.
Figure 11: Setting look and feel for the cross-tab
Date on Row header
This imaginary example represents expenditure by branch, on lighting and heating for Months January, February and March.
Figure 12: Expenditure by branch
All you need to have to get this report, is a record-set having fields:
- Date of Bill
- BillAmount (or amount paid, or similar)
General steps in studio / arrangement of fields on cross-tab would be:
- Set the appropriate connection, create an SQL and refresh fields.
- Place Cross-tab component (preferably on Report Header section).
- Place Date of Bill on Row header.
- Set Date Group By as Month.
- Set Date Format as MMM and Alignment as Left.
- Place BranchCode on column header.
- Place BillAmount (or amount paid, etc.) in summary field.