Scroll

CrossTab Reports

CrossTab Wizard

The New CrossTab Wizard is an interactive tool which will walk through the process of creating a new CrossTab report. All of the settings in the New 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 New CrossTab Report Wizard has four sub tabs. The Name, Categories, and Layout tabs must be completed while the other tabs are optional.

 

Name Tab

In the Name Tab, 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. Avoid special characters such as ? : / \ * “ < >.

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 a folder that is read-only ().

 

 

Categories Tab

In the Categories Tab, select the Data Categories that you would like to have access to on the report. It is important to understand two terms: Data Category and Data Field.

 

Data Category

A Data Category is a data object that has several attributes. E.g., 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. E.g., Students.ID is the numeric value that identifies a specific student.

 

  • To add a Data Category, either drag and drop it to the Category Name Column, use thebutton, 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 Tab

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

 

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

  • To filter a Data Field, either drag and drop it to the Filter By column, use the button, or double-click it.
  • Use the up () and down () arrows to indicate the filter priority.
  • To remove a filter, click the delete button ().
  • Set the operator (equal to, less than, one of, etc.) 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 Tab

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

 

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 (see image below).

  • 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 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.
  • 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 Month({Orders.OrderDate}) to provide columns for each month grouped by year (see the image below).

 

  • 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 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.
    • 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 button () to open the Tabulation Options menu. In the Tabulation Options Menu, you can:
    • 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.
      • 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.
    • Use the Value dropdown to select how the Tabulation Data should be displayed.
      • 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 Colum: Display the result of the selected method as a percentage of the column total.
  • 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 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.

 

Preview

At the bottom of the Layout Tab, a preview will display how the Crosstab will appear based on the fields that have been added.  You can increase the size of the preview or hide it altogether by dragging the top of the preview box.


Hidden Article Information

Article Author
Nick
created 2015-12-21 22:50:42 UTC
updated 2016-05-24 22:12:03 UTC

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