Intellicus Studio provides the facility to create Cross-Tab reports. The Cross-Tab reports are also known as matrix reports that convert three column data into matrix data. To create a Cross-Tab report you need to select at least three columns from the database to define:
- Column heading
- Row Heading, and,
- Data field — intersection of column and row on common criteria.
Creating a Cross-Tab Report
To create a Cross-Tab report, from the Insert menu, click Cross-Tab. Your cursor will change to a cross (+) symbol. Click and drag the mouse to mark an area for the Cross-Tab report in any section of the layout editor. When you release the click, you will be prompted for creating a bound or an unbound report.
Figure 1: Creating Cross-Tab Report
Bound Cross-Tab Report: On selecting Yes, Cross-Tab report will use: 1) parent report’s SQL statement, and, 2) parent report’s connection.
Unbound Cross-Tab Report: By selecting No, you can define a database connection for Cross-Tab report as well as write a customized SQL statement for data retrieval.
Important: In case of bound cross-tabs, cross-tab is created depending on the section where cross-tab component is placed. This is because the data made available for cross-tab depends on data source used for report. In case of unbound cross-tabs, this is not the case. In whichever section you place the cross-tab, entire records received, will be considered for creating the cross-tab. This is because the data made available for chart comes from its own data source.
To do settings for bound or unbound reports, right-click on the report and select the Properties menu item. On doing this, a tabbed dialog box will appear.
This dialog has four tab pages that would help you do the required settings. Explanation given in the tabs is for both: unbound and bound reports. But wherever a difference occurs, a note has been provided.
Defining the Data Source
In the Data Source tab you need to define the source database whose data will be used in creating the cross-tab report.
Figure 2: Cross-Tab Data Source
Note: If you are creating bound Cross-Tab report the Connection Name and the SQL Query fields will be disabled. Rest of the options remains the same.
Note: For OLAP type connections under SQL editor, Design tab, Filters tab, Sort tab, Stored Proc. tab will not be available. You can use Edit tab to write the query.
Connection Name: This list will contain the database connection names defined earlier. Select the connection name to be used for creating the cross-tab report. Crosstab can be created using OLAP type of connection also.
Select Query: This drop down box lists all the query objects available in the repository. Select a query object to use.
SQL Query: Here you have to type the SQL that would retrieve the required fields from the database. These fields will then be used to denote the column headings for the cross-tab report. Remember to select those fields that have something in common. For example: To get the department wise salary of employees, select employee name/code, department, and salary in the select statement.
SQL Editor button: Click this button to get SQL Editor dialog box. This dialog box has features to design an SQL by drag and drop as well as by typing a query.
Note: The SQL Editor opened from here will have Sort and Filter tab disabled.
Verify SQL button: Click this button to check the SQL for errors. This button can also be used as a refresh button for the fields that are defined for retrieval using the SQL provided above. For example, once you have selected the fields from Available Fields to add in the cross-tab, and have switched over to some other tab, when you return back to this tab the unselected fields will not be displayed.
Available fields: This displays fields that were retrieved from the database as a result of the SQL entered in the SQL Query field.
Selection window: This window contains layout for the cross-tab. To define the data in this cross-tab, simply select a column from the Available Fields and drop it on the pertinent column. You can either select individual or multiple columns.
To select random columns use the CTRL key and click on the columns you want in your report. Now drag all selected columns at once. To select a regular range of columns, select one column and while keeping the <SHIFT> key pressed, select the column that is last in the range.
Formatting a Cross-Tab Report
Format tab enables you to format the Cross-Tab using different foreground and background colors for distinctive display of column values. This will also allow you to select or unselect the columns used in the Cross-Tab report display. To change the format for any column, you have to first select the column from the selection window and then do the settings.
Figure 3: Cross-Tab Format
The options provided in this dialog will be available for formatting depending on the column name selected from the selection window.
For example, if you select a summary field column from the selection window, a new field called Summary Type will be displayed on the dialog, which remains hidden when other types of fields are selected. Also, when Grand Total column is selected, the section selection field is not available for selection.
Note: Options available on Format tab depends on type of cell (row header, column header, summary field, row total or column total) selected for formatting. For example, some of the formatting options will not available when you select Row total or column total.
Align: Based on the summary value, set the alignment to left, right, or center.
Title: Specify the title that will appear for column headers for summary values.
Back color: Click this field and select the background color from the displayed palette. This would take effect on the selected item on cross-tab.
Fore color: Similar to the background color selection, you can change the foreground (font) color of the selected item on cross-tab.
Display Field: Select the field that should be displayed for selected item on cross-tab. For example, field being used is “product number”, but field being displayed is “product name.”
Replace Zero With: To replace zero values with another value (for example “Null”, “No Value” or “Zero”), select this checkbox and specify the value that should be displayed when value for the field is Zero.
Visible checkbox: Clear this checkbox if you want to use the field on cross-tab but don’t want to place it on cross-tab as selected item. For example, you need a row summary but you do not want summary cell. And you want a summary cell, but do not want row summary.
Repeat on each page checkbox: If cross-tab may extend to multiple pages, check it to repeat headers on all pages.
XL – On Span: If your cross-tab data row gets repeated, select the right option among:
- First Cell: To get data value only in the first header.
- Repeat: To get data value in all headers including total’s cell.
- Merge: To get data value only in one header and merge all other cells into one.
Figure 4: Effect of XL – On Span (First Cell, Repeat and Merge)
Font: Open Font dialog box and specify font properties for text that will appear in selected cross-tab item.
Format (Date): For date type data, set the format in which data should be presented.
Date Group By: For date type data, select the group option. There will one column for each group. Group can be formed by date, week, month and year.
Format (num): For number type data, specify the format in which data should appear.
Num Group By: For number type data, specify the group option. There will be one column for each group.
Note: If there is no data for a range that row / column will not appear on cross-tab.
First (N): To include only few top values in the cross-tab. For example, in a cross-tab having sales summary field, specify 20 to include records having top 20 sales summaries.
Height (not applicable to column headers): Enter the height of row in pixels).
Width (not applicable to row headers): Enter the width of column (in pixels).
Data Format: For selected summary cell, select the format in which the data should be represented.
Summary function: The function to be applied to calculate the summary.
Summary Level (Applicable to row summary and column summary): Set data record to calculate summary based on the actual data records. Select respective header to calculate summary based on rows / columns of that header appearing on cross-tab.
Caption: Name to be given to header of selected column, row or summary.
Position (Applicable to row summary and column summary): Set Before to have summary as first row (row summary) or first column (column summary). Set After to have summary as last row (row summary) or last column (column summary).
This is to associate another report or a URL with the selected cross-tab item. Click Hyperlink button to open Hyperlink Options dialog.
This is to setup conditional formatting for the selected cross-tab item. Click the Conditional Formatting button to open Conditional Formatting dialog box.
If you want to apply section-wise format settings, select the required section from the section selection list box. This will also allow you to select or unselect the column values from the Cross-Tab report.
Selecting General Settings
You can use the Settings tab for distinctive display of the Cross-Tab report. You can format the background and the border for the selected Cross-Tab report. This would help in easy differentiation between different values on the Cross-Tab.
Figure 5: Cross-Tab Settings
Title: Specify text that will appear on top-left corner of the cross-tab.
Show Cell Caption: Check (select) this checkbox to have field captions on row header and column header. Keeping this clear will have first row as actual column values and leftmost column as actual row values.
Include in CSV
Value set in this dropdown box will decide if this cross-tab should be rendered when report output format is CSV.
Never: Exclude cross-tab component
Once: Select this to render the first instance of cross-tab component, ignore all other instances. For example, when placed on group header or footer, it will render cross-tab only for the first group.
Repetitive: Render cross-tab for all of its occurrences.
Specify background formatting related properties.
Style: Set it as Opaque to apply background color. Keep it Transparent for not applying background color.
Scheme: You can select the scheme to apply to the cross-tab report display. This contains predefined format settings for the cross-tab reports.
Color: Click button to get color picker and set the background color for cross-tab component.
Select the checkbox to apply border to cross-tab. Set other properties:
- Style: Select a border style from the drop-down.
- Width: Specify border width.
- Color: Select border color.
If cross-tab may extend to multiple pages, check Horizontally to make sure last column is not split between two pages, check Vertically to make sure last row is not split between two pages.
To apply an alternate color, select Alternate BackColor checkbox and select the color using the box next to it. To apply an alternate text color, select Alternate ForeColor checkbox and select the color using the box next to it.
Level: Applicable when cross-tab has multiple row levels, for example, Country and within country, cities. Select the level where alternate color should be applied.
Providing a Runtime Connection
Runtime tab will enable you to select the database connection for the Cross-Tab report. The data source name chosen from the ‘Data Source tab’ is used to create the Cross-Tab layout. However, to populate the data in the report, you need to use the named (configured) connections in the report engine.
As the report layouts created by using the Intellicus Studio can be run on any computer (other than the one on which the report engine is installed), it becomes mandatory to connect to the database using the name with which the database connection was configured in the report engine.
Figure 6: Cross-Tab Runtime Connection
The database connections mentioned here are configured• connections in the report engine.
Use Parent’s: Select this option to select the database connection that the parent report has used (this will appear as selected by default if you are working with a sub-report).
Use Engine Default: Select this option to use the connection that was marked as default from the Intellicus Connection Manager dialog (this will appear as selected by default if you are working with a report that is not a sub-report).
Use Named: Select this option to provide the named (configured) connection to fetch data for the report. This will override the default connection.