Intellicus Enterprise Reporting and Business Insights 18.1

Working with Cross Tab Reports

0 views June 28, 2020 0

To create a Cross-Tab report, you must select at least three columns from the data source to define:

  • Column heading
  • Row Heading
  • Data field (summary field) — intersection of column and row on common criteria

On the Intellicus Studio home screen, click Insert > Cross-Tab. The cursor changes 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 are prompted to create a bound or an unbound report.

Cross-Tab Report
Figure 1: Creating Cross-Tab Report

  • Click Yes to create a Bound Cross-Tab report.
  • Click No to create an Unbound Cross-Tab report.

Note: In case of bound cross-tabs, cross-tab is created depending on the section where the cross-tab component is placed because the data made available for cross-tab depends on data source used for report.
In case of unbound cross-tabs, irrespective of the section where the cross-tab is placed, the entire records received are considered for creating the cross-tab. This is because the data made available for the chart comes from its own data source.

To do settings for bound or unbound reports,

Cross-Tab Report Properties
Figure 2: Cross-Tab Report Properties

Right-click on the report and click Properties. The CrossTab Properties screen is displayed with four submenus:

Cross-Tab Properties Sub-Menus
Figure 3: Cross-Tab Properties Sub-Menus

  • Data Source
  • Format
  • Settings
  • Runtime

The process of creation of CrossTab is same for both Bound and Unbound except for in few steps.

When you create a Bound Cross-Tab report, the parent report’s SQL statement and parent report’s connection is used. In this case, the data source tab is grayed out and the Connection Name and SQL Query fields are disabled.

Data Source tab
Figure 3: Data Source tab for Bound Report

When you create an Unbound Cross-Tab report, you can define a database connection for Cross-Tab report as well as write a customized SQL statement for data retrieval and select an existing query object. In this case, the data source tab is enabled as shown in the next section.

Data Source tab
Figure 4: Data Source tab for Unbound Report

Defining the Data Source

In the Data Source tab, you can define the source database from which data is used to create cross-tab report.

Cross-Tab Data Source
Figure 5: Cross-Tab Data Source

  • Source QO: This allows you to select the source query object.
    • Select Query: This drop-down lists all the query objects available in the repository. Select a query object to use.
  • Specify SQL: This allows you to select the connection, write SQL query, and select available fields.
    • Connection: This list contains 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.
    • SQL Query: Enter the SQL query that would retrieve the required fields from the database.
    • Available fields: This displays fields that were retrieved from the database as a result of the SQL entered in the SQL Query field.
  • SQL Designer: Click SQL Designer to open the designer to design an SQL query by dragging and dropping as well as by writing a query.

Note: If the SQL Designer is opened from the Cross-Tab Properties screen, the Sort and Filter tabs are disabled.

  • Verify SQL: Click Verify SQL 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.
  • Field Properties: Click to view and edit the field properties.Field Properties
    Figure 6: Field Properties
  • Selection Window: This window contains layout for the cross-tab. To define the data in this cross-tab, simply select Column Header or Row Header or Summary Filed from Cross tab view present in selection window and check columns from the Available Fields.Selection Window
    Figure 7: Selection Window

Formatting a Cross-Tab Report

Using the Format tab, you can format the Cross-Tab report using different foreground and background colors for distinctive display of column values. This allows you to select or unselect the columns used in the Cross-Tab report display. To change the format for any column, select the column from the selection window and do the settings.

Cross-Tab Format
Figure 8: 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 Function 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.

Format the following fields:

    • Title: Enter the title to be displayed for column headers for summary values.
    • Font: Select the font properties such as font type, font style, and font size for the text that appears in selected cross-tab item.
    • Align: Select the alignment based on the summary value. For example, left, right, or center.
      • Back color: Click to select the background color from the displayed palette. The selected back color takes effect on the selected item on cross-tab.
      • Fore color: Click to select the foreground color from the displayed palette. The selected fore color takes effect on the selected item on cross-tab.
      • Width: Enter the width of column (in pixels).
      • Height: Enter the height of row (in pixels)
    • Hyperlink: Click to hyperlink to another report or a URL with the selected cross-tab item. For more information, refer to Desktop Studio – Hyperlinking Reports document.
    • Conditional Formatting: Click to set up conditional formatting for the selected cross-tab item. For more information, refer to Desktop Studio – Conditional Features document.
    • Visible: De-select the check box if you want to use the field on cross-tab but do not 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. Else, select the check box to make the field visible.
    • Repeat on each page: Select the check box if the cross-tab extends to multiple pages and the headers are to be repeated.
    • Display Field: Select the field that should be displayed for selected item on cross-tab from the drop-down list. For example, field being used is ‘product number’, but field being displayed is ‘product name’.
    • Replace Zero With: Select the check box and specify the value that should be displayed when value for the field is Zero, that is, to replace zero values with another value (for example “Null”, “No Value” or “Zero”).
    • Format (Date): For date type data, set the format in which data should be presented.
    • Format (num): For number type data, specify the format in which data should appear.
    • 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.
    • 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): Enter the value 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.
  • 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.

Effect of XL
Figure 9: Effect of XL – On Span (First Cell, Repeat and Merge)

When you select a summary field column from the selection window following option are available under Format tab.

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).

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.

Cross-Tab General Settings
Figure 10: Cross-Tab General Settings

Provide the following information:

  • Title: Enter a title to appear on top-left corner of the cross-tab.
  • Style: Select a border style from the drop-down list. For example, Opaque or Transparent.
  • Theme: You can select the theme to apply to the cross-tab report display. This contains predefined format settings for the cross-tab reports. For example, official, original, nature, classic, and delicate.
    • Color: Click to select the color from the color picker.
  • BackColor: Select the check box for specifying background formatting related properties.
  • ForeColor: Select the check box for for specifying background formatting related properties.
  • Level: Select the level for color. Applicable when cross-tab has multiple row levels, for example, Country and within country, cities. Select the level where alternate color should be applied.
  • Border: 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.
  • Include in CSV: Value set in this dropdown box will decide if this cross-tab should be rendered when report output format is CSV.
  • 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.
  • 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.

Providing a Runtime Connection

Runtime tab enables 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.

Cross-Tab Runtime Connection
Figure 11: 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.