Scroll

CrossTab Reports

The CrossTab Report wizard is an interactive tool which will walk through the process of creating a new CrossTab Report. All of the settings in the CrossTab Report wizard can be modified in the report designer after the report is created.

To navigate the wizard, either click the desired tab or use the buttons at the bottom.

The CrossTab Report wizard has four subsections: Name, Categories, Filters, and Layout. The Name, Categories, and Layout sections must be completed in order to create a CrossTab Report

 

Name Section

In the Name section, enter a report name and click on the Folder where the report will be saved.

The report name can be up to 255 characters long. The following special characters may not be used: ? : / \ * " < >

A report’s description appears at the bottom of the Main Menu when it is selected. The description text may also be used to search for a report.

Note: You cannot create a report inside of a folder that is read-only ().

 screen.ct_namesection.png

Categories Section

In the Categories section, select the Data Categories that you would like to have access to on the report. It is important to understand the difference between Data Categories and Data Fields.

Data Category:

A Data Category is a data object that has several attributes. For example, Students is a category; each student has an ID, a major, an advisor, etc.

Data Field:

A Data Field is a single attribute within a category. For example, Students.ID is the numeric value that identifies a specific student.

screen.ct_categoriessection.png

 The following actions may be preformed within the Categories section:

  • To add a Data Category, either drag and drop it to the Category Name Column, use the icon.add.pngAdd button, or double-click the field.
  • To search for a specific Data Category or folder, type its name into the Search box.
  • To see what Data Fields are in a Data Category, click the information button.
  • Check the Suppress Duplicates box to suppress duplicate information from appearing on the report.
  • To remove a Data Category, click the delete button ().

 

Filters Section

In the Filters section, create statements to filter the data at runtime.

screen.ct_filtersection.png

A date filter applied to output data from the years 2015 to 2016

There is no limit to the number of filters that can be defined. Filters can be numeric (up to eight decimals) or alphanumeric.

The following actions may be performed within the Filters section:

  • To filter a Data Field, either drag and drop it to the Filter By column, use the icon.add.pngAdd button, or double-click it.
  • To filter a Data Field using a custom or built-in Exago formula, click the icon.add.pngAdd Formula button (v2018.2+). For more information, see the Filter by Formula article.
  • Use the up () and down () arrows to indicate the filter priority.
  • To remove a filter, click the delete button ().
  • Set the operator by selecting it from the operator drop-down.
  • Set the filter value by either entering it manually or selecting a value from the dropdown. If the Data Field is a date, the calendar and function buttons can be used to select a value.
  • Check Prompt for Value to allow the filter to be modified at the time the report is executed.
  • Select AND With Next Filter to require that the selected filter and the one below it both evaluate to true. Choose OR With Next Filter to require that either be true.
  • Check Group With Next Filter to specify the precedence of the filters. Filters can be nested indefinitely by using the following keyboard shortcuts while a filter is selected:
    • Ctrl + [ adds an open-parenthesis before the selected filter.
    • Ctrl + ] adds a close-parenthesis after the selected filter.
    • Ctrl + Shift + [ removes an open-parenthesis from before the selected filter.
    • Ctrl + Shift + ] removes a close-parenthesis from after the selected filter.
       

Layout Section

In the Layout section, design the CrossTab by moving Data Fields into the Row Header, Column Header, and Tabulation Data panels.

screen.ct_layoutsection.png

Row Headers

Row Headers expand a CrossTab vertically. A CrossTab has a row for each unique value of a Row Header. For example, if you were using sales data, you may have the Row Headers Category.CategoryName and Products.ProductName to provide rows for each product grouped by category.

The following actions may be performed on Row Headers:

  • To add a Row Header, either drag and drop it to the Row Header Source panel or use the Add Row Header button .
  • Click the Formula Editor button to insert a formula into the Row Header.
  • Click the Edit Header edit.png button to open the Header Options menu. In the Header Options menu, you can:
    • Set a label for the Row Header. This label will appear at the top of the CrossTab.
    • Select a sorting method and direction:
      • None: Does not sort the Row Headers.
      • Header Value (Text): Sorts the Row Header by its values as though they are text.
      • Header Value (Number): Sorts the Row Header by its values as though they were numbers.
      • Tabular Totals: Sorts the Row Header by the totals of the Tabulation Data.
Note: If there is more than one Row Header, the Header Options menu for the topmost Row Header will have Options for subtotals of Tabulation Data.
    • Select where to display subtotals by using the Placement dropdown:
      • None: Does not display subtotals.
      • Top: Displays subtotals above the Tabulation Data for each Row Header value.
      • Bottom: Displays subtotals below the Tabulation Data for each Row Header value.
    • Set a label for the subtotals.

      screen.ct_rowheader_options.png

  • Use the up () and down () arrows to rearrange the order of the Row Headers.
  • To remove a Row Header, click the delete button ().

 

Column Headers

Column Headers expand a CrossTab horizontally. A CrossTab has a column for each unique value of a Column Header. For example, if you were using sales data you may have the Column Headers Year({Order.OrderDate}) and MonthName({Orders.OrderDate}) to provide columns for each month grouped by year.

screen.ct_columnheader_example.png

 

  • To add a Column Header, either drag and drop it to the Column Header Source panel or use the Add Column Header button.
  • Click the Formula Editor button to insert a formula into the Column Header.
  • Click the Edit Header edit.png button to open the Header Options menu. In the Header Options menu, you can:
    • Set a label for the Column Header to appear at the top of the CrossTab.
    • Select a sorting method and direction:
      • None: Does not sort the Column Headers.
      • Header Value (Text): Sorts the Column Header by its values as though they were text.
      • Header Value (Number): Sorts the Column Header by its values as though they are numbers.
      • Tabular Totals: Sorts the Column Header by the totals of the Tabulation Data.
Note: If there is more than one Column Header the Header Options Menu for the topmost Column Header will have Options for subtotals of Tabulation Data.
    • Select where to display subtotals by using the Placement dropdown:
      • None: Does not display subtotals.
      • Left: Displays subtotals to the left of the Tabulation Data for each Column Header value.
      • Right: Displays subtotals to the right of the Tabulation Data for each Column Header value.

        screen.ct_columnheader_options.png

  • Set a label for the subtotals.
  • Use the up () and down () arrows to rearrange the order of the Column Headers.
  • To remove a Column Header, click the delete button ().

 

Tabulation Data

Tabulation Data provides information when data exists for both the Column Header and Row Header values. For example, if you have a Row Header on products and a Column Header on the month, then Tabulation Data of Orders.OrderID may use the Count function to display how many orders contained each product each month.

  • To add a Tabulation Data, either drag and drop it to the Tabulation Data panel or use the Add Tabulation Data button.
  • Click the Formula Editor button to insert a formula into the Tabulation Data.
  • Click the Edit Tabulation edit.png button to open the Tabulation Options menu. In the Tabulation Options menu, you can:
    • Sum: Totals the Tabulation Data.
    • Count: Counts the Tabulation Data.
    • Average: Takes the mean of the Tabulation Data.
    • Minimum: Displays the lowest value in the Tabulation Data.
    • Maximum: Displays the highest value in the Tabulation Data.
    • None: Displays the value of the Tabulation Data without applying any formula.
    • Aggregate: Display the result of the selected method.
    • Percent of Row: Display the result of the selected method as a percentage of the row total.
    • Percent of Column: Display the result of the selected method as a percentage of the column total.

      screen.ct_edittab_order_count.png

    • Set a label for the Tabulation Row to appear at the beginning of each row.
    • Use the method dropdown to select the summary function to be applied to the Tabulation Data:
    • Use the Value dropdown to select how the Tabulation Data should be displayed:
  • Use the up () and down () arrows to rearrange the order of the Tabulation Data.
  • To remove a Tabulation Data source, click the delete button ().

 

CrossTab Themes

The Theme dropdown can be used to quickly style the CrossTab using a predefined theme. Further styling can be done in the Report Designer.

 

CrossTab Options

Settings that affect the entire CrossTab are controlled in the CrossTab Options Menu. Open the CrossTab Options Menu by clicking the icon.generaloptions.png Options button. Using this menu, you can adjust the following settings:

General

  • Use the Row Headers Placement dropdown to determine how the Row Headers are displayed:
    • Columns: Display the Row Headers in columns from left to right in the order they appear in the Row Header Source panel.
    • Hierarchical: Display Row Headers in a hierarchical structure using indentation to display their order.
  • Check Repeat CrossTab Header every new page to repeat Row Header labels and Column Headers on each new page.

Grand Total Row

  • To get a total for each column, select Top or Bottom from the Placement dropdown in the Grand Total Row section and provide a label in the Label text box.

Grand Total Column

  • To get a total for each row, select Top or Bottom from the Placement dropdown in the Grand Total Column section and provide a label in the Label text box.

screen.ct_generaloptions.png

 

Preview

At the bottom of the Layout section, a preview will display how the CrossTab will appear based on the fields that have been added.

screen.ct_preview.png

A preview layout of the steps applied above

Finally, clicking the Finish button will create the report and open the CrossTab Report designer.

 

CrossTab Report Designer

Upon entering the CrossTab Report designer, the CrossTab Report that was created will appear in the Report Footer of report:

screen.ct_designer.png

To edit the CrossTab Report after its creation, right-click the report and click Edit CrossTab or double-click anywhere within the report. This will open the CrossTab Data Designer window.

The CrossTab Report designer offers all of the functionality as the Advanced Report designer, including:

  • Cell and report formatting and design
  • Visualizations
  • Filtering and sorting
  • Data aggregation and formula building

However, some of these features, namely visualizations and formulas, may not be added to the CrossTab Report itself, and must be added to another section of the report. If one of these items is added to the CrossTab Report, the following message will appear:

screen.ct_notapplicablewarning.png

CrossTab Report Formatting

CrossTab Reports often contain a large amount of information, and, as such, may appear cluttered if they are not properly formatted. The following list contains formatting recommendations to help avoid overwhelming reports:

  • Set predefined widths of rows containing long strings of characters or integers.
  • Use a theme or apply color to cells via cell formatting to help differentiate between certain sections.
  • Add borders via cell formatting to detail and header sections.
  • Abbreviating longer strings using the built-in Exago String functions. For example, Left(MonthName({Orders.OrderDate}), 3) will output the first three letters of each month.
  • In Options > Report Viewer, deselect Simulate PDF. This will allow larger data sets to span further than the simulated margins set by this option.
  • Add filters to limit the size of the data set.

 

 


Hidden Article Information

Article Author
Nick
created 2015-12-21 22:50:42 UTC
updated 2019-01-03 15:59:26 UTC

Labels
CrossTab Reports, Cross Tab, CrossTab, CrossTab Options,
Have more questions? Submit a request