Table

Info Center > Configure & Design > Report Templates > Add Report Template / Edit > Gear Icon

A Table is configured using a three step wizard:

• Step 1 - Select Columns
• Step 2 - Ordering and Grouping
• Step 3 - Filtering

Step 1 - Select Columns

Format

• Dataset Name - The name of the data set associated with this table.
• Title - Enter a title for the report part.
• Title Alignment - Left, Right, Center
• Show Title - If checked, the title displays on the published report with this report part. If blank, the title is hidden.
• Page Break - If enabled, forces a page break next to this report part. Options include No Page Break, Before, After, Before and After. A break in either cell, before or after, has precedence over no break in the other cell. An After page break is ignored if a table runs beyond the length of the page in the other cell.
• Text Size - Extra Small, Small, Normal, Large.

Columns / Column Selections

Drag-and-drop columns from the Columns list to the Column Selections list.

• Delete Row - Removes a selected column from the list.
• Column - A column selected for inclusion in the published report.
• Alias - Displays as the heading for a selected column, in place of the column name.
• Aggregate - Aggregate options return a single numeric value calculated from multiple cells in a selected column. For example, the aggregate option COUNT returns the number of non-null values in a selected column. Except for COUNT or COUNT_BIG, aggregate functions ignore null values.
  • AVG - Returns the average of the values in a group. Null values are ignored.
  • COUNT / COUNT_BIG - Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.
  • MAX - Returns the maximum value in a group.
  • MIN - Returns the minimum value in a group.
  • STDEV - Returns the statistical standard deviation of all values in a group.
  • STDEVP - Returns the statistical standard deviation for the population for all values in a group.
  • SUM - Returns the sum of all the values in a group. SUM can be used with numeric columns only. Null values are ignored.
  • VAR - Returns the statistical variance of all values in a group.
  • VARP - Returns the statistical variance for the population for all values in a group.
• Weight - Determines the percentage width of each column by assigning a numerical value. For example, if four rows are sequentially weighted with the values 4, 3,2,1, then,
  • The first row, with a weight of 4, is 40% of the sum of all weight values, 10.
  • The second row, with a weight of 3, is 30% of the sum of all weight values, 10.
  • The third row, with a weight of 2, is 20% of the sum of all weight values, 10.
  • The fourth row, with a weight of 1, is 10% of the sum of all weight values, 10.

Step 2 - Ordering and Grouping

Order by

Determines the order data is displayed, from first to last. Multiple rows can be configured, with a higher row having precedence over a lower row. A selected order by column does not have be displayed in the report.

• Add Row - Adds an order by row.
• Delete Row - Deletes an order by row.
• Column - Selects a column used to determine the order data displayed, from first to last.
• Aggregate - If an aggregate option is selected, the sort order is applied to the numeric value returned by the aggregate option instead of the selected column. See the description for each aggregation option described above.
• Sort Order - Ascending or Descending. Applies to either the selected column or to the aggregate option, if one is specified.

Group by

Returned rows of data can by organized into subheadings and subgroups by selecting "group by" columns. Multiple levels of "group by" columns are supported. Applies to table parts only.

• Add Row - Adds a group by row.
• Delete Row - Deletes a group by row.
• Column - The column selected to group returned rows of data.

Step 3 - Filters

The data displayed can be limited by specialized data filters.

Note: Additional filtering options display when a report definition or report template is run or scheduled.

Row Filter

• Limit Type - The type of row limit specified.
  • Top N - Limits data returned to the first N number of rows returned. Example: If the Limit is 10, the first 10 rows of 300 rows available are returned. Result: 10 rows are returned.
  • Top N % - Limits data returned to the first N % of rows returned. Example: If the Limit is 10 the first 10% of 300 available rows are returned. Result: 30 rows are returned.
• Limit - The number specified for the Limit Type field.
• Select Distinct - If checked, duplicate rows are not returned. For all columns displayed in the report, the values in a row must match the values of another row to be considered a duplicate.

Date Filter

Date filters only display if date/time columns are included in the report part.

• Date Filter Column - Select a date/time column to filter the data queried by this part of the report.

  Note: You must select a date/time column for the other date filter options below to have any effect.

• Time Range Type - Select a time period to filter the data queried for this part of the report.
  • Predefined Ranges - This Week, Last Week, This Month, Last Month, This Quarter, Last Quarter.
  • Inherit from Report - When you schedule or run a report, Date Filter options display on the Filters tab and determine the time period used to query data for this part of the report.
  • Last N Days - Enter the value of N in the Number of Days field.
  • Fixed Range - Enter a Start DateTime and End DateTime.
• Number of Days - Enter the value of N in this field if Last N Days was selected.
• Start DateTime - Select a start date and time if Fixed Range was selected.
• End DateTime - Select the end date and time if Fixed Range was selected.

Advanced Filters

Rows can be limited by comparing selected columns with specified values.

• Add Row - Adds a comparison row.
• Delete Row - Deletes a comparison row.
• Field - Selects a column used to compare with a specified value.
• Operator - The operator used to compare a selected column with a specified value.
  • Equal (=) Enter a comma separated list of values to create an OR statement.
  • Not Equal (!=) Enter a comma separated list of values to create an OR statement.
  • Like - If a selected column contains this specified value as a substring, then display this row. Enter a comma separated list of values to create an OR statement.
  • Not Like - If a selected column does not contain this specified value as a substring, then display this row. Enter a comma separated list of values to create an OR statement.
  • Greater Than (>)
  • Greater Than or Equal (>=)
  • Less Than (<)
  • Less Than Or Equal (<=)
  • Between - If the selected column is between two string values, separated by a comma, display this row. Comparison is from left to right. Examples:
    • Number field format - 1000,9999
    • String field format - aaa,zzz
    • Date field format - 01-01-2014,03-31-2014
  • Is Empty - If the selected column does not have characters, display this row.
  • Is Null - If the selected column is null, display this row.
  • Not Empty - If the selected column has characters, display this row.
  • Not Null - If a selected column is not null, display this row.
• Value - The specified value.