Home

Welcome to the Contextual Help System!

This application is a full-featured business intelligence solution that runs in your browser. Technical and non-technical users can build interactive tabular reports, visualizations, and dashboards using intuitive, specialized designers.

Getting started

If you are new here, welcome! You can get started immediately by clicking the Create New Report [image] icon and selecting [image] ExpressView.

If you are a veteran reporter looking to take advantage of our advanced features, click the Create New Report [image] icon and select [image] Advanced Report.

Once you have made one or more reports, you can organize them onto a workspace by clicking the Create New Report [image] icon and selecting [image] Dashboard.

New Report menu

Managing reports

To see the reports that you and others have made, click the Browse Reports [image] icon. To locate a specific report, type the name of the report into the Search field.

Browse existing reports

Select a report and click the Run [image] icon to Run it. If there is no Run icon click the Menu [image] icon and select [image] Run Report.

You can run a report and save it as a PDF, RTF, CSV, or Excel (XLS) file. Click the Menu [image] icon, hover over [image] Export As, and select a format.

Report options menu

Double-click a report to Edit it. If a report has a lock icon [image] then it is read-only, and you cannot edit it. But you can make an editable duplicate of the report. Click the Menu [image] icon and select [image] Duplicate.

Managing folders

Reports are organized into folders. To make a new folder, click + Add Folder and enter a name for the folder.

Caution
A folder name cannot contain the following characters:
\ / : * ? " < > |

To move a report or folder into a folder, drag it into that folder.

If a folder has a lock icon [image] then it is read-only. You cannot edit or delete this folder or the folders and reports within it.

Why am I here?

Help tab and help buttons

Click the Get help [image] icon to open the help page for the feature on screen. Right now you are in the Home page of the Contextual Help.

If you do not want to see the Getting Started page anymore, you can instead choose a report to run when you start the app. Click the Change User Preferences [image] icon to open the User Preferences window. In the Startup Reports page, select a report and click [image] Add. Then click OK.

Choosing a startup report

To close this tab, click the Close this tab [image] icon.

How to use the help

The Contextual Help contains instructions and advice for using the application. Use the Contents, Index, and Search panes to find help for a feature.

Help tabs

Contents

The Contents pane shows all the pages in the Contextual Help. The page which is open is outlined.

If a page has an arrow [image] next to its name, click the arrow to expand or collapse its subtopics. Click a page title to open the page.

To see the next page in the contents, click the Next [image] icon in the top right of this window. To see the previous page in the contents, click the Previous [image] icon.

Index

The Index pane contains a list of keywords related to topics in the Contextual Help. Click a keyword to see the related page.

Type a word or phrase into the text field to narrow the list to keywords containing the word or phrase. Clear the text field to see the full list.

Search

The Search pane allows you to search for text anywhere in the Contextual Help. To find pages where a specific word or phrase is mentioned, type the word or phrase into the text field, then click the Search [image] icon or press Enter. The pages which contain the word or phrase will be listed under the text field.

Click a page title to open it. The word or phrase will be highlighted on the page. Click the Highlight [image] icon to toggle highlighting on or off.

To find pages which contain multiple words or phrases, type AND between each word or phrase.

To find pages which contain any one of several words or phrases, type OR between each word or phrase.

To find pages which do not contain a word or phrase, type NOT before that word or phrase.

Any combination of the AND, OR, and NOT keywords can be used in the same search text.

Example

line chart OR column chart OR bar chart AND NOT pie chart

Navigating help links

Some pages contain links to other parts of the Contextual Help. Links are in bold green text.

If you navigate away from a page, you can use the Back button in your web browser to return. Then you can use the Forward button visit that page again.

You can always return to this page by clicking the Home [image] icon in the top right of this window.


ExpressView

The ExpressView Designer is a data discovery and reporting tool that simplifies grouping, sorting, filtering, and aggregating data with a drag-and-drop interface. A chart can be added with a single click, and ExpressViews can be styled and saved as PDF, RTF, CSV, or Excel files.

The ExpressView Designer

Making an ExpressView

ExpressViews allow you to quickly see data and make reports without the minutiae of old-fashioned report building.

To make an ExpressView:

  1. Click the New Report [image] icon and select [image] ExpressView.

    The ExpressView Designer is divided into the Data pane on the left, and the Design pane in the middle. The Data pane comprises all of your accessible data categories, containing groups of related data fields. Click the Choose Data [image] icon to expand or collapse the Data pane. For more information, see Data Pane.

  2. Expand a category by clicking the arrow [image] icon. This shows the fields in that category.

  3. Drag a field onto the Design pane to add it to the ExpressView. This expands the field into a data column. It will only show placeholder data initially.

    Dragging a field onto the Design pane

  4. Continue to add fields as desired. Fields can be added or removed at any time.

    Tip
    As you add fields, unrelated fields will become unavailable. To learn about how fields relate to each other, see Joins.

  5. When you are satisfied with your data selection, click [image] Live Data to populate the ExpressView with data. You can now page through your data using the navigation icons [image] [image] [image] [image] in the toolbar.

    Truncated results displayed

    This icon and message indicates that the report has only returned a partial data set. There are fewer detail rows than the full set, and aggregate formulas and visualizations apply only to the data that was returned to the report.

    To get more data, click the icon and select either:

    • Generate +number to get the next number of data rows and add them to the existing report.
    • Generate All to get the full data set.

  6. Click the Save [image] icon to save the ExpressView. In the Settings window, enter a Name and select a folder where it should live. Then click Save Report Info.

These steps illustrate how quick it is to make a tabular report from scratch using ExpressView. But ExpressViews can be more than basic. You can make groups, charts, calculations, and customize the look of the report. And it is all designed to be easy to use. The following sections describe how to use these powerful features.

Grouping • Managing Data • Visualizations • Styling • Data Pane • Exporting

About the radial menu

Throughout this topic you will see references to a menu called the radial menu. The radial menu is a menu of options for each data column and group, which is accessed by clicking the colored Radial [image] icon on the top left of the column or group.

Using the radial menu

Each radial menu has four options arranged in a circle around the center. When this guide prompts you to select a radial menu option, you will be asked to "use [image] radial>direction". Direction is one of the four cardinal directions corresponding with one of the four options: left, right, up, down.

For example, "use [image] radial>left" means to open the radial menu and select the left option. To do this, you have two options:

  • Click the radial icon, move your cursor left, then click again.
  • Click-and-hold the radial icon, drag your cursor left, then release.

To close the radial menu without selecting an option, click in the center.


Grouping

Groups collect a set of data rows, based on a common value, into a column called a group column. This allows you to identify rows with common factors. You can perform summary calculations on grouped data, such as counting all the rows in each group, or adding up the values for a data field in each group. You can also make charts to visualize the data in each group, and compare groups to each other.

Grouping data in an ExpressView

Making a group

To turn a data column into a group column use [image] radial>up. Doing this will organize the other columns into groups. Each row in the data column is a unique group of the rows in the other columns. The group column has a new color to distinguish it from the data rows. Remember to turn on Live Data to see your actual data and verify that this is the desired grouping.

To ungroup a column, use [image] radial>left. This turns the group column back into a data column.

Groups can be created inside other groups. These are called nested groups. To make a nested group, add another group to an ExpressView which already has one. Additional levels of nesting can be made as needed.

Changing group level

If you have two or more levels of grouping, you may decide that they are nested improperly. For example, if you had Products grouped by Orders, then you add another grouping on Employees, you may end up with Products grouped by Employees grouped by Orders.

One possible data grouping

However, it makes more sense to have Orders grouped by Employees instead, since Employees have multiple Orders, but Orders does not have multiple Employees. To move the Employees group up one level, use [image] radial>up on the group column.

Changing the data grouping

Summarizing group data

Each group has a footer that contains summary calculations, or aggregates, for each column. There is also a report footer, which contains summaries for the entire ExpressView. You can choose one of several calculations for each column.

To change the calculation for a column, click a footer and select one of the following options:

Sum

Totals the data values in the group. Only available for numeric fields.

Min

Shows the smallest data value, or first value alphabetically, or earliest date in the group.

Max

Shows the largest data value, or last value alphabetically, or latest date in the group.

Count

Counts the number of values in the group.

Distinct Count

Counts the number of distinct values in the group.

Avg

Takes the average, or arithmetic mean, of the values in the group. Only available for numeric fields.

None

Show no summary calculation.

Choosing an aggregate

Hiding data rows

If you only want to see the summary calculations, you can hide the data rows, for each group, or for the entire ExpressView. This does not remove the data or alter the summaries; it only hides the rows from view.

Click the header for each group to toggle whether its rows are shown or hidden.

To hide or show all the rows in the ExpressView:

  1. Click the Group Expand/Collapse Options [image] icon.

  2. Click [image] Hide All Group Content to hide all the rows, or [image] Show All Group Content to show all the rows.

If you have nested groups, this hides all but the top level groups.

To remove all data rows from the report and only show summary data, deselect Include Detail Rows. This may improve the performance for reports that do not depend on the detail values.


Managing Data

You can exercise some fine grained control over the actual data that appears in the ExpressView. You can choose how the data appears, you can filter down the rows to appear, and you can change the order of rows in their respective sections.

Formatting

Formatting allows you to specify a data type for specific data fields, and choose how that data displays.

To change the data type for a field:

  1. Click the Formatting and Style [image] icon to open the Formatting page.

  2. Click the data rows to format. They will highlight in blue.

    Selecting data to format

  3. Click the Data Format tab.

  4. From the Format Type list, select a data type from the following options:

    General

    Format the data using the default settings for your environment. The application will assume the data type.

    Number

    Format the data as a number.

    Optional: Choose how the number displays:

    • In the Decimal Places field, enter a number for how many decimal places to display. Then, in the field to the right, enter a symbol to use as the decimal mark.

    • To show a delimiter every three digits, select Use 1000 Separator. Then, in the field to the right, enter a symbol to use as the delimiter.

    • To show a currency symbol before the number, select Use Currency Symbol. Then, in the field to the right, enter the symbol to show.

    • To show a percent sign (%) after the number, select Append Percent Sign.

    • To show no value if the number is 0, select Blank When Zero.

    • To show a minus sign (-) in front of negative numbers, select Show Negative Symbol.

    • To show parentheses ( ) around negative numbers, select Show Parenthesis.

    • To show negative numbers in a different color, select Color, then enter a color code or use the color picker to choose a color.

    Date

    Format the data as a date, time, or date and time.

    Optional: From the Date/Time Format menu, choose which date and time components to display, and how to show them.

    Choosing a date/time format

    Text

    Do not apply any formatting to the data, and show it exactly as it appears in the database.

Tip
If the data field cannot be formatted as a number or date, then selecting one of those options will have no effect on the appearance of the data.

Sorting

Sorting allows you to set the order that the data rows appear in each section. Click the Sorts [image] icon to open the list of sorts. Every data column in the ExpressView that is not a group column is automatically sorted.

You can choose which columns take precedence for sorting. The order of fields on the Sorts page is their order of precedence, from highest to lowest. Their order is also indicated by a number on the right of the column headers: The lower the number, the higher the precedence.

To set the sort precedence of data fields, either:

  • On the Sorts page, drag fields up or down.
  • Use [image] radial>down to give a field the highest precedence.

Dragging a sort to change its precedence

You can choose which direction to sort the data for each data field: ascending or descending. A field's sort direction is indicated by an arrow on the right of the column header: Up [image] for ascending, down [image] for descending.

To change a field's sort direction, either:

  • On the Sorts page, select asc for ascending, or desc for descending.
  • Click the column header to swap to the opposite direction.

For more information on sorting data, see Sorts.

Filtering

Filtering allows you to narrow the scope of your ExpressViews by restricting the amount of data shown. You can filter data by only showing rows whose values satisfy certain conditions. These are Standard filters. You can also filter data to only the rows with the top or bottom values, for either data fields or data summaries, per group or for the entire report. These are Top N filters. For more information, see Filters.

Click the Filters [image] icon to open the list of filters. The filters page has two tabs: Standard and Top/Bottom.

To show only the data that satisfies several conditions:

  1. On the Filters page, click the Standard tab.

  2. To add a data field to filter, either:

    • Use [image] radial>right on a data column or group.

    • Drag a data field from the Data pane to the Meet all of the following conditions pane.

      Tip
      This allows you to filter any accessible data field, not just those on the ExpressView.

  3. Choose a filter operator from the list. See Filters for details.

  4. Enter a filter value or values, or select them from the list.

  5. Repeat steps 2-4 for every filter condition that the data must satisfy in order to show.

  6. If you are viewing live data, click Apply Changes to apply the filters.

To show only the data that satisfies at least one of several conditions:

  1. On the Filters page, click the Standard tab.

  2. To add a data field to filter, either:

    • Use [image] radial>right on a data column or group. Then drag the filter to the meet any of the following conditions pane.

    • Drag a data field from the Data pane to the meet any of the following conditions pane.

      Tip
      This allows you to filter any accessible data field, not just those on the ExpressView.

  3. Choose a filter operator from the list. See Filters for details.

  4. Enter a filter value or values, or select them from the list.

  5. Repeat steps 2-4 for every filter condition, of which the data must satisfy at least one in order to show.

  6. If you are viewing live data, click Apply Changes to apply the filters.

Adding a field as a Standard filter

To show only the top or bottom values, for either data fields or data summaries:

  1. On the Filters page, click the Top/Bottom tab.

  2. Select the Limit the report to the top/bottom values check box.

  3. Select either Top or Bottom, for whether you want to show the top or bottom values.

  4. Enter a number for how many values you want to show.

  5. Choose how you want to limit your data:

    • If you want to show the rows with the top or bottom data values for a field or group, select Values from the list.

    • If you want to show the groups with the top or bottom summary values for a parent group or the ExpressView, select one of the summary calculations, Sum, Avg, Min, Max, Count, or Distinct Count, from the list. See Grouping for details on the calculations.

      Tip
      Only numeric data fields support Sum and Avg calculations.

  6. Select the data field or group field to filter from the Of list.

  7. Optional: To show the top or bottom values for each group:

    1. Click [image] Add Group.

    2. Select a group field from the For Each list.

Adding a Top/Bottom filter


Visualizations

Charts allow you to showcase your data in a visual format. They allow you to quickly scan your data for patterns and trends. Charts can be easily made and customized in ExpressViews, in as little as one click.

An ExpressView visualization

To add a chart to an ExpressView:

  1. Click the Show Visualization [image] icon. A bar chart is added and populated with the data from your ExpressView.

  2. In the Visualizations pane, select the type of chart you want to use. The chart immediately swaps to that type.

Click on a chart series to drill down into the data for that series. This filters the data columns to only the series you select.

You can add many more personal touches, small or large, to the visualizations you create. See the following sections for details on how to customize your visualizations.

Choosing chart data

When a visualization is first added, the application presets the chart fields to try to match the ExpressView data as closely as possible. This may not always match the data that you want the chart to use.

To see the chart data fields, click the Visualizations [image] icon, then click the Data tab. There are two fields: Labels and Values.

Labels

The Labels field is a group column, where each group is a series on the chart. For example, using an Employees group field as the label field represents each employee as a series.

Several charts allow an additional labels field. This is useful if you have a nested group, which represents common series to be measured across several groupings. For example, to compare the number of sales per product per employee, you could add a nested Product field as a second label to the aforementioned employees chart.

To add a chart label, either:

  • Drag a data field onto the chart and release on the Add Label area.

  • In the Visualizations pane, click the Data tab. Either:

    • Drag a data field to the Labels area.

    • Click [image] Add Label, then select a field from the Field list.

Drag a field onto the visualization or onto the Data tab to add it as a label

Values

The Values field is the data values to plot on the chart for each Labels group. For example: The number of sales per employee.

Several charts allow for multiple values fields. This is useful if you have common series to be measured across several groupings, and the values fields are in the same data row. For example, the number of sales and number of lost sales per employee.

To add a chart value, either:

  • Drag a data field onto the chart and release on the Add Value area.

  • In the Visualizations pane, click the Data tab. Either:

    • Drag a data field to the Values area.
    • Click [image] Add Value, then select a field from the Field list.

Drag a field onto the visualization or onto the Data tab to add it as a value


Styling

ExpressViews can be styled in a variety of ways. Click the Formatting and Style [image] icon to see your available options.

Using a premade theme

If there are any available, you can select a premade theme to use for the ExpressView, or to use as a baseline for further customizations. On the Formatting page, click the Theme tab. Then select a theme from the Theme Selector list:

Legacy

This is the default theme.

Custom

This option indicates that you are not currently using a premade theme.

Selecting a premade theme overrides any custom styling you have. Be sure to save the ExpressView first, so that you can retrieve your styling if you accidentally override it.

Choosing a premade theme

Styling data cells

Data columns and group columns can be styled independently, using the Style tab on the Formatting page. You can style the data cells, as well as group headers and footers.

To style a data cell:

  1. With the Formatting page open, click the section, header, or footer you want to style. It will highlight in blue.

    Selecting an element to format

  2. Click the Style tab.

  3. Choose a look for the cell text and background.

You can also use the Formatting page to edit the header names of the data columns. To do so:

  1. With the Formatting page open, click the header which you want to edit. It will highlight in blue.

  2. Click the Edit [image] icon on the header.

  3. Type a name for the header.

    Changing header text

  4. Press Enter.

Changing data row colors

The background colors for the data rows can be customized, and you can set the pattern by which the colors alternate. This affects every data section in the ExpressView.

To set the background colors:

  1. On the Formatting page, click the Row Shading tab. By default there are two colors which alternate every other row.

  2. Choose the number of colors that you want to alternate between:

    • To make all rows the same color, click [image] to delete all the colors except one.

    • To make rows alternate between more than two colors, click [image] to add more colors.

  3. Enter a color code or use the color picker to set each color.

Choosing data row colors

Changing group colors

The headers and footers for group columns are prefixed by a different color depending on the level of grouping. These colors can be customized, and you can set the pattern by which the colors alternate. This affects every group header and footer in the ExpressView.

To set the group level colors:

  1. On the Formatting page, click the Group Colors tab. By default, nested groups alternate between four colors.

  2. Choose the number of colors that you want to alternate between:

    • To make all group levels the same color, click [image] to delete all the colors except one.

    • To make group levels alternate between more than four colors, click [image] to add more colors.

  3. Enter a color code or use the color picker to set each color.

Choosing radial menu colors


Data Pane

The data pane provides a search function, data type filtering, and a list of data categories that can be expanded to show their data fields. To learn about data categories, see Categories. Click the Choose Data [image] icon to expand or collapse the data pane.

Viewing the available data fields

To locate a specific data field, type the name of the field into the Search field and press Enter. To see all the data fields, click [image] to clear the search field.

You can show only data fields of a certain type by clicking one of the type icons under the Search field:

  • text
  • numeric
  • date/time
  • other

All available data categories are usable on an ExpressView. You do not have to worry about adding categories manually or thinking about join structure. Adding a data field is as simple as dragging it onto the Design pane. This turns it into a data column. Use [image] radial>left to remove a data column.


Exporting

ExpressViews can be used as a starting point to quickly add fields to a report, before delving into some of the more Advanced Reporting capabilities. The ExpressView format is not compatible with the Report Designer, but you can export it to an Advanced Report and edit the copy.

To export an ExpressView to an Advanced Report:

  1. Click the Settings [image] icon.
  2. Click the Create Advanced Report tab.
  3. Enter a name for the report and select a folder where it should live.
  4. Click Create Advanced Report.

If you to save an ExpressView as a file, it is exported as PDF, RTF, CSV, or Excel. Each format has some advantages and disadvantages.

  • PDF is closest to the look of the ExpressView, and is suitable for printing and emailing. But if there is a large amount of data, you may have too many pages, or too large a file size.
  • RTF retains the basic look of the ExpressView, and can be opened in a word processor for any additional editing. But it may look different depending on the program it is viewed with, and it is not suitable for viewing large amounts of data.
  • CSV retains only the data, and none of the look of the ExpressView. It is best used if you need to process a large amount of data in an accounting or analysis program.
  • Excel retains the data, visualization, and optionally, some styling. It is a good hybrid format if you have a lot of data, but you still need it in a visually presentable form.

Export settings

You can make some customizations to the appearance that the exported files will take. With the Settings page open, click the Export Settings tab to see the available options:

  • Page Options affect the size which PDF, RTF, and Excel exports will show on a screen and in print.

  • General Options allow you to restrict available export types, and choose a default type.

  • Other Options:

    • Include Setup Info: Choose whether to include some information about the fields, sorts, and filters in the body of the file.

    • Use Group Color Styling: Choose whether to include the group header colorings in the output file.

    • 'No Data Qualified' Mode: If the ExpressView returns no data, choose whether to render an empty file, or show a user message instead.

    • Flatten Groups in Excel and CSV: For Excel and CSV files, choose whether to automatically ungroup, or flatten, all group columns into data columns. This may make the output more suitable for data analysis.

    • Keep Cell Styling in Excel: For Excel files, choose whether to show styling, such as font and row shading, or to show only the bare data.


Report Viewer

When you run an Advanced Report, Express Report, or Crosstab Report, the output is shown in a paged, interactive format called the Report Viewer. The viewer shows you the report populated by all of its data. You can page through the data and search for specific items. You can also make some additional adjustments to the report, such as styling cells or changing column sorts.

The interactive Report Viewer

To run a report in the Report Viewer:

  • If you are in the Report Designer, click Run Report.

    Tip
    The Run Report button also saves the report.

  • If you are in the folder tree, click the Run [image] icon, or click the Menu [image] icon and select [image] Run Report.

The Report Viewer opens in a new tab, indicated by the viewer [image] icon. This tab represents an instance of the report output as you have just run it. If you go back and edit the report, you need to run it again to see the changes. This will open another viewer tab, and you can go back and close the old one.

Page through the report by using the navigation icons [image] [image] [image] [image]. Search through the report output by typing into the Find field.

If you want to save the output, you can export from the Report Viewer to a PDF, RTF, CSV, or Excel file.

Truncated results displayed

This icon and message indicates that the report has only returned a partial data set. There are fewer detail rows than the full set, and aggregate formulas and visualizations apply only to the data that was returned to the report.

To get more data, click the icon and select either:

  • Generate +number to get the next number of data rows and add them to the existing report.
  • Generate All to get the full data set.

Interacting with reports

There are a number of ways to interact with reports in the Report Viewer. Your available options depend on how the report was designed and the type of content in it.

Tip
If your environment has Action Events, you may have access to more interactive features than are listed in this section.

Drilling into data

Some reports allow you to drill down into cells or charts to see some additional data related to the field you selected. If a cell or chart has a drilldown, you can click on one of the values to drill down into the related data.

Drilldowns are actually other reports that you can interact with or even export to a file. Drilldowns may even have drilldowns of their own!

Drilling down into a data cell and a chart

Expanding hidden data

Some reports have hidden data that can be seen by expanding certain rows. Expandable rows are indicated by an arrow icon [image]. Click on the icon to expand the row. Click the icon again to collapse it. Right-click the icon to open a menu to do the following:

Expand / Collapse

Expand or collapse the outer group

Expand / Collapse Group

Expand or collapse the group and any inner groups

Expand / Collapse All

Expand or collapse all outer and inner groups on the report

Expanding a data row

Chart features

Charts have a number of interactive features depending on the type of chart. For example, line charts and bar charts allow you to hide series, multi-axis charts allow you to turn axes on or off, and pie charts allow you to rotate the chart and pull out slices. Charts can also show some additional information if you hover over a point or series.

Toggling a chart series on or off

Drilling into maps

On a Google Map, use the mouse wheel or click the Zoom in [image] and Zoom out [image] icons to change your view level. Drag the map to move the view in that direction. Double-click on a highlighted region to drill down into the location data.

On a geochart, click on a highlighted region to drill down into the region. Click [Back] to return to the previous view.

Customizing report output

In the Report Viewer you can make changes to the style, formatting, and data of the report. These changes can be saved to the original report, to a new copy of the report, or as a user report, by clicking the Save changes [image] icon.

Caution
If you make any changes, you must save them before closing the Report Viewer or you will lose them.

What is a user report?

A user report is a customized version of a report that is specific to you. User report changes are saved separately from the report design, and will only show when you run the report yourself. These can be useful if you want to make changes to a locked [image] report.

Note about cookies: User reports may be stored as browser cookies. Ask your administrator whether this is the case. If so, then user report changes will only apply to your current computer and web browser. Clearing your browser cookies will remove any user report customizations that you have made.

Styling data cells

Right-click a cell to open the style menu. Using this menu you can set custom styling for the following: Font, Size, Color, Background color, Text decoration, Alignment, Word wrap. Styling applies to all cells of the same type in the group; affected cells are outlined by a yellow band.

Right-clicking a cell to change its styling

For group cells with dynamic data from fields or formulas, use the Conditional Filter list to apply styling to only the cells that meet a certain condition. For each of the following options, styling applies to the cells of the same type in the group that meet the specified condition:

Apply to all

All cells

Apply to equal values

All cells with the same value as the selected cell

Apply to unequal values

All cells with different values than the selected cell

Apply to greater values

All cells with values greater than the selected cell (larger number, later date, or alphabetically subsequent)

Apply to greater than or equal values

All cells with values greater than or equal to the selected cell

Apply to lesser values

All cells with values less than the selected cell (smaller number, earlier date, or alphabetically preceding)

Apply to less than or equal values

All cells with values less than or equal to the selected cell

Tip
Select a condition before selecting the styling.

Styling charts

Right-click a chart to open the chart menu. Using this menu you can swap to a different Type of chart, change the color Theme, change the location of the Legend, and change the Sort order of the data series.

Right-clicking a chart to change its type

Managing data columns

Each column on the report has a light gray control box at the top of the page. Change the width of the columns by dragging the control left or right. Sort by a column by clicking the control. To hide a column, right-click the control and select [image] Hide Column from the list.

Resizing a column and right-clicking a column to hide it

Setting interactive sorts and filters

Reports can have some additional filters and sorts saved on top of the base report. Click the splitter [image] icon to open the sidebar to manage interactive sorts and filters.

Filterable fields can be narrowed down to select values or to a range of values. If a data field is not available to filter, click the Add Item [image] icon to add a filter for that field. To remove a filter, click the Delete Item [image] icon.

The order of sorted fields can be swapped between ascending [image] and descending [image].

You can also add conditional filters to hide rows where the cells do not meet a certain condition. To do so, right-click a cell to open the style menu, select a condition from the list, then click the Filter Values [image] icon. Conditional filters are shown in the sidebar.

Adding a conditional filter


Report Wizard

The New Report Wizard opens at the start of each new Express Report, Advanced Report, or Crosstab Report. The wizard lets you add and configure data, and lets you quickly set a layout for your report before you dive into the full designer.

The Express Report Wizard Layout page

What are Express Reports?

This report type is made entirely using the Report Wizard. If you do not need the additional capabilities of the advanced Report Designer, Express Reports are an easy way to design tabular reports. To open an Express Report in the Advanced Designer, you need to convert it to an Advanced Report.

Using the Report Wizard

The Report Wizard has several pages, which you should progress through from left to right. The following topics detail how to use each page in the wizard.

Name • Categories • Sorts • Filters • Layout • Options

Tip
Only Express Reports have an Options page. Crosstab Reports do not have a Sorts page. The Layout page differs slightly for each report type. The Name and Categories pages are mandatory. If a page is not available, your administrator may have disabled it.


Name

Enter a name for the report and select a folder where it should live. Choose a unique, descriptive name that is easy to remember. If you are in a shared environment, you may want to put your name in the name of the report.

Caution
You cannot save a report in a locked [image] folder. A report name cannot contain the following characters:
\ / : * ? " < > |

This page must be completed before moving on, but you can always go back later and rename or relocate the report.

Description

This is an optional field, which allows you to give some additional information about the report.

Filter description

You may see an additional field for entering a description for the report filters. If you intend to add user prompting filters to this report, then you can enter some information in this field that a user will be able to see when entering filter values.

Overview

Categories


Categories

Select which data to use on the report. The left pane shows the data categories you can access. To see the fields in a category, select it, then click the View Category Fields [image] icon.

What are data categories?

Data categories are tables of data, which are organized by rows and columns. Columns are also known as data fields. A row of data has entries for one or more columns in the category. When you add a data field onto a report you are seeing the information in one column of data for every row in the category.

For example, a data category for Employees could have columns for the first and last names of each employee, an identification number, and a home phone number. Each row represents a person, and each column contains a specific type of information such as Last Name or Phone Number.

Example of a data category for employee records

You add entire categories at a time to a report, but in the report view you select only the columns you want to see. When you add a data field to the report design, even though you only see one column, the rest of the table is still present behind the scenes. You will never lose the connections between items in each row, and you can always add more fields.

Adding categories

On the Categories page, add data categories to the report. Later on, you can select which fields you actually want to see in the report layout.

Dragging a category to the Category Name pane

Tip
As you add categories, unrelated categories will become unavailable. For more information on how categories are related to each other, see Joins.

Name

Sorts


Sorts

Sorting is the process of ordering your data rows by a certain sequence. For each available data category, you can choose which data field should be used to sort the rows. Fields can be sorted in ascending or descending direction. The way in which rows are sorted depends on the type of value in the field:

Numeric

Asc [image] Lower values Higher values [image] Desc

Date

Asc [image] Past Future [image] Desc

Text

Asc [image] A Z [image] Desc

Sorting Employees by the Department field, ascending

A report can have multiple sorts. This can be useful when you want your highest precedence sort to affect a data field where the values for multiple rows may be the same.

For example, imagine a large company with many employees. There could be multiple people with the last name Buchanan. With only a sort on LastName, you do not know how all the people with the last name Buchanan will be ordered amongst themselves. If this matters, then you can add a second sort on, say, the FirstName field, so that people with the same last names will be ordered by their first names.

Adding sorts

On the Sorts page, add data fields to sort. The precedence of the sorts starts with the highest row and moves down the list. Drag the rows up or down to change the precedence.

Dragging a field to the Sort By pane

Tip
Sorts are not applicable for Crosstab Reports.

Categories

Filters


Filters

Sometimes you may only want to see a portion of rows in a data category, rather than its entirety. Filters allow you to narrow the scope of your reports by restricting the amount of data by specified criteria.

For example, imagine a category containing a row for every single sale your company has ever made. You can use a filter to limit the report to only sales from the past month. This also has the benefit of speeding up your reports.

Filters in the Report Wizard, called Standard filters, limit data by only showing rows where the values for a field meet a certain condition.

Adding filters

On the Filters page, add data fields to filter.

Dragging a field to the Filter By pane

For each data field, select a condition. When the report is run, the field value for each row is checked against the condition for that field. Only the rows where the field satisfies the condition will show on the report.

To create a filter condition:

  1. Select an operator from one of the following options. This is the condition used to match the data values to your specified filter value or values. Some operators are only available for some data types.

    The following table describes the filter operators and their applicable data types:

    Condition

    Type

    Filter Value(s)

    Matching Value(s)

    Not Matching Value(s)

    Equal To (=)

    Any

    July 4, 2016

    July 4, 2016

    July 4, 2016 12:00 AM

    July 4

    July 4, 2016 2:00 PM

    Less Than (<)

    Number, Date/Time

    July 4, 2016

    July 3, 2016

    July 5, 2016

    Greater Than (>)

    Number, Date/Time

    July 4, 2016

    July 5, 2016

    July 4, 2016 5:00 PM

    July 3, 2016

    Starts With

    Text, Number

    203

    2035550224

    8458081120

    Ends With

    Text, Number

    224

    2035550224

    7188044606

    Contains

    Text, Number

    555

    2035550224

    2038081120

    Between

    Any

    1, 4

    1, 1.5, 3, 4

    0.999, 5

    One Of

    Any

    1, 2, 3, 4

    1, 2, 3, 4

    1.1, 9

  2. Enter a filter value or values, or select them from the list of existing values.

    Type into the filter field to search for data values to filter. Click the settings icon [image] to select whether to search for values that either Start With or Contain the typed text.

    Choosing a filter value

  3. Optional: If you have multiple filters, you can choose how they should be grouped. See Grouping filters together.

  4. Optional: If you want the report to prompt the user to enter a filter value when the report is run, select Prompt For Value.

Sorts

Layout


Layout

Use the Layout page to create the data and look of the report. This page is different depending on the type of report you are making:

  • Advanced Report: Add data fields, formulas, groups, headers, footers, and summary calculations.
  • Express Report: Add data fields, formulas, groups, headers, footers, and summary calculations. Customize the style of the report or select a premade theme.
  • Crosstab Report: Add data fields and formulas as row headers, column headers, or tabulation data. Select a premade theme for the report.

Adding data (Advanced and Express Reports)

Add data fields to display in the output of the report.

Dragging a field to the Data Field pane

Each data field is a column of associated data values; each value belongs to a row in the category. In the Preview pane, each data field has a header and some placeholder values indicating how the values from the field will look.

To show a formula instead of the data field, click the formula [image] icon. Then use the formula editor to make a data formula. See Formulas for more information.

Grouping data

If you have Sorts then you can group your data fields by one or more sort fields. To add a group:

  1. For each data field, select a summary function for how the total for each group is calculated. See Grouping for details on each function.

    Choosing a summary function

  2. In the Summarize By pane, select the check box for every Sort category to group by. By default, this groups on the unique key of the category.

  3. Optional: To customize which data field the group category uses:

    1. In the Summarize By pane, click the name of the Sort category.

    2. Select the data field from the Summarize by each unique list.

  4. Optional: In the Summarize By pane, click the name of the Sort category to customize how the group looks:

    • Select the Add space before each unique item check box to add a blank row before each group.
    • Select the Include Header at the beginning check box to put a group field value before each group. Choose which field to use, or click the formula [image] icon to make a formula for the text.
    • Select the Include Total at the end check box to include the data summary values, selected in step 1, at the end of every group.

Customizing the group design

Page header and footer

A page header is inserted by default with the name of the report. To remove the header, clear the Page Header check box. To edit the header, click Page Header. The following options are available:

  • To include the report title at the top of every page, select the Include title at the top of every page check box.

    If you have an image in the header, choose whether the title is in a column to the Left or Right of the image, from the Position list. Choose how many columns the title spans.

  • To include an image at the top of every page, select the Include image at the top of every page check box, then click Change Image to upload an image from your computer.

    If you have a title in the header, choose whether the image is in a column to the Left or Right of the title, from the Position list. Choose how many columns the image spans.

To add a footer to every page, select the Page Footer check box. To edit the footer, click Page Footer. The following options are available:

  • To include the page number at the bottom of every page, select the Include page number at the bottom of every page check box.

    If you have an image in the footer, choose whether the page number is in a column to the Left or Right of the image, from the Position list. Choose how many columns the page number spans.

  • To include an image at the bottom of every page, select the Include image at the bottom of every page check box, then click Change Image to upload an image from your computer.

    If you have a page number in the footer, choose whether the image is in a column to the Left or Right of the page number, from the Position list. Choose how many columns the image spans.

To include a summarization of all the data fields at the end of the report, select the Grand Total check box.

Customizing the page header

Styling the report (Express Reports)

You can do all your styling for Express Reports in the Report Wizard. To style a cell, click the cell and then choose from the available options:

  • Layout Options: Hide the data rows for the selected field by selecting the Suppress Detail Rows check box. Set the pattern by which the background colors for the data rows colors alternate. See Section Shading for details.
  • Font: Select a font, size, and decoration for the cell text.
  • Color: Select a color for the text and for the background of the cell.
  • Formatting: Select any additional formatting for the data. See Cell Format.
  • Alignment: Select how the cell contents should align to the borders of the cell.

To change the text in a header or footer, double-click the cell and edit the text. To insert a blank column between two data fields, click [image] Add Blank, then drag the column between the two fields. To resize columns, drag the left or right border of the column.

Making changes to an Express Report's design

Premade themes

If you want to use a premade theme, or use one as a starting point for your customization, select a theme from the Theme list.

Caution
Selecting a premade theme will override your current cell styling. If you want to undo a theme selection, click the Undo [image] icon, or press Ctrl-Z.

What are Crosstabs?

Crosstabs are an easy way to do calculations when you have one or more groups and you want to make calculations on subdivisions of each group. Crosstabs extend horizontally for each subdivision so that you can fit many calculations in a small space.

For instructions on making crosstabs, see Crosstabs.

The Crosstab Report Wizard puts a crosstab "widget" in a new Advanced Report. When you finish, the report opens in the Report Designer. To edit the crosstab in the designer, double-click the crosstab.

Filters

Options


Options

The Options page is available for Express Reports. For information about the available options, see General Options.

Converting to an Advanced Report

Express Reports can be used as a starting point to quickly design a report, before delving into some of the more Advanced Reporting capabilities. To edit an Express Report in the Report Designer you need to convert it to an Advanced Report. The data and design of the report are preserved and it gains some additional capabilities, but it can no longer be edited in the Report Wizard.

To convert an Express Report to an Advanced Report:

  1. In the Options page, click the Advanced tab.
  2. Click Convert Express Report to an Advanced Report.

Layout


Report Designer

The Report Designer is the main editor for Advanced Reports. At your fingertips is a full suite of reporting and design tools with an interface that is familiar and easy to understand.

The Advanced Report Designer

Cells

The Report Designer interface is based on cells, like a spreadsheet. Cells can contain text, images, charts, widgets, and formulas. More importantly, cells can contain multiple rows of data. A cell in a Detail section will expand into as many rows as are needed to show all the data. A cell in a Group section will repeat for every group. And a cell in a Page section will repeat for every page on the report.

A Detail cell repeats for every row in the "Employees" data category

All of this is dynamic, meaning that the report will adapt its look based on the data that is returned. For more information on the different types of report cells, see Sections.

Toolbar

You can add a variety of content and styling to your report. Most of the features can be accessed by clicking the relevant icon in the toolbar. Click a link below to read more about a feature.

  • Report Options
    • Rename
    • Description
    • Categories
    • Sorts
    • Filters
    • Options
      • General
      • Report Viewer
      • Execution Caching
    • Template
    • Advanced
      • Joins
      • Events
  • Save Report
  • Create a New Report
  • Undo
  • Redo
  • Format Cells
  • Format Paintbrush
  • Font Style
  • Bold
  • Italic
  • Underline
  • Foreground Color
  • Background Color
  • Font Size
  • Vertical alignment
  • Horizontal alignment
  • Merge Cells
  • Split Cells
  • Wrap Text
  • AutoSum
  • Insert Image
  • Formula Editor
  • Chart Wizard
  • Gauge Wizard
  • Suppress Duplicates
  • Crosstab Wizard
  • Link Reports
  • Linked Action Event
  • Geochart Wizard
  • Google Map Wizard

Tip
Not every listed feature may be available.


Rename

To rename or relocate a report, from the [image] Report Options menu, click [image] Rename. Enter a new name for the report, or choose a different folder. The name must be unique.

Caution
You cannot save a report in a locked [image] folder. A report name cannot contain the following characters:
\ / : * ? " < > |


Description

The description is an optional field that allows you to give some additional information about a report. Descriptions show at the bottom of the folder tree when a report is selected. When searching for reports, you can choose to search through the report descriptions.

The description of a report

To add or edit a description, from the [image] Report Options menu, click [image] Description, then enter a description for the report.

Filter description

You may see an additional field for entering a description for the report filters. If you intend to add user prompting filters, then you can enter some information in this field that users will be able to see when entering filter values.


Categories

Use this window to select which data to use on the report. The left pane shows the data categories you can access. To see the fields in a category, select it, then click the View Category Fields [image] icon.

What are data categories?

Data categories are tables of data, which are organized by rows and columns. Columns are also known as data fields. A row of data has entries for one or more columns in the category. When you add a data field onto a report you are seeing the information in one column of data for every row in the category.

For example, a data category for Employees could have columns for the first and last names of each employee, an identification number, and a home phone number. Each row represents a person, and each column contains a specific type of information such as Last Name or Phone Number.

Example of a data category for employee records

You add entire categories at a time to a report, but in the report view you select only the columns you want to see. When you add a data field to the report design, even though you only see one column, the rest of the table is still present behind the scenes. You will never lose the connections between items in each row, and you can always add more fields.

Dragging a category to the Category Name pane

Relationships between categories (advanced users)

In the data source, data categories are joined to other categories by associating uniquely identifying data fields from one category to matching data fields in another. This means that if a row's identifying field matches one or more rows in a joined category, then those rows connect to an entire row or group of rows, which have their own separate data fields.

Only joined data categories, which are described as having a relation, can be added to the same report. This is why some categories may become unavailable as you add others. But data categories, even if they are not related to each other, may both be related to another category. If you add that category, then you can add both those categories, because there is now a join path between them.

For more information on how categories are related to each other, see Joins.

Suppressing duplicates (advanced users)

Be judicious when adding data categories. If you find that your report has unexpected duplicate values or empty rows, the cause is most likely that you have a one-to-many join to a category that you are not using.

For example, this report has Employees and Orders categories. There is a one-to-many join from Employees to Orders, indicating that each Employee row is joined to one or more Order rows. Even though we are not using Orders on the report design, there are duplicate Employees because our join setup causes us to have a row for each Order, instead of each Employee.

Unexpected duplicate Employee values

There are several ways to eliminate these duplicates. You can suppress duplicates for the Employees category, which will show blank rows for consecutive duplicates. In the Categories window, select the Suppress Duplicates check box for the Employees category.

Duplicate-suppressed category

You can also suppress duplicates for the cell, which will hide unnecessary duplicate rows. Select the cell and click the [image] Suppress Duplicates icon. If a field from Orders is on the report, the behavior will be the same as suppressing duplicates for the category.

Duplicate-suppressed cell

Or, if you do not think you will need the Orders category, remove it from the report. In the Categories window, click the [image] Delete Category icon next to Orders to remove the category. You can always add it again later if needed.


Sorts

Sorting is the process of ordering your data rows by a certain sequence. For each available data category, you can choose which data field should be used to sort the rows. Fields can be sorted in ascending or descending direction. The way in which rows are sorted depends on the type of value in the field:

Numeric

Asc [image] Lower values Higher values [image] Desc

Date

Asc [image] Past Future [image] Desc

Text

Asc [image] A Z [image] Desc

Sorting a category by Department

A report can have multiple sorts. This can be useful when you want your highest precedence sort to affect a data field where the values for multiple rows may be the same.

For example, imagine a large company with many employees. There could be multiple people with the last name Buchanan. With only a sort on LastName, you do not know how all the people with the last name Buchanan will be ordered amongst themselves. If this matters, then you can add a second sort on, say, the FirstName field, so that people with the same last names will be ordered by their first names.

Adding sorts

In the Sorts window, add data fields to sort. The precedence of the sorts starts with the highest row and moves down the list. Drag the rows up or down to change the precedence.

Dragging a field to the Sort By pane

Relationship between sorts and groups

Sorts are a prerequisite for making groups. Here's why.

Sorting puts data in order so that data rows which share common values for the sort field are next to each other. This is essentially what grouping does as well. Grouping simply takes those common values, pulls them out of the rows, and makes sections for each group of rows which share that value. Sorts tell the report how you want to your data to be grouped.

Grouping a category by the Department sort

Tip
Set the sort precedence so that nested groups are in order of their grouping level. The outermost group should have the highest precedence, with the next levels following in order. If the precedence is set incorrectly, it could result in inconsistent data groups.

Sort formulas (advanced users)

You can sort by a formula instead of a data field. This allows you to have finer and more specific control over your groups.

If you do not have a single data field as a unique key, you can use a sort formula to sort on a concatenation of two fields instead. For example, EmployeeId plus TerritoryId fields:

={EmployeeTerritories.EmployeeId} & {EmployeeTerritories.TerritoryId}

Or if your sort field would generate too many groups, you can sort on a piece of the field instead. For example, you could group on only the month and year component of a date field.

=Date(Year({Employees.HireDate}),Month({Employees.HireDate}),1)

To add a sort formula, click [image] Add Formula, then use the formula editor to make a composite field to sort on.

Sorting by aggregates (advanced users)

You may want to sort groups by the summary, or aggregate, calculation of each group. You can do this using Top/Bottom filters.

Top/Bottom filters look for the highest or lowest values in a set, and then put those values in order. You can supply any arbitrary cell in the report in the Top/Bottom filter. So if you set the number of displayed values to "infinity", then no values are excluded, but the ordering still takes effect.

To sort by a summary calculation:

  1. Ensure that your report has the appropriate aggregate formula in a group footer cell. The cell should return a numeric value in the report output, which you want to sort the groups by.

  2. From the [image] Report Options menu, click [image] Filters. Then click the Top/Bottom tab.

  3. Select the Limit the report to the top or bottom values of a defined dataset check box.

  4. From the Top/Bottom list, select Bottom to sort in ascending order, or Top to sort in descending order.

  5. In the # field, type 2147483647.

    Why this number? We cannot enter "infinity", so instead we want to enter an arbitrarily large number like 99999. This is the largest number that can fit without causing a report error.

  6. From the Value list, select the group footer cell with the aggregate formula.

  7. If there is a For Each group, click [image] to remove it.

Keep in mind that this is not a Sort from the sort menu. You cannot use this to make nested groups. This only affects the order in which a group of data is shown in the output. Because this is technically a Filter, this has precedence over the report sorts.


Filters

Sometimes you may only want to see a portion of rows in a data category, rather than its entirety. Filters allow you to narrow the scope of your reports by restricting the amount of data by specified criteria.

For example, imagine a category containing a row for every single sale your company has ever made. You can use a filter to limit the report to only sales from the past month. This also has the benefit of speeding up your reports.

There are three types of filters available in the Filters window:

Standard • Group Min/Max • Top/Bottom

Standard

Standard filters limit data by only showing rows where the values for a field meet a certain condition.

Adding filters

In the Filters window, add data fields to filter.

Dragging a field to the Filter By pane

For each data field, select a condition. When the report is run, the field value for each row is checked against the condition for that field. Only the rows where the field satisfies the condition will show on the report.

To create a filter condition:

  1. Select an operator from one of the following options. This is the condition used to match the data values to your specified filter value or values. Some operators are only available for some data types.

    The following table describes the filter operators and their applicable data types:

    Condition

    Type

    Filter Value(s)

    Matching Value(s)

    Not Matching Value(s)

    Equal To (=)

    Any

    July 4, 2016

    July 4, 2016

    July 4, 2016 12:00 AM

    July 4

    July 4, 2016 2:00 PM

    Less Than (<)

    Number, Date/Time

    July 4, 2016

    July 3, 2016

    July 5, 2016

    Greater Than (>)

    Number, Date/Time

    July 4, 2016

    July 5, 2016

    July 4, 2016 5:00 PM

    July 3, 2016

    Starts With

    Text, Number

    203

    2035550224

    8458081120

    Ends With

    Text, Number

    224

    2035550224

    7188044606

    Contains

    Text, Number

    555

    2035550224

    2038081120

    Between

    Any

    1, 4

    1, 1.5, 3, 4

    0.999, 5

    One Of

    Any

    1, 2, 3, 4

    1, 2, 3, 4

    1.1, 9

  2. Enter a filter value or values, or select them from the list of existing values.

    Type into the filter field to search for data values to filter. Click the settings icon [image] to select whether to search for values that either Start With or Contain the typed text.

    Choosing a filter value

  3. Optional: If you have multiple filters, you can choose how they should be grouped. See Grouping filters together.

  4. Optional: If you want the report to prompt the user to enter a filter value when the report is run, select Prompt For Value. See below for details.

Prompting for values

If you want to let users select their own filter values when they run the report, you can set filters to Prompt For Value. In the Report Options, you can choose whether to let users change the operators and delete filters.

You do not need to enter a value for prompting filters. But if you do, it is entered as the default value for the filter in the prompt dialog.

Grouping filters together

When a data row is checked against the report filters, the values for each of its data fields are checked against all of the filters for the data fields. By default, it must satisfy every filter condition to show on the report. The combined statement used to filter the data rows is shown in the Summary field.

Example of a combined filter statement

You can specify that a data row needs only to satisfy one filter, or a selection of filters, to show on the report.

To specify that a data row needs to satisfy any one of several filters:

  1. On the Filters page, drag the filter rows next to each other.

  2. Select the first filter.

  3. Select OR With Next Filter.

    You should notice that in the Summary field, the And between this filter and the next has changed to Or.

  4. Repeat steps 2-3 for every filter except the last.

You can group filters together, to specify that a data row can match either one group of filters, or another group of filters, to show on the report.

To specify that a data row needs to satisfy any one of several groups of filters:

  1. On the Filters page, drag the filter rows such that the filters are nearest to their group mates.

  2. Select the first filter in the first group.

  3. Select Group With Next Filter.

    You should notice that in the Summary field, there are now parentheses around this filter and the next.

  4. Repeat steps 2-3 for every filter except the last in the first group.

  5. Select the last filter in the first group.

  6. Select OR With Next Filter.

    You should notice that in the Summary field, the And between this filter and the next has changed to Or.

  7. Repeat steps 2-6 for every group of filters. Skip step 6 for the last filter in the last group.

Nesting filter groups (advanced users)

Filter groups can be nested arbitrarily. This allows for more detailed control over grouping. The Summary field shows the actual statement used to filter the data. You can make arbitrary groupings by inserting parentheses manually at locations in the statement. Use the following keyboard shortcuts:

  • Ctrl + [ to add an opening parenthesis before the selected filter
  • Ctrl + ] to add a closing parenthesis after the selected filter
  • Ctrl + Shift + [ to remove an opening parenthesis from before the selected filter
  • Ctrl + Shift + ] to remove a closing parenthesis from after the selected filter

Caution
Make sure to have a closing parenthesis for every opening parenthesis.

Group Min/Max

Group Min/Max filters limit groups to a row with only the highest or lowest value in a field. This is useful if you are only interested in viewing the highest or lowest values, such as the most recent hire or highest revenue figure, in a given set.

Entire Data Set

The most basic usage of a Group Min/Max filter is to limit the whole report to just the min or max value for a field.

For example, if you wanted to show only the most recently hired employee then you would only need to add a filter for the "Maximum Employees.HireDate for each Entire Data Set", that is, the entire report.

Showing only the row with the max hire date for the report

Tip
To see more than one maximum or minimum for a data set, use a Top/Bottom filter instead.

Min/Max for each Group

Another common usage for Group Min/Max filters is to see the min or max value for each instance of a group. For example, say you wanted to see the most recent order date for each employee.

First, make sure to add a sort on Employees, since we need to group our Orders by each Employee. Since sorts are implicit groups, adding a Group section is optional. (See Relationship between sorts and groups for an explanation).

Then add a filter for the "Maximum Orders.OrderDate for each Employees".

Showing only the row with the max order date for each employee

Min/Max with Multiple Groups

If the Employees group was within an outer group, say "Customers", then each Employee would repeat for each applicable Customer. The previous filter would cause the report to show the latest Order Date for each Employee for each Customer.

If you only wanted to see the latest Order Date for each Employee, ignoring the Customers group, select the Ignore other groupings on report check box. This results in a report showing only the Customers relevant to each max Order Date per Employee.

Max per Employee per Customer versus Max per Employee (ignoring Customer)

Nested groups also allows the opportunity to provide a second Min/Max filter. For example, if you wanted to see every Customer, but only see the Employee with the highest seniority for each Customer, add another filter for the "Minimum Employees.HireDate for each Customers". Ensure that this filter is lower in precedence than the other, since the Order Date filter should apply first, and then the Hire Date filter.

Senior-most Employee, of each max per Employee per Customer group

Top/Bottom

Top/Bottom filters, also known as Top N filters, limit the report to a number of the highest or lowest values in a field or group.

To use a Top/Bottom filter, the field or formula to filter must be in a cell in a Detail or Group section. Filtering on a Detail cell will filter out data rows, while filtering on a Group cell will filter out entire groups.

  1. In the Filters window, click the Top/Bottom tab.

  2. Select the Limit the report to the top or bottom values of a defined dataset check box.

  3. From the Top/Bottom list, select whether you want to show the top or bottom values.

  4. In the # field, enter a number for the maximum number of values to show.

  5. From the Value list, select a cell to filter by.

    • Choose a Detail cell to show only rows with the top or bottom values in a field.

    • Choose a Group cell to show only groups with the top or bottom values in the report.

  6. Optional: To show the top or bottom values for each of a group:

    1. Click [image] Add Group.

    2. Select a group field from the For Each list.

Tip
Top/Bottom filters work after all other filters have been applied. If a Standard or Group Min/Max filter has narrowed down a data field, a Top/Bottom filter will work on the remaining values.


General Options

The following options are available in the General page:

General Options

  • Allow Execution in Viewer: Allow or prevent the report from being run in the Report Viewer.

  • Include Setup Info: Select Top or Bottom to display the data categories, sorts, and filters at either the beginning or end of the report.

  • Allowed Export Types: Enable or disable output types for the report.

  • Default Export Type: Specify the default output type for the report.

  • Report Tree Shortcut: Specify whether the Run icon for the report in the report tree runs the report in the Report Viewer or exports it in the default output type.

  • Filter Execution Window: Select which type of Filter menu displays when running the report with prompting filters:

    • Default – Display the default type of filter execution window.

    • Standard – Display the standard filter execution window.

    • Simple with Operator – Display a simplified filter execution window that only allows the operator and value to be changed.

    • Simple without Operator – Display a simplified filter window that only allows the filter value to be changed.

  • Always Show Filters in Report Viewer: Show the filter menu and allow changes to be made every time the report is run.

  • No Data Qualify Display Mode: Select what to display if no data qualifies for the report.

    • Show Message – Display the standard no data qualified message.

    • Show Report – Display the Page Header, Page Footer, Report Header, and Report Footer sections of the report. Any cells containing Data Fields will not be displayed.

Excel Options

  • Suppress Formatting: Export only the report data to Excel, without the formatting.

  • Show Grid: Show grid lines between cells in Excel output.

  • Freeze Rows: Freeze the top number of rows when exporting to Excel, so that the rows stay anchored to the screen when scrolling through the report.

  • Freeze Columns: Freeze the left number of columns when exporting to Excel, so that the columns stay anchored to the screen when scrolling through the report.

Page Options

  • Page Size: Select the page size of the report output.

  • Page Orientation: Specify whether the report runs in Portrait or Landscape orientation.

  • Fit to Page Width: Fit the width of the report to the width of the page.


Report Viewer Options

The following options are available in the Report Viewer page:

General

  • Show Grid: Show or hide grid lines.

  • Simulate PDF: Have the report appear as if it was on a page.

  • Allow Hide/Show Columns in Report Viewer: Enable or disable the ability to hide columns.

  • Show Toolbar in Report Viewer: Select whether to show or hide the interactive toolbar in the Report Viewer.

Filters

To allow users to select interactive filters in the Report Viewer, add data fields as filters. For each filter you can select the following options:

  • Title: Enter text to appear in place of the data field name.

  • Type: Specify the type of ability the user has to choose filter values:

    • Single Choice - A drop down menu with all possible filter values. Users can choose one value.

    • Multiple Choice - A check list with all possible filter values. Users can choose multiple values.

    • Single Slider - Users can choose one value by sliding a point along a scale.

    • Range Slider - Users can choose multiple values between two points on a scale.

  • Value Sort Direction: Whether the filter values should display in ascending or descending order.

  • Filter Value Format: Select how the filter values should display.

  • Initially Display Filter on Panel: Display the filter choice automatically when the report is run.

Sorts

Display Sorts in Report Viewer: Select whether the user can see and modify sorts in the Report Viewer.

In the Title column, you can enter text to appear in place of the data field names.


Execution Caching

Execution caching allows you to save the data from a report execution, so that reports can be viewed more quickly and with less stress to the database. The frequency and schedule of database updates is customizable for each report. Updates can be scheduled for off-hours in order to better balance system load. Caching can significantly reduce database calls for reports which are accessed frequently. Large or data-intensive reports, which can take a long time to run, especially benefit from caching.

To set up caching for an Advanced Report:

  1. From the [image] Report Options menu, click [image] Options > [image] Execution Caching.

  2. Select the Enable execution caching check box.

  3. Choose which users will be able to see the cached data:

    • User - Only you can see the cached data. When another user runs the report, it will run against a database instead of the cache.

    • Company - All users in your company can see the cached data. When a user from outside the company runs the report, it will run against a database instead of the cache.

    • Global - All users can see the cached data.

  4. Choose how often the cache is updated:

    1. Enter a time of day in the Schedule Time field, or use the picker to select a time.

    2. Optional: To update the cache multiple times per day, select the Repeat Every check box and enter a time for how often it should repeat.

      Optional: To repeat until a certain time of day, enter that time in the until field, or use the picker to select a time.

  5. Choose which days of the week the cache is updated. Select either:

    • To repeat every day or every number of days, click Every __ day(s) and enter a number of days for how often it should repeat.

    • To repeat every Mon, Tues, Wed, Thurs, and Fri, click Every weekday.

  6. Click OK.

When execution caching is enabled for a report, the cache is not created immediately. To run the report, you can manually create the cache by clicking the refresh [image] icon, or you can wait until the first scheduled update completes. The cache can always be updated manually by clicking this icon.

Cache schedules are visible in the Schedule Manager, with the label (cache). Cache schedules can be edited in the Report Designer or the Schedule Manager.

A cache schedule visible in the Schedule Manager

Editing cached reports

Editing a cached report invalidates its cache. It cannot be run until the cache is updated. A missing or invalid cache is indicated by the message Cache is invalid in the report description and the report menu.

Indicators of a valid or invalid cache

It is recommended that you fully design a report first, so that you can manually verify the output before enabling the cache.


Template

You can use reports to dynamically fill out fields in templates and forms.

Template report with repeating data fills a set of form templates

To use a report to fill out a template:

  1. Enter field data in cells on the report. For repeating data:

    • Repeating cells mapped to static template fields make a new instance of the template for each repeat.

    • Repeating cells mapped to limited-repeating template fields make a new instance of the template each time the number of values exceeds the limit. This is often undesirable - ensure that there are appropriate constraints on your data and enough fields to fit it all.

    • Repeating cells mapped to unlimited-repeating template fields never make a new instance of the template. Overflowing data is cut off.

  2. From the [image] Report Options > [image] Template window, select an existing template or click the Upload Template [image] icon and add a new one.

  3. For each template field, select either:

    • A report cell containing text or data. Images, visualizations, and other widgets are not supported.

    • Leave as is - Any bookmark text shows as-is in the output

    • Nothing - The field is blank

  4. Click OK.

To remove a template from a report:

  1. From the Template window, select the blank option from the template list.

  2. Click OK.

Tip
Template reports must be exported in the same file type as the template. You may want to limit the export types to only that type using the [image] Report Options > [image] General Options window.

Making templates

The process for making templates differs between the three supported types: PDF, Word, and Excel; as do the available features.

Caution
Close the template file before running or saving a report that uses it, or you may get an error.

PDF Templates

PDF templates support static fields and limited-repeating fields. PDFs are convenient for preexisting and standardized forms, such as for government or businesses.

To make a PDF template:

  1. Open a PDF in a PDF editor program, such as Adobe Acrobat or PDFescape.

  2. Add form fields where you will insert report data. For fields where text may span multiple lines, select the multi-line property.

  3. Give each form field a unique name, as follows:

    • For static fields, which appear only once per template instance, use any name, with the exception of the format reserved for repeating fields.

    • For repeating fields, which are mapped to consecutive values in a repeating cell, use the following naming format:

      Name.0, Name.1,

      Where Name is shared by the repeating fields, and 0 maps to the first value, 1 maps to the next value, and so on.

  4. Save the PDF. Then upload it to the report.

Microsoft Word Templates

Word templates support static fields, limited and unlimited-repeating fields, and conditional suppression.

Tip
Supported file types are .doc, .docx, and .rtf. Report templates exported as "RTF" will save to the original file type.

To make a Word template:

  1. Open a Word file in Microsoft Word or a compatible document editor.

  2. Add text where you will insert report data.

  3. Select the text and add a bookmark.

  4. Give each bookmark a unique name, as follows:

    • For static fields, which appear only once per template instance, use any name, with the exception of the formats reserved for repeating and conditional fields.

    • For limited-repeating fields, which are mapped to consecutive values in a repeating cell, use the following naming format:

      Name_0, Name_1,

      Where Name is shared by the repeating fields, and 0 maps to the first value, 1 maps to the next value, and so on.

    • For unlimited-repeating fields, which are mapped to all the values in a repeating cell, use the following naming format:

      RepeatForEach_Name

      Where Name is a unique name.

  5. Optional: To conditionally show or hide text:

    1. Select the text and add a bookmark with the following naming format:

      KeepIF_Name

      Where Name is a unique name.

    2. In the report, map this field to a cell with a formula that returns 1 if the text should be shown, and 0 if the text should be hidden.

      Example

      =If ( {Products.ProductName} = "Chai", 1, 0 )

  6. Save the file. Then upload it to the report.

Microsoft Excel Templates

Excel templates work differently than other types. Templates are used to fill Excel columns with report data. This is useful for passing data to Excel charts, pivot tables, and macros.

To make an Excel template:

  1. Open an Excel file in Microsoft Excel or a compatible spreadsheet editor.

  2. The first worksheet is used for dynamic report data. Repeating cells are mapped to columns in the worksheet. For each column where you will add report data, enter a unique name to the topmost cell in the column. All following cells must be empty.

    Formatting an Excel file to be used as a template

  3. Save the file. Then upload it to the report.


Joins

Caution
The Joins window is recommended for advanced users only.

Joins describe how the categories on a report are related to each other. When two categories are joined, a field in the first category is associated with a field in the second category. Wherever a value in the first category's field matches a value in the second category's field, that value's rows from each category come together to form a composite row. The table produced by all the composite rows is the resulting data that appears on the report.

For example, take the following categories, Orders and Products. The Orders.ProductId field corresponds with the Products.Id field. When the categories are joined from Orders.ProductId to Products.Id, the rows are connected wherever those two fields have matching values.

Categories joined on Orders.ProductId >> Products.Id

The result of this join is the following composite rows. These categories have a one-to-one relationship, because each row in the "left" category joins at most one row in the "right" category.

Joined categories. Products.Id is omitted.

Tip
Categories could be joined along more than one set of fields; composite rows are formed only when all sets have matching values.

For two categories to be copresent on a report, there must be a join path between them. They are either directly joined, or there is a path through one or more intermediate categories. You do not have to configure joins manually - they already exist in the environment. However, if you want to learn how to add or adjust joins on a per-report basis, this topic will explain the options that are available.

Join Types

The join that was previously described is the most common type of join, an inner join. When an inner join is applied, rows in either category that have no matching row in the other are excluded from the resulting table. However, you may not want to exclude these rows. To do so, you can change the type of join to an outer join.

For example, the row in the Products category with Id: 12 has no matching row in the Orders category. With an inner join, this row is excluded from the output. If you want to see the Products rows that have no matching Orders row, you can change the join type.

To do so, from the [image] Report Options > [image] Advanced > [image] Joins window, select the Products data that does not have Orders data check box.

Left outer join

This changes the join between these categories to a left outer join, because all rows from the left category are included. The following rows result:

Joined categories with all Products rows. Products.Id is omitted.

Similarly, selecting the Orders data that does not have Products data check box changes the join to a right outer join, which includes all rows from the right category. Selecting both check boxes includes all rows from both categories; this is a full outer join.

Relationship Types

There are two types of join relationships: one-to-one and one-to-many.

In the previous example, the relationship between the categories is one-to-one, because each row in the left category joins at most one row in the right category. Some categories have a one-to-many relationship, where each row in the left category joins zero or more rows in the right category.

A one-to-many relationship from categories X to Y is represented in the following diagram:

Each X is joined to one or more Y

Reports with a single one-to-many join are well suited to grouping by the left category. The data in these reports is generally well-formed and understandable.

However, when a report has multiple categories with one-to-many joins, data can appear more disorganized and confusing. For example, the following diagram represents data from three categories, X, Y, and Z, where the relationships between X - Y and X - Z are both one-to-many:

Each X has 1 or more Y, and 1 or more Z

Because Y and Z are not directly related to each other, there are many rows with only Y or only Z. This can cause the report to be significantly larger, and to be difficult to read and interpret. This occurs even with inner joins, the most restrictive type, because by default there is no logic that deals with the relationship between Y and Z.

Read on for different ways of improving the structure of a report with multiple one-to-many joins.

Cartesian Processing

You could fill the blank spaces with supplementary data by disabling Special Cartesian Processing from the Joins window. Blank cells are filled in with data that is repeated directly from the previous row. The following diagram demonstrates how this works:

Disabling Special Cartesian Processing

The shaded cells represent data that has been repeated from the previous row. This can make the report more readable. However, this approach poses a problem: Blank cells indicate a lack of a relationship between two fields, so filling in these spaces with artificial data can obfuscate any relationship between Y and Z. This can decrease the accuracy of the report.

There are better ways to improve the readability of such a report without sacrificing accuracy:

  • Use repeating groups to show the X - Y and X - Z relationships in entirely separate sections. This is suitable if any relationship between Y and Z is irrelevant or nonexistent. See Sections for more information.

  • Hide some or all of the rows which do not have data for both Y and Z. This is suitable if you want to highlight an implicit or indirect relationship between Y and Z. This is done by imposing Must constraints. Read on for more information.

Must Constraints

Although Y and Z are not directly joined, they are both related to X, so there is an implicit relationship between them. If you examine Y and Z alone, you will notice that they technically exhibit a full outer join.

Y and Z, without X

Because all the rows from Y and Z that relate to X are shown, there are rows with both Y and Z, or with only one of either. Must constraints allow you to change the implicit join type, and in doing so, eliminate rows that lack data from one or both categories.

To set Must constraints, from the Joins window, locate the MUST panel for the applicable categories:

Setting Must constraints

Do one of the following:

  • Select the Y check box - Left outer join: Any rows without Z are removed

  • Select the Z check box - Right outer join: Any rows without Y are removed

  • Select the Y and Z check boxes - Full outer join: Any rows without Y or Z are removed

Effect of setting various Must constraints

Modifying Joins

The Joins window shows all direct and implicit joins on the report. Direct joins can be added, modified, or removed from the report.

To add a new join:

  1. Select From and To categories.

  2. Click [image] Add.

  3. Click [image] Add Condition then select From (left column) and To (right column) fields.

    Tip
    If there are multiple conditions, only the rows that satisfy all the conditions are joined.

  4. Click OK.

To remove conditions, click the Delete [image] icon next to the condition to delete.

To modify a join's fields:

  1. Click the Edit [image] icon next to the join to edit.

  2. Add, remove, or modify conditions.

  3. Click OK.

To remove a join, click the Delete [image] icon next to the join to delete, then click OK.

To restore the default joins, click [image] Recreate, then click OK.

Advanced Joins

You may be able to specify join conditions that are more complex than column equality.

Type

Instead of joining between two columns, one or both sides of the join may instead be an arbitrary expression, constant, or SQL sub-query that you specify.

To change the expression type for one side of a join condition, select one of the following from the Type list:

  • Value: One or more constant values separated by commas

  • Expression: Formula or calculation

  • SubQuery: SQL query

Then enter the value in the Value field.

Operator

As opposed to the default equality (=) operator, which joins fields from the left expression to matching fields from the right expression, a join condition can use one of several alternative operators instead. For example, the inequality (!=) operator joins fields on the left to non-matching fields on the right. To do so, select one of the alternative operators from the Operator list:

  • != not equal
  • > greater than
  • >= greater than or equal
  • < less than
  • <= less than or equal
  • IN match one or more values, either specified or calculated from a subquery

Grouping

When a join has two or more conditions, you can specify how the conditions should be met as a group in order for the join to take effect.

To specify that either one of two conditions will satisfy part of the clause, select OR from the Conjunction list for the first condition of the two.

To add parentheses around two conditions, select the Group check box for the first condition of the two.

You can preview the full join clause in the Summary field.


Events

This menu allows you to add server events to the report. Server events are custom bits of functionality created by the systems administrator for a reporting environment. For more information about your available server events, contact your administrator.


Cell Format

The cell formatting menu allows you to customize how data values will show, add custom borders, and add formatting that will only show if a condition is met.

Number

If a cell has a numeric, date, or time value, then you can use Number formatting to choose how the value should appear on the report. For example, you could add a dollar sign ($) to monetary values and separate each three digits to make values easier to read.

Numeric values with currency styled formatting

The following options for Number formatting are available:

General

Format the data using the default settings for your environment. This is the default option. The application will assume the data type based on the value.

Number

Format the data as a number, currency, or percentage.

Optional: Choose how the number displays:

  • In the Decimal Places field, enter a number for how many decimal places to display. Then, in the field to the right, enter a symbol to use as the decimal mark.

  • To show a delimiter every three digits, select Use 1000 Separator. Then, in the field to the right, enter a symbol to use as the delimiter.

  • To show a currency symbol before the number, select Use Currency Symbol. Then, in the field to the right, enter the symbol to show.

  • To show a percent sign (%) after the number, select Append Percent Sign.

  • To show no value if the number is 0, select Blank When Zero.

  • To show a minus sign (-) in front of negative numbers, select Show Negative Symbol.

  • To show parentheses ( ) around negative numbers, select Show Parenthesis.

  • To show negative numbers in a different color, enter a color code in the Color field or use the color picker to choose a color.

Date

Format the data as a date, time, or date and time.

Optional: Choose which date and time components to display, and how to show them. Either select one of the patterns from the Date/Time Format list, or enter a custom pattern using the following variables:

Variable | Description | "Sept-2-1907 5:08:04 PM"
d | day of the month, from 1 to 31 | 2
dd | day of the month, from 01 to 31 | 02
ddd | day of the week, abbreviated name | Mon
dddd | day of the week, full name | Monday
M | month, from 1 to 12 | 9
MM | month, from 01 to 12 | 09
MMM | month, abbreviated name | Sept
MMMM | month, full name | September
y | year of the century, from 0 to 99 | 7
yy | year of the century, from 00 to 99 | 07
yyyy | year, from 0001 to 9999 | 1907
h | hour using a 12 hour clock, from 1 to 12 | 5
hh | hour using a 12 hour clock, from 01 to 12 | 05
H | hour using a 24 hour clock, from 0 to 23 | 17
HH | hour using a 24 hour clock, from 00 to 23 | 17
m | minute, from 0 to 59 | 8
mm | minute, from 00 to 59 | 08
s | second, from 0 to 59 | 4
ss | second, from 00 to 59 | 04
t | A/P | P
tt | AM/PM | PM

Text

Do not apply any formatting to the data, and show it exactly as it appears in the database.

Border

Alter the width and color of the cell borders. To set a color for a cell border, enter a color code or select a color from the picker. To set the width of the border, enter a pixel value, or use the arrows to make the border thicker or thinner.

To set all the cell borders to the same color and width, select Make Borders Uniform.

Tip
If gridlines are enabled for the Report Viewer, then cell borders will show in addition to the gridlines.

Choosing border colors and widths

Conditional

A conditional format allows you to format a cell according to its output data. The cell and text styles can depend on its data value, and you can even conditionally hide rows or entire sections. This can be useful for highlighting certain values in a data set, such as outliers from a trend.

Conditional formatting uses a formula to set the condition. The formula must evaluate to True or False: If True, the formatting will be applied, and otherwise it will not. Conditional formulas are often based on data in the cell, but they can also be based on other cells, data fields, or other information about the report.

Example of a formula that evaluates to True or False

To set or modify the format of a cell based on a conditional formula:

  1. Click [image] Add to create a new condition.

  2. From the Action list, select an action to occur if the condition is met.

    Optional: If applicable, select an attribute for the action from the Attribute list.

  3. Click the formula [image] icon and enter a formula for the condition. The formula must evaluate to True or False.

    To use the value of the current cell in the formula, use the function CellValue(). Click [image] Cell Value to insert CellValue() into the formula.

A cell can have multiple conditional formats, each of which is a separate row in the Conditional page. If two or more overlap, the lower condition takes precedence. Click the Move Row Up [image] and Move Row Down [image] icons to reorder the precedence of the conditions.

A cell with multiple conditional formats


Formulas

Formulas allow you to do calculations, make logical comparisons, parse strings, insert images, and more. Formulas are compositions of functions, parameters, data fields, cell references, and number, date, or text values.

To add a formula to a report cell, select the cell, then click the formula [image] icon to open the Formula Editor window. The Formula Editor has a suite of features to help guide you when using formulas.

You can use the Search field to search through the functions by name. Or start typing in the Formula field to get a list of functions that match the text.

List of functions that match the text

Click on a formula, or use the up and down arrow keys, to highlight a function and see a description of what it does. To add the selected function to the formula, press the Enter key.

Tip
You can search for data fields, report cells, and parameters in the same manner.

Using functions

Formulas work by applying some calculations to a few values that you give them. A basic example of a formula is 1 + 2. In this example, the formula comprises one function, the addition function (+), and two arguments, the numbers 1 and 2. When the report runs, the formula calculates and returns a value of 3.

Not every function takes two arguments, so functions cannot always be written as(argument1 function argument2). In most cases functions instead use the following format:

Function(argument1, argument2, ...)

The addition example could also be written as Add(1, 2). This is the style that most formulas in the application use. Most functions are more abstract than simple arithmetic. Each function has a description which tells you exactly what it does and how to use it.

Arguments

An argument is a value that a function uses to do a calculation. Functions have different amounts and types of arguments. When a function is first entered into the Formula field, there are placeholder values for each required argument. Click on a placeholder to see the description of the argument.

Description of the condition argument of the If function

Some arguments are optional. Those are surrounded by brackets [ ]. Some arguments are a list of values. Those are followed by an ellipsis (...).

The Today function takes an optional argument. The And function takes a list of arguments.

Tip
Some functions take no arguments. These are formatted with empty parentheses:
Function()

You need to supply values for all of the function's required arguments. Type a value into the argument space, or drag a data field or function over the placeholder.

Dragging a data field to an argument

Click OK when you are finished.

Formulas are used in several areas besides the report design: custom sorts and groups, drilldowns, conditional formatting for cells and charts, and custom crosstab fields, to name a few. In every area where you can use a formula you can click on a formula [image] icon to open a Formula Editor window. These areas may require a specific type of data to be returned from the formula. Some built-in functions, such as aggregates, may be unavailable. Consult the relevant topic for the specifics.

Note about sections

Formulas which reference data fields or cells, with the exception of aggregate functions, should be in the same report section as the reference data. Detail sections repeat for every data field, group sections repeat for every group, and page sections repeat for every page. Since most formulas expect only one reference value, and not repeated values, referencing a repeated field or cell from outside of its section can return irregular data. Formulas need to repeat alongside their reference values. Aggregate functions are the exception since they are designed to evaluate once for a group of data. For more information, see Sections.

Manually typing formulas

You have the option of typing in your functions, data fields, parameters, and cell references manually. Use the following formatting guidelines.

Text

Surround text with double or single quotation marks:

"Hello, World!"

'I am on fire'

If you want to use a quotation mark in the text, then surround the text with the opposite mark:

"You're on fire"

'He says "like" too often'

Do not use quotation marks around numbers.

Data fields

{DataCategory.DataField}

where DataCategory is the name of the data category

and DataField is the name of the data field

Cell references

To use the value from another cell in a formula, use the following format:

[C#]

where C is the letter of the cell column

and # is the number of the cell row

Caution
Rearranging cells can cause cell references to break.

Parameters

Parameters return special values depending on some condition. The built-in parameters are:

  • @pageNumber@ gives the current page of the report
  • @reportName@ gives the name of the report
  • @reportFullName@ gives the name and path to the report

To find out if you have more parameters available in your environment, contact your administrator.

Syntax checking

If there are any typos in the function, the Formula Editor will underline the relevant section in red, and show a brief description of the problem. You need to fix the problem before running the report or the result of the formula will be an error.

This formula will not work without a concatenation operator "&"

Types of functions

There are several types of functions, which are grouped into the following categories:

Aggregate

Aggregate functions act on a group of data, and return a calculation based on that group. Unlike with other functions, aggregate functions return a different value based on the section of the report they are in. To get an aggregate for each group of a grouped data field, place the function in a Group Footer section. To get an aggregate for an entire data field, place the function in a Report Footer section. Aggregate functions will not work in any other report section.

An aggregate function in a Group Footer and Report Footer (output in following image)

The aggregate values for groups and for the entire report

Operators

Operators perform basic mathematical or logical operations on two arguments. Unlike other functions, operators go between their arguments, and parentheses are not necessary. For example:

  • ="Hello "&"world!"

  • ={Order.UnitPrice}-{Order.Discount}

The operator ^ is also available, such that A^B returns the result of raising A to the power of B. This would be written as AB on paper.

There are also several logic operators that are listed in the following section.

Logical

Logic functions measure truth conditions, which are used in conditional functions to do different things depending on whether their arguments are true or not. The main conditional functions are the If() function, the Switch() function, and the conditional formatting editors for cells and charts.

What is truth? (advanced users)

Functions which measure logical conditions will return a value of either True or False, depending on whether the condition is met or not. These values, also called Booleans, are represented implicitly when passing the result of logic conditions to other functions. This means that there is no explicit representation of True or False that you can display in a report cell. Rather, if you want to display the result of a logical function, the application will convert True or False to strings with the text "True" or "False".

Tip
Strings are surrounded by double quotation marks (").

For example, the formula =(2 > 1) has an implicit value of True. In a report cell, this would resolve to the string "True". However, for the formula =If(2 > 1, x, y), the If() function evaluates this argument as the Boolean value True, not as the text string "True".

Some functions have Boolean arguments that you simply want to pass a static truth value. To pass a static True or False to other functions, use the functions True() or False().

Note about conditional formatting

Conditional formatting formulas must evaluate to either True or False. Consider these formulas each to be the condition for an invisible If() function. If the condition is met, then the formatting will take place; and otherwise, it will not. You should not manually put an If() function inside a conditional formula in most circumstances.

Logic operators

The following logical operators are available:

  • = equal

  • != not equal

  • > greater

  • < less

  • <= less or equal

  • >= greater or equal

Logic operators are used as A•B, where A and B are arguments and • is the operator. They return True if the operant condition is met, and False otherwise.

Date

Date functions do calculations based on Dates, Times, or a type called a DateTime, which is a combination of a date and time.

  • Dates can represent a day or month of the year, such as January or January 1st, or a specific date, such as January 1st, 2017.

  • Times can represent a quantity of time, such as one year, or a time of day, such as 12:00 pm.

  • DateTimes represent a specific date and time, such as January 1st, 2017 12:00pm. However, sometimes functions will return only a date or time as a DateTime type. There will be a default or placeholder value for the other component, which should be ignored.

Functions that input a date accept a Date or DateTime argument. Functions that input a time accept a Time or DateTime argument. To pass text as a date or time to a function, first wrap the text in the DateValue() or TimeValue() function. Some functions can interpret a date or time as a string, but as a general rule, it is better to convert the value first.

How formatting affects date display

By default, the application interprets text strings that "look" like dates or times, as a DateTime. This means that, when your cell formatting is set to General or Date, the cell contents will be displayed in the DateTime format. You can test this out by simply typing "Jan-02" into a report cell, then running the report. Instead of seeing the text "Jan-02", you will see a formatted date, and maybe a time attached to the end. Note that since you did not type a year or time, the application uses placeholder values for the year (the current year) and the time (12:00 AM).

You may or may not want this to happen. An example of erroneous behavior could happen if you type a Time, say "02:00 AM", into a cell formatted as General. The application sees this value as a DateTime. If the default formatting for DateTimes omits the time component, you would end up with only a default date value displayed, and no time at all.

Change the cell formatting to Date to select how the date or time should display for this cell. For example, if you set the formatting to MM/dd, and run the report again, the date "Jan-02" will display as 01/02. This is likely closer to what you would have wanted.

If you do not want to format a date or time cell at all, set the cell formatting to Text. The cell text will be displayed as is. However, note that if you pass this cell as an argument to a date function, the text will be interpreted as a DateTime regardless of the formatting.

Day/month ambiguity (American versus European style dates)

Americans represent the date "Jan-02-2017" as 01/02/2017 when written with backslashes or hyphens. However, Europeans would write this same date as 02/01/2017 instead, with the day and month places switched. Date functions always interpret a date written this way in the American style: Month/Day/Year. To avoid potential ambiguity, dates should be written in a different way, preferably by spelling out the name of the month.

Financial

Financial functions are used for a variety of monetary calculations, such as interest rates or asset depreciation.

Database and Data Type

Data functions are used to check whether values are of a certain type. They are often used within logical formulas to handle conditions where data fields have irregular data. Such formulas are often referred to as sanity checking or error checking. Data functions can be used to display custom error messages instead of the default errors, and to make sure that a report will run even if it has erroneous data.

Arithmetic and Geometric

These functions are used for a variety of mathematical calculations, such as finding the square root of a number, or rounding a number to the nearest decimal place.

String

String functions are used for manipulating text values in different ways. They can be used for extracting parts of strings, combining multiple strings together, or transforming strings into different forms. These are best used on text that follows predictable patterns, such as street names or phone numbers. They can be combined with conditionals to handle less uniform text patterns.

Formatting

Shows the input argument in bold, italic, or underlined text. Styles apply alongside any cell formatting. These can be used to format only some parts of the text.

Other

These are miscellaneous functions that do not fit into any other categories. For more information, see the function descriptions.

Combining functions

It is possible to combine multiple functions into one formula calculation. This occurs often when using logical functions, because you will often want to evaluate a function, and then check the value with an If() function to decide what to display. This is called function nesting, since you are using functions as arguments in the parentheses of other functions.

The following formula nests a logical comparison of the result of the AggCount() function inside an If() function:

If(AggCount({Products.ProductName}) >= 10, "OK", Bold("Warning!"))

Caution
Pay extra attention to the parentheses to make sure that every function is closed correctly. This is the most common cause of errors when nesting functions.


Sections

An Advanced Report's sections define the appearance and pattern of the report. Specifically, sections determine how frequently their cell contents are repeated.

The appearance of a report is based on data that is not necessarily known at the time it is made. The data, and the relationships it describes, are dynamic and mutable. So tabular reports are usually not fixed designs. Instead, you are essentially describing how the report structures itself around a set of unpredictable data. Knowing which sections are suitable for different types of content is crucial for designing the best possible report.

Types of sections

There are two general types of sections: Static and Dynamic. The difference is in the repetition of their cells.

The cells in a dynamic section repeat according to the data that is returned to the report. Dynamic sections are suitable for showing content related to that data. For example, cells in a Detail section repeat for every composite data row in the report. Therefore it is most suitable for data fields, of which you will want to see each entry per row.

The cells in a static section do not repeat according to the report data. Static sections are best used for information that is not related to data rows. For example, you could use a Page Header to define column headers that describe the type of data in each field.

Static header cells used to describe the data in dynamic detail cells

Each section type has several subtypes that repeat in different ways. Static and Dynamic are only descriptive keywords. The advanced Report Designer does not use these terms - sections are referred to by their subtype.

See the following topics, Static and Dynamic, for more information.

Manipulating sections

A report can contain any variety of sections desired. To add, remove, or change sections, click a section title, to the left of the row numbers in the report grid. The section menu will appear.

The section menu

To add a section, hover over [image] Add Section, then choose the section to add. If you add a group section, choose which data category or field to group by.

To change the category or field for an existing group section, click [image] Modify Section.

To change the order of sections, click [image] Move Section Up or [image] Move Section Down.

To delete a section, click [image] Delete Section. This will delete every row in the section.

To add alternating background colors for each row in a section, click [image] Section Shading. See Section Shading for details.

Static

Sections categorized as static do not repeat according to the data in the report; although they may repeat by other means. These sections are best used for information about the report and about the data in the report. They can also be useful for boilerplate elements, such as logos or disclaimers.

A typical report may have a report header with the title of the report, a page header with labels for each column, a page footer that uses the @pageNumber@ parameter to number the pages, and a report footer with summary information.

Page Header/Footer

Position of Page sections

Page sections repeat for each page - the Header at the top, the Footer at the bottom. Page breaks are usually caused when the data in dynamic sections overflows the length of a page. The length of pages is determined by the Page Size and Orientation lists in the Report General Options window. The data on each page is consistent between the paged output types.

You can add page breaks manually by clicking the row number to add a break, then selecting [image] Page Break from the menu. Page breaks added for a row in a dynamic section will make a new page every time the data changes.

Tip
Excel and CSV output types have no pages. Page sections function the same as Report sections for these output types.

Use these sections for information to repeat on every page, such as the title of the report, column headers, and company logos. You can also use the special @pageNumber@ parameter to number each page of the report.

Even though page sections repeat, they are not considered dynamic sections because their repetition does not directly depend on the data. Thus they are not suitable for displaying data fields. And because there is no way of knowing ahead of time which data will appear on which page, these sections are not suitable for summarizing data with aggregates or visualizations.

Report Header/Footer

Position of Report Header and Footer sections

Report header and footer sections appear once each - the Header at the beginning, the Footer at the end. Use these sections for introductory and concluding information.

The report footer is where you would summarize all of the report data with aggregate formulas and visualizations.

Dynamic

Sections categorized as dynamic repeat according to the data in the report. These sections are best used for displaying and grouping data from data fields. Dynamic sections comprise the bulk of most tabular reports. Charts, maps, and gauges must reference data fields in dynamic sections.

The key for knowing how to use dynamic sections is knowing how the data will repeat in relation to the rest of the report.

Detail

Position of Detail section

The rows in the Detail section repeat for each data row retrieved from the database. By default, there are exactly enough Detail rows to show all the data fields in all the categories on the report.

Detail sections are typically used for showing the actual data values. This is the main area of display for the bare report data. Aggregate formulas usually reference cells in the Detail section.

You can use duplicate suppression to limit the number of rows to only visible data fields. You can also use filters to limit the rows by certain criteria.

Users with advanced knowledge of the data relationships can also use advanced join logic to limit the Detail rows to only the relevant relationships.

Group Header/Footer

Position of Group sections

Group sections are the primary way in which data is grouped into "buckets" that each share a unique attribute. Detail rows are arranged into the groups they belong to. The common attribute is defined by a preexisting sort. In order to make a group, first add a sort in the Sort window.

For example, if you wanted to group a set of rows by each Product, first add a sort on the Products.ProductName field.

A sort is required for a group

The main difference between the header and footer is that the data comes after the header, but before the footer. This means that you can only use aggregate formulas in the group footer, since the header is not aware of the data in its group.

When you add a group section, you are asked which sort to base the group on. For each sort, you can choose to group on either the sort field or the sort category.

  • If you group on a field, the group breaks at each new value of the field.

  • If you group on a category, the group breaks at each new instance of the category's unique identifier.

The group window appears when adding a group section

For example, instead of grouping by each Product, you may want to instead group by product quantity, so that products that are low or out of stock are grouped together. First add a sort on the Products.Quantity field, then group by that field.

You can have multiple group sections, if you wanted to add additional levels of stratification to the report. Each group requires a corresponding sort.

Tip
The order of the sorts has an effect on the way the data is arranged. Data is sorted by the first field, then any ambiguities are sorted by the second field, then any remaining ambiguities are sorted by the third, and so on. In general, you should order group headers by their associated sorts, and group footers in inverse order.

Repeating Group

Position of Repeating Group sections

Repeating groups are a way to organize and display data which contains multiple one-to-many relationships. Each repeating group has data from a "many" category, grouped by the "one" category. The following diagrams demonstrate how this works.

Two categories, X and Y, with a one-to-many relationship from X to Y, will display like so in a Detail section.

Tip
Category X has duplicates suppressed - this is similar to grouping on X.

Each X has 1 or more Y

If there is a third category, Z, with a one-to-many relationship from X to Z, there is not necessarily a relationship between Y and Z. So there may be blank rows with only Y or only Z.

Each X has 1 or more Y, and 1 or more Z

There are two potential solutions. If there is a relationship between Y and Z, then you can use Advanced Joins to impose additional constraints so that only the rows with both Y and Z are shown. For more information, see Joins.

On the other hand, there may be no relationship between Y and Z - only between X and Y, X and Z. Or there may be a relationship, but it does not matter for this data set. You can use repeating groups to ignore that relationship and simply organize the data by X instead.

To do so, create two repeating groups for X, and put Y in the first, and Z in the second:

Organizing Y and Z into repeating groups for X

Each X repeats twice: once for Y, once for Z

Each repeating group section has its own group header, detail, and group footer. These follow the same principles as their general purpose equivalents, but within the repeating groups. For example, to calculate aggregate data for Y for each X, use an aggregate formula in the footer for the repeating group with the Y data.


Section Shading

Section shading allows you to specify alternating background colors for repeating elements in a section. Alternating colors applies to Detail and Group sections; for other sections, only one color can be used.

To add section shading:

  1. Click a section title to open a menu for that section. Select [image] Section Shading.

  2. Click [image] New to add a new shading color.

  3. For each shading color, select a color with the color picker, or enter a color code.

  4. Use the up [image] and down [image] arrow icons to rearrange the order of the colors.

  5. Click OK.

Adding two alternating background colors to a Detail section

Tip
Section shading is overridden by the cell background color and by conditional formatting.


Crosstabs

A crosstab, or cross tabulation, is a widget for grouping and summarizing data fields that automatically expands vertically and horizontally depending on the number of data groupings. Crosstabs are also known as two-way tables, contingency tables, and pivot tables.

Using a crosstab on a report will prevent you from using any of the cells below or to its right. Therefore, it is recommended to use crosstabs as the main data display for the reports they are on.

To add a crosstab to a report:

  1. Select a report footer cell, then click the Crosstab Wizard [image] icon.

  2. To add rows, drag data fields to the Row Header Source pane. To add columns, drag data fields to the Column Header Source pane.

    Adding more than one row or column creates data groups from the selected fields. Drag the rows up or down to select how they are grouped.

  3. Select which field to collect data by dragging the field to the Tabulation Data Source pane.

  4. Optional: To use a formula instead of a data field, click the formula icon [image] and enter a formula.

  5. Optional: Click the edit [image] icon to select options for a data field:

    • Label: Edit the title of the field.

    • Method: Select how to sort the data. Select a Direction.

    • Placement: Select whether to show a Total and Subtotal, and where to display them.


Charts

Charts illustrate report data in order to visualize trends and patterns. Each data field on a chart is a series of data, which have a common association and are connected in some way. A chart with one data field is a single-series chart, which is useful for comparing data values to each other. A chart with multiple data fields is a multi-series chart, which is useful for comparing trends.

Before creating a chart, make sure that your data is laid out on the report. These cells can be suppressed so that they are not visible in the output. Charts are interactive in the Report Viewer and Dashboard Viewer, but will appear as static images in PDF, Word, and Excel files. CSV files cannot show charts.

To insert a chart into a report, select a Group Footer or Report Footer cell and click the Chart Wizard [image] icon. Progress through the Type, Data, and Appearance pages, then click Finish when done.

Type

The Type page lays out the available chart types. There are five categories of charts which comprise several related chart types:

Line Show trends over time | Bar/Column Compare values between groups | Pie Compare portions of a whole | Scatter Show a relationship between groups | Combination Several charts combined into one
Spline Line chart with interpolated curves | Stack Compare portions of groups | Doughnut Pie chart with a center cutout | Zoom Scatter Zoom into clusters of points |
Area Show cumulative totals for trends over time | 100% Stack Compare percentages of groups | Pyramid Compare portions of a hierarchy | Bubble Show multiple relationships between groups |
Spline Area Area chart with interpolated curves | Pareto Show frequency of values and cumulative total | Funnel Show a value reducing over time |  |
Spark Line Simply show a trend over time | Spark Column Simply compare a metric over time |  |  |
Zoom Line Zoom into line segments |  |  |  |
Statistical Process Control Show when a process exceeds limits |  |  |  |

Click a chart icon to select that type, then proceed to the Data page. Chart types have different requirements, so if you return to this page and change the type, be prepared to clarify the following pages as well.

Data

Specify which cells to use as chart data. You can change how data is translated into points by changing the Data Layout. You can also choose a Sort Order, as well as Upper and Lower boundaries for the data and axes, if applicable.

Add series to the chart by selecting a data field containing numeric values from the Series Values list. Some charts may require you to select a data field to label the X-Axis. Some charts may ask for two or three data fields per series. The data axis is drawn automatically when the chart is generated.

Tip
Data is on the Y-Axis; this may not always be the vertical axis. Labels are on the X-Axis; this may not always be the horizontal axis. Scatter charts have no labels axis, but have X and Y data axes.

For multi-series charts, you can add additional series by clicking the [image] Add Series icon. Enter a Name for each series.

Data Layout (advanced users)

The data layout types allow you to select from a few different ways to build a chart.

Column Based Chart

The default layout, column based charts are used for two situations:

  1. Mapping data fields to X and Y axes, across common rows

    Example: Each OrderDetails.Total for each Orders.OrderDate

    Column based line chart for Sales per Date

  2. Mapping group aggregates against a sort field or category

    Example: The aggregate sum of OrderDetails.Total for each Employee

    Column based pie chart for Sales per Employee

Row Based Chart

Row based charts are multi-dimensional. They are used for visualizing nested groups, or in other words, some grouped data per an outer group, per another outer group.

Example: The aggregate sum of OrderDetails.Total for each Month(Orders.OrderDate) for each Employee

Row based bar chart for Sales per Month per Employee

Cell Based Chart

Use this layout to build charts statically, from non-dynamic data. Enter coordinates onto the report grid manually.

Data points must be formatted as formulas, with a leading equal sign (=):

Number: =42

Text: ="February 24th"

Data field: ={Employees.EmployeeName}

Formula: =Month{Orders.OrderDate}

Math: ={Orders.UnitPrice} * 2.43

Appearance

Customize how the chart looks, and add some additional features, such as axes and benchmark lines.

Colors

Select a color theme to apply to the chart. Use a custom theme by selecting the Linear Range or Custom Colors options. For some chart types you can select Use 3D Style to give the chart a three-dimensional look. For SPC and combination charts you can choose whether to Allow Scrolling left or right in the chart output.

Labels

You can optionally add labels to different parts of the chart. Some labels are only available for some chart types.

  • Chart Title

  • X-Axis Title for line charts—except spark and SPC—and bar and scatter charts.

  • Y-Axis Title for line charts—except spark and SPC—and bar and scatter charts.

  • Point Labels can be the series, labels, values, or percentages; options are based on the chart type. Not available for scatter or spark charts.

  • Legend Position adjacent to the chart, or hidden. Available for pie and stack charts, or any chart—except spark—using the Row Based layout.

  • Label Font of point labels

Number Format / Chart Axes

For charts with no axes, or which only support one Y-axis, the Number Format window lets you choose how the axis values and point labels are formatted. See Number Format for more information.

Line charts—except zoom and spark—and combination charts support up to two Y-axes. For these charts, the Chart Axes window lets you add a second Y-axis, label and format axes, and assign each series to an axis. The following options are available:

  • X-Axis Title

  • Y-Axis Formatting and title for each Y-Axis. Click [image] Add Y Axis to add a second Y-Axis. Then assign each series to an axis.

  • Y-Axis Assignment matches each series' color and point label formatting with its assigned axis. Axes and their assigned series can be toggled on and off in the chart output.

Benchmark Lines

Benchmarks are horizontal lines at locations on a chart that are used to call out certain types of values, such as ranges, limits, and averages. Benchmarks are not available for spark or pie charts. To add a benchmark line, click [image] New. Then specify its location and appearance using the following options:

  • Line Type and Value determine the location of the benchmark. This can be a static location or derived from a calculation on the series data. The following line types are available:

    • Static point specified in the Value field

    • Minimum value in the series

    • Maximum value in the series

    • Mean, or average, of the series

    • Median, or midpoint, of the series

    • Standard Deviation of the series, as given by the Value formula:

      μ + nσ

      You can set how many (n) standard deviations (σ) from the mean (μ), and in what direction (+/-), the line is drawn.

      Use Population for a full data set. Use Sample for a portion of a larger set.

    • Tip
      Statistical Process Control charts have benchmark lines at the mean and at +3 and -3 standard deviations from the mean by default. These cannot be removed, but you can change their formulas and appearances if desired.

  • Label for the line

  • Color of the line, selected using the picker or by entering a color value

  • Style of the line, either Solid or Dashed

Conditional Colors

Bars, columns, segments of pie charts, points in line and scatter charts, and bubbles in bubble charts can change color conditionally, depending on a formula. To add a conditional formula, click [image] Add. Choose a color by using the color picker, or enter a color value. Then click the Formula [image] icon to open the Formula Editor and set the condition.

There are additional parameters available when making chart conditional formulas that allow you to set the condition for coloring points based on the point data. The following parameters are available depending on the type of chart and data layout:

  • @data_label@ - the value of the point's Data Label field, or Y Value field for scatter and bubble charts

  • @data_value@ - the value of the point's Data Value field, or X Value field for scatter and bubble charts

  • @series_label@ - the value of the point's Series Label field for charts using the Row Based layout

  • @bubble_size@ - the value of the point's Bubble Size field for bubble charts

  • @bubble_label@ - the value of the point's Bubble Label field for bubble charts

Conditional formulas must evaluate to True or False, to determine whether the condition has been met and the color . See Conditional Formulas for more information.

Other Features

Other features are miscellaneous attributes that affect the appearance of the chart. To add an attribute, select it from the list, click [image] Add Attribute, then set its value. The following attributes are available:

  • Font color affects the color of all text in the chart

  • Background opacity is the transparency of the chart, from 0 (transparent) to 100 (opaque)

  • Font size affects the size of axis and point labels

  • Background color

  • Title alignment is the horizontal alignment of the chart title

  • Title font size

  • Title on top sets whether the title is above or below the chart

  • Show border adds a thin gray border around the chart

  • Subtitle is additional text below the chart title

  • Subtitle font size

Advanced Features are recommended for advanced users. Ask your administrator for available features.


Gauges

A gauge is a visualization that shows a linear quantity as a percentage of a total. Gauges are often used to visualize occupancy or progress.

To add a gauge to a report, select the report cell where you want the gauge to appear. Then click the [image] Gauge Wizard icon. Proceed through the Appearance and Data pages, then click Finish when you are done.

To edit a gauge, double-click it, or select it and click the [image] Gauge Wizard icon to reopen the wizard.

Appearance

There are four types of gauges available:

Angular | Linear | Bulb | Thermometer
Arc shaped scale with colored ranges. Rotating pointer to value. | Horizontal scale with colored ranges. Sliding pointer to value. | Single circle calls out current range by its color. Value shown below. | Vertical scale. Fill level indicates the value.

Select a gauge type, then set its size by specifying fixed Height and Width dimensions, or select the Fit to Cell check box to have the gauge scale to the size of the report cell.

Data

Gauges are a linear scale that is usually divided into ranges, each representing a category of meaning. In the Data page, set the data point and data ranges of the scale. Ranges can be fixed or based on dynamic data values.

Value and Range

Select a cell from the list to set the gauge data value.

To set the range, choose one of the following range types:

  • To use a fixed range, select the Static Value check box. Then enter the min and max values for the range, as whole numbers.

  • To use a dynamic range, select the Cell Value check box. Then select the cells to supply the min and max values for the range.

  • To use a partially dynamic range, select the Cell Value check box. Select the cell to supply the dynamic endpoint value. Then for the fixed endpoint, select a report cell containing a fixed numeric value, as written with a preceding equal sign (=). For example: =100.

    Selecting a fixed numeric cell

Color Ranges

Colors are used to classify and distinguish ranges of data. Ranges are set by percentage, fixed values, or dynamic values. A Bulb gauge must have two or more ranges in order to be meaningful. Thermometer gauges have only one color.

To add color ranges to a gauge:

  1. Click [image] Add for each range to add.

  2. Choose how to set the ranges:

    Tip
    Each value is the upper endpoint for that range, which starts from the end of the previous one. Fixed values must be whole numbers in ascending order. Values cannot exceed the max value of the gauge. The last range's upper endpoint is the end of the gauge. Ranges will not overlap.

    • To use percentage ranges, select the Percentage check box. Then enter a value for each range.

      Percentage ranges are fixed proportions on the gauge, irrespective of the total range or data value.

    • To use fixed ranges, select the Static Value check box. Then enter a value for each range.

    • To use dynamic ranges, select the Cell Value check box. Then, for each range, select a cell to supply the value.

      Dynamic ranges are ordered by value, not by the specified order.

    • To use partially dynamic ranges, select the Cell Value check box. For each dynamic range, select a cell to supply the value. For each fixed range, select a report cell containing a fixed numeric value, as written with a preceding equal sign (=). For example: =100.

  3. For each range, enter a color code or use the color picker to select a color.


Geocharts

Geocharts are interactive maps that plot data per geographic area. Geocharts use data highlighting, whereby data color-codes areas in varying levels of intensity. Highlighting can affect multiple levels of granularity, to allow for drilling down into regions.

A geochart viewed at the country level and the city level

To add a geochart to a report, select the report cell where you want the geochart to appear. Then click the [image] Geochart Wizard icon. Proceed through the Type, Locations, and Data pages, then click Finish when you are done.

To edit a geochart, double-click it, or select it and click the [image] Geochart Wizard icon to reopen the wizard.

Type

Choose a Base Map from the list. This selection determines the initial, maximum view level of the geochart. Data from outside this region is not shown on the map.

Set the size of the geochart by specifying fixed Height and Width dimensions, or select the Fit to Cell check box to have the geochart scale to the size of the report cell.

From the Colors list, determine the color range to use for highlighting. Do one of the following:

  • Select a theme. For most themes, lighter highlight colors represent lesser values, and darker colors represent greater values.

  • Choose Linear Range and select a custom color range. The highlight trends towards the first color for lesser values, and towards the second color for greater values.

    If you only want to differentiate values by marker size, choose the same color for both ends of the range.

Tip
Areas with no data are colored gray.

Select the Show Legend check box to show an explicit scale for the color levels.

Locations

Geocharts support up to three levels of granularity:

Country

Highest level. Select a data cell with country names if the Base Map and data comprises multiple countries.

This level is required if your Region or City names may be ambiguous between countries. For example, "Vancouver" is ambiguous between "Vancouver, British Colombia", and "Vancouver, Washington".

Region

Middle level. This is for country subdivisions such as States, Provinces, or Districts.

This level is required if your City names may be ambiguous between regions. For example, "Portland" is ambiguous between "Portland, Oregon" and "Portland, Maine".

City

Lowest level. Geocharts have no boundary data for cities, so they are shown as circular markers.

In the Show last location type as list, choose how to demarcate the areas at lowest level in your geochart. If country or region is the lowest level, select either circular Markers or geographic Regions. If city is the lowest level, you must select Markers.

Regions versus Markers

Naming scheme

Geographic naming schemes must be consistent within each level. Countries can be named by either their country code abbreviation, English name, or regional name. But do not mix schemes or the application may not interpret the data correctly.

For example, pick one of the following schemes:

  • Germany | Italy | United States

  • Deutchland | Italia | United States

  • DEU | ITA | USA

  • DE | IT | US

Follow similar rules for regions. Cities should be identified by their full names.

Data

Values can be supplied for highlighting and marker size. Each dimension could represent the same data aggregation, two different aggregations of a data set, or two distinct data sets. Data values should be in a Detail or Group section.

Color of Locations must be set.

Size of Markers is optional. If this is not set and the geochart uses markers, Size of Markers uses the Color of Locations field. If the geochart does not use markers, this field is ignored.

For each data dimension to use:

  1. Select a cell to supply the Data Values.

  2. In the Data Labels field, enter a label that will prefix each value when a user hovers over a location.

  3. Choose which aggregate method to use from the Aggregate Type list.

  4. From the Display Format list, choose how the data values are formatted in the legend and hover popup.

  5. From the Decimal Places list, choose a number of decimal places to round the data values.

To use fixed-size markers instead of dynamic size:

  1. Select a report cell containing a fixed numeric value, as written with a preceding equal sign (=). For example: =10.

    Use a value appropriate for the values in the data set.

  2. From the Aggregate Type list, select Avg.


Google Maps

Google Maps are interactive maps that plot data per geographic area. Google Maps use data highlighting, whereby data color-codes areas in varying levels of intensity. Highlighting can affect multiple levels of granularity, to allow for drilling down into regions.

A Google Map viewed at the state level and the county level

To add a Google Map to a report, select the report cell where you want the map to appear. Then click the [image] Google Map Wizard icon. Proceed through the Locations, Data, Appearance, and Size and Preview pages, then click Finish when you are done.

To edit a Google Map, double-click it, or select it and click the [image] Google Map Wizard icon to reopen the wizard.

Locations

Select the locations to use for data highlighting. Google Maps support up to six levels of granularity of locations. Some location types require data for enclosing types, in order to disambiguate location names. For each location type, select the report cell that contains the location names.

Use the following chart to determine the required data for different location types. Start at a slice and follow the chart inward to find its required locations.

Location requirements

Alternately, you can use latitude and longitude to place drop pins at coordinates on the map.

Caution
County and Zip Code locations are only available for data based in the United States.

Data

Select the data to highlight each location, and optionally, additional metrics to see in a tooltip when hovering over a location. The Primary Metric sets the data highlighting.

Hover tooltip with three metrics

To add a metric, click [image] Add Metric.

To delete a metric, click the Delete [image] icon next to the metric to delete. There must be at least one metric.

For each metric:

  1. Select the report cell that contains the data for that metric from the Metric list.

  2. Choose how to aggregate the data from the Agg. Type list.

  3. Enter a label for the metric in the Labels field. The label will show in the hover tooltip.

  4. Optional: Click the Number Format [image] icon to change how the data values will show in the hover tooltip. See Cell Format for details.

Appearance

In the Primary Metric Colors pane, determine the color range to use for highlighting. Do one of the following:

  • Select a Theme. For most themes, lighter highlight colors represent lesser values, and darker colors represent greater values.

  • Choose Linear Range and select a custom color range. The highlight trends towards the first color for lesser values, and towards the second color for greater values.

Tip
Areas with no data are not shaded.

In the Metric Levels pane, you can select which levels of location are highlighted. Clear the check box for a level to prevent highlighting and drilling down into that level of data.

You can also select whether to Display Drop Pins instead of highlighting for the lowest location level available in your data.

Size and Preview

Determine the size, initial view, and style of the map.

Set the initial view by panning and zooming the map. Drag in a direction on the map to pan. Click the zoom buttons to zoom in [image] and out [image].

To set the size, either:

  • Drag the lower-right corner of the map

  • Select the Set specific size check box and enter Height and Width values

  • Select the Fit map to cell check box to scale the map to the size of the cell

Select the style of map from the Base Map list.

Road map versus Terrain map


Drilldowns

Linked reports allow you to add custom drilldowns to cells or charts. Drilldowns are a means of exploring data points by "drilling down" into their background data.

Tip
Drilldowns work in the Report Viewer and Dashboard Viewer, not in exported reports.

Drilling down into a cell and a chart

Drilldowns require you to have a child report, which is an Advanced or Express report that contains the background data for the parent report. You can create a drilldown on a Dynamic cell or a chart by linking the child report to the parent report cell. Each data value, or each chart series, when clicked, filters the child report by its respective value before opening the resulting report in a window at the cursor.

Since drilldowns are themselves reports, they can have interactive sorts and filters, dynamic visualizations, they can be exported, and they can even have their own drilldowns.

Default linking

By default, the data category that corresponds to the linking cell filters the closest joined category on the linked report. See Joins for more information. If there is no join path, then you must set the linked fields manually in the Fields page.

For example, a parent report links an Employees data field to a child report with an Orders category. The two categories are joined on Orders.EmployeeId >> Employees.Id. For each Employees row, its Employee.Id value filters the linked report down to the Order rows with matching Orders.EmployeeId values.

Orders linked report filtered by Employee Id

Tip
Filters on the parent report or containing dashboard do not cascade down to linked reports. Linking filters do not cascade down to grand-children, or child reports of the linked report.

Adding linked reports

To add a linked report drilldown:

  1. Make an Advanced or Express report that contains the drilldown data. This will be the linked child report.

    Linked reports typically open in a small window, so the child report should be simple and concise. Avoid large fonts, too much static content, or making it too large in size. You should also set the General Option for No Data Qualify Display Mode to Show Report, to show an empty drilldown instead of a popup window for links with no data.

  2. In the parent report, select the cell to link, then click the Link Reports [image] icon. The cell can contain a data field, formula, or a column-based or row-based chart.

  3. Select the child report. Then click OK.

    Caution
    Because the linked report is a separate report from the parent, if you move the linked report to another folder, or remove the linked category, the link will be lost. You will have to edit the parent report and add it again.

To remove a linked report drilldown:

  1. Click the link [image] icon, or select the cell and click the Link Reports [image] icon.

  2. Click the Remove Link [image] icon.

  3. Click OK.

Caution
Adding a linked report to a cell may override or interfere with any linked Action Event on the cell. Adding a linked report to a chart may override some aspects of the chart's basic interactivity.

Fields

You may want to link on different fields or categories than the default join. The Fields page allows you to specify which categories and fields are used to determine the drilldown data.

The Fields page is suitable for the following situations, among others:

  • The default join is not the link you want to use

    Example: Linking on related fields other than the Id field, such as "Region"

    Employees.Region >> Orders.ShipRegion

  • No join exists between the From and To categories

    Example: Categories have related fields but are not joined, such as Orders and Suppliers

    Orders.ShipCity >> Suppliers.City

  • The From and To categories are the same

    Example: Fields are related to other fields in the category, such as Employee X supervises Employees Y and Z

    Employees.Id >> Employees.ReportsTo

Tip
The From fields from the parent report filter the To fields on the child report.

To specify the linked fields:

  1. Select the From Category and To Category from their respective lists.

  2. Click [image] Add for each set of linked fields to add.

    Use multiple linked fields to show only the drilldown rows that satisfy all the link conditions.

  3. For each set of linked fields, select the From Field and To Field.

  4. When finished, click OK.


Action Events

This menu allows you to add action events to the report. Action events are custom bits of functionality created by the systems administrator for a reporting environment. They are usually designed to be attached to specific types of cells or visualizations. If you have questions about what the action events do and when to use them, contact your administrator.

Caution
Attaching an action event to an incompatible cell or object can cause the report to exhibit faulty behavior.


Dashboard Viewer

When you run a Dashboard, the output is shown in a new tab called the Dashboard Viewer. Reports and charts on the dashboard are interactive, as are any embedded web pages. The dashboard refreshes periodically, or you can refresh it manually. You can also filter the dashboard with the filter sidebar or with filters on the dashboard design itself.

Viewing an interactive dashboard

To run a dashboard:

  • If you are in the Dashboard Designer, click Run Dashboard.

    Tip
    The Run Dashboard button also saves the report.

  • If you are in the folder tree, click the Run [image] icon, or click the Menu [image] icon and select [image] Run Report.

The Dashboard Viewer opens in a new tab, indicated by the viewer [image] icon. This tab represents an instance of the dashboard as you have just run it. If you go back and edit the dashboard, you need to run it again to see the changes. This will open another viewer tab, so you can go back and close the old one.

Interacting with dashboards

There are a number of ways to interact with dashboards. Your available options depend on how the dashboard was designed and the type of content in it.

Reports and visualizations

Hover over a report to show a navigation bar at the bottom of the tile. Enter text into the Find field, then click the up [image] or down [image] icons to search for the text in the report. Page through the report by using the navigation icons [image] [image] [image] [image].

Use the [image] Show Chart and [image] Show Table icons to swap between chart or tabular view for an ExpressView visualization.

Click the menu [image] icon in the top right corner for some additional view options for reports:

  • Select Expand to show a report in the full screen. Select Collapse to return to the dashboard.

  • Select Refresh Report to reload the data in the report.

Click the refresh [image] icon to refresh all of the reports on the dashboard.

Filtering data

Use dashboard filters to narrow down the data for multiple reports at once. Filter tiles can appear as scales, menus, or check boxes on the dashboard. Select one or more data values, or a range of data, to apply the filter to all its connected reports. This will automatically refresh the reports.

If there is a filters [image] icon then there are more filters available. Click the icon to open the filters pane and choose values for the filters.

If there is a parameters [image] icon then there are parameters available, which are a special type of filter. Click the icon to open the parameters pane and choose values for the parameters.


Dashboard Designer

Dashboards are a way to combine a several related reports into one unified viewing space. You can add preexisting reports to a dashboard, but you can also create new ExpressViews and Visualizations directly in the dashboard itself. You can also add images, text, and embed other web sites side by side with your data. And you can add interactive filters which can work on multiple reports all at once.

The Dashboard Designer

Double-click a dashboard to open it in the Dashboard Designer, or click the Menu [image] icon and select [image] Edit.

For more information about the types of content that can be added to a dashboard, see the following links:

Adding Reports • ExpressView Visualizations • Interactive Filters

Grid and tiles

Each element on a dashboard is a rectangular tile that can be resized and dragged to the proper location. Tiles are arranged onto a grid, and they will snap into place next to each other.

Adding tiles

To add a new tile to the dashboard, drag the New Tile icon onto the dashboard grid. You can drag to an empty location to fill the space, to the side of an empty location to take up a portion of the space, or over another tile to place it adjacent and resize the other tile to fit. You can then drag the resizing handles for fine grained control over the tile size.

A new tile placeholder will be added. Select what type of content should be on the tile:

New Visualization

Create a new ExpressView visualization right in the tile. Choose between a chart or a tabular ExpressView. See ExpressView Visualizations for more information.

URL

Embed another web page inside the dashboard. Enter the URL, or web address, to the web page.

Caution
Some web pages may not be embeddable.

Image

Upload an image from your computer. Drag the image onto the tile or click browse your files and locate the image.

Text

Enter text into a field that can be formatted and styled.

Filter

Add several styles of interactive filters, which can affect multiple reports on the dashboard. See Interactive Filters for more information.

Existing Report

Drag an existing report onto the tile. See Adding Reports for more information.

Screen fit and scaling

Tiles can resize and adjust their positions automatically to fit different screen sizes. A dashboard can resize to fit on a very large television, or on a very small smartphone screen, so you do not need to make multiple dashboards for different sizes.

You can customize how dashboards will scale to fit different screen sizes. Click the format [image] icon to open the Dashboard Format pane. The following Canvas Fit options are available:

All

Tiles will resize to fit the height and width of the screen (default).

Width

Tiles will resize to fit the width of the screen. Specify the Height of the dashboard.

None

Tiles will not resize. Specify the Height and Width of the dashboard.

Use the Snap to Grid [image] icon to choose if tiles should snap to the grid lines.

Tip
Older dashboards default to Canvas Fit: None and snapping off.

You can also use the [image] Dashboard Format pane to set the default options for the dashboard background color, tile and tile header background colors, and tile border colors and widths. These settings can be overridden for individual tiles.

Managing tiles

Every tile has a formatted header, background color and border. Some types of tiles can be set to resize differently than the dashboard canvas. And tiles have a menu with some additional options.

Tile menu

Select a tile, then click the tile menu [image] icon to access some additional options for managing tiles.

You can Delete a tile from the dashboard. You can Copy a tile then Paste it next to another. You can Expand a tile to temporarily fill the screen, then Collapse it back into place. And you can Refresh the data in Reports and Visualizations.

ExpressView visualizations can be saved as new ExpressViews by clicking Save as ExpressView. You will be asked to name the report, then taken into the ExpressView Designer.

Tile style

Select a tile, then click the Style tab to access the options for changing its appearance.

Click Tile Fit to choose how the tile scales to fit the screen. This option is not available for Filters or ExpressView Visualizations.

Click Tile Header to add header text to the tile. Then choose the text font and formatting.

Click Background and Border to change the background color of the tile and tile header, and the border color and style.

More information

See Adding Reports, ExpressView Visualizations, and Interactive Filters for more information about their unique features and options.


Adding Reports

Any Advanced Report, Express Report, ExpressView, and Crosstab Report can be added to a dashboard. Existing filters and prompting parameters can be accessed and modified from the Dashboard Designer. Reports have most of the same interactability as in the Report Viewer, with the exception of the interactive sidebar. See Interacting with reports for more information.

To add a report to a dashboard, simply drag it from the report tree onto the dashboard.

For information about managing report filters and parameters, see Filters and Parameters.

For information about what report settings are available, see Report Settings.

Filters and Parameters

When you add a report with prompting filters or parameters to a dashboard, you have several choices for how these filters can be accessed on the dashboard, as well as which reports they can apply to. Select the report tile, then click the Filters or Parameters tabs to edit the filter settings

For each filter or parameter you can do the following:

Apply to

Choose whether this filter or parameter applies to the Report, or to all reports on the Dashboard. If you select Dashboard, then the filter or parameter must be edited in the dashboard Filters or Parameters panes. Deselect the report, then click the [image] Filters or [image] Parameters tab.

Prompt for value

Select this option to prompt users to enter a value when running the dashboard.

Interactive

Interactive filters and parameters can be edited in the Dashboard Viewer filters and parameters panes.

Operator

Change the filter operator. Click the lock [image] icon to toggle whether other users are allowed to select an operator when running the dashboard.

Value

Set the value or values for the filter or parameter. If the filter or parameter is interactive or prompting, then other users can select different values when running the dashboard.

Report Settings

You can edit some additional settings for reports on a dashboard. The following settings are available:

Reload Interval

Select how often, in seconds, the report will automatically refresh in the dashboard viewer. The default '0' seconds disables automatic refresh.

Allow Searching

Show a search and paging bar to allow users to browse the report.

Allow Scrolling

Show horizontal and vertical scroll bars if the report cannot fit in its tile.

Only run report in design screen when report is manually refreshed

Do not run the report immediately in the Dashboard Designer. Choose this option for large reports which may take a while to load.


ExpressView Visualizations

You can create tabular reports and visualizations directly on the dashboard with ExpressView Visualizations. To do so, either:

  • Drag the [image] New Tile icon onto the dashboard and select Visualization.

  • Drag a field from the Data Pane directly onto the dashboard.

A KPI chart is the default visualization for a single data field. To add data fields, drag fields from the Data Pane onto the visualization. To swap to a tabular report, click the Show Table [image] icon. To swap back to a chart, click the Show Chart [image] icon.

For details on building a tabular ExpressView Visualization, see ExpressView.

For details on building a chart ExpressView Visualization, see Visualizations.

For information about what report settings are available, see Report Settings.

To save the visualization as a new ExpressView, click the menu [image] icon and select Save as ExpressView. The visualization will open in the ExpressView Designer.

To remove all data rows from the visualization and only show summary data, from the Settings pane, select Suppress Tabular Detail Rows. This may improve the performance for visualizations that do not depend on the detail values.


Interactive Filters

Interactive filters are filters that appear as tiles directly on the dashboard itself. Filter tiles have several different styles to choose between, and can resize and scale just like any other tile. Filters can apply to as many or few reports as needed.

Type

You can choose between four styles of filters and two orientations:

  • Single Choice - A drop down menu with all possible filter values. Users can choose one value.

  • Multiple Choice - A check list with all possible filter values. Users can choose multiple values.

  • Single Slider - Users can choose one value by sliding a point along a scale.

  • Range Slider - Users can choose multiple values between two points on a scale.

Select whether the filter values are oriented horizontally or vertically.

Reports

Select which reports the filter applies to. Only reports that include the filter data field are valid.

Data

Select which data field the filter applies to. The filter is automatically populated with values from the data field. You can select which value or values are selected by default when the dashboard is run. Select Prompt for value to prompt users to enter a value when running the dashboard.

Text and Format

Choose how the filter value labels are styled and formatted.


Chained Reports

Chained Reports combine multiple reports into a single multi-page document. They are a convenient way to bundle related reports into a format suitable for printing or sending to a mailing list.

For example, you could combine a report on total monthly sales over time, product sales for the month, and a report highlighting this month's top selling employee. And you could schedule it to be emailed regularly at the end of every month.

A benefit of Chained Reports is that, unless you want to use collation, they do not need to have anything in common. You could chain entirely disparate reports together without a problem.

Making a Chained Report

A Chained Report is basically a list of reports, and making a Chained Report is as simple as making a list.

To make a Chained Report:

  1. Click the New Report [image] icon and select [image] Chained Report. Enter a name for the Chained Report and select a folder where it should live. It does not have to be in the same folder as the reports that it contains.

    Caution
    A report name cannot contain the following characters:
    \ / : * ? " < > |

  2. On the Reports page, add the reports to include in the Chained Report. Supported report types are Advanced Reports, Express Reports, Crosstab Reports, and ExpressViews.

    The order of the reports in the list is the order they will appear in the output. Click the Up [image] or Down [image] arrows to move a report up or down in the list. Click the Delete icon [image] to remove a report from the list.

  3. Click the Save [image] icon to save the Chained Report.

Note about export types

If one of the reports cannot export to a certain file type, then the Chained Report cannot export to that type. All of the reports must share at least one export type, or else the Chained Report will have no compatible type to run as. Supported types are PDF, Word (RTF), CSV, and Excel (XLS).

Reports with templates

Template reports can be added to a Chained Report. The Chained Report must be exported to the same file type as the template. Multiple templates of the same file type can be chained together, with the exception of Microsoft Word based templates.

Prompting filters and parameters

If one or more reports has prompting filters or parameters, then you can set rules for what should happen when the Chained Report is run.

To edit a report's prompting options:

  1. On the Reports page, click the Report Options [image] icon.

  2. For each prompting filter and parameter, the following options are available:

    Common Prompt

    All reports which contain this filter field or parameter will use the specified value. In the Data (Prompt Text or Value) field, enter the text to prompt the user for a value.

    Report Prompt

    Only this report will use the specified value. In the Data (Prompt Text or Value) field, enter the text to prompt the user for a value.

    Assign Value

    The Chained Report will not prompt for a value for this filter or parameter. In the Data (Prompt Text or Value) field, enter the value to use.

Tip
Values for prompting filters and parameters cannot be set when scheduling a Chained Report. If a Chained Report is intended to be scheduled, use the Assign Value option to specify values.

Chained Report options

The Options page contains the following options:

Default Export Type

Select the file type to which the Chained Report will export by default. Available types are limited by report export restrictions, and by the Allowed Export Types option. HTML is not supported.

Default

Uses the environment's default export type.

Allowed Export Types

Select which file types the Chained Report is allowed to export to. You must select at least one type.

No Data Qualified Action

If a report has no data, choose what to display:

Show Placeholder

Show the report without any data in it.

Skip Report

Show nothing, and move on to the next report.

Collate Reports on

Select whether to collate the Chained Report, and which data field to use. This option is disabled if the reports have no common data fields. See Collating reports.

Page break after each report

Select whether to start a new page after every report. This only affects PDF and RTF files.

Collating reports

Collation breaks up the reports in the Chained Report by a specified sort field, and then groups together the reports by each instance of the sort. Essentially, this turns a sort field into a common grouping for the reports, working in much the same way a group section would. This can allow you to use individual reports as pieces in a composite report.

Order of reports in a collated Chained Report

Report 1, filtered by group 1

Report 2, filtered by group 1

Report 1, filtered by group 2

Report 2, filtered by group 2

and so on.

Some common uses for Chained Report collation include:

  • Grouping disparate reports by order number, or employee number, or something else in common

  • Combining mostly identical reports, only with different filters or joins, into a composite report

  • Making a composite report with multiples of a feature which normal reports can only have one of, such as a detail section

Caution
Collation is recommended for advanced users only.


Scheduler

The scheduler allows you to run reports regularly at specified times. This can be useful for reports which are based on periods of time, such as weekly sales reports or quarterly earnings reports.

Making a schedule

Schedules are created and edited with the Schedule Report Wizard, which is a tool designed to streamline schedule creation.

To schedule a report:

  1. Click the Menu [image] icon and select [image] Schedule Report.

  2. Enter a Name for the schedule.

  3. Choose which file type to save the report as.

  4. Optional: For PDF or Excel, enter a Password to secure the report.

  5. Select a date and time for the schedule, and choose how often it should run:

    1. In the Schedule Time field, enter a time for when the schedule should run.

    2. Optional: To run the schedule as soon as it is created, select the Execute Immediately check box. This schedule cannot be given a recurrence pattern. Go to step 6.

    3. Optional: To run the schedule multiple times per day, select the Repeat Every check box and enter a time for how often it should repeat.

    4. Optional: To run the schedule on more than one day, choose a Recurrence Pattern from the available options:

      Daily

      The schedule will repeat every day, every weekday, or every number of days. Enter a number of days for how often the schedule should repeat, or select Every weekday to run it every Mon, Tues, Wed, Thurs, and Fri.

      Weekly

      The schedule will repeat every week, or every number of weeks, on one or more days. Enter a number of weeks for how often the schedule should repeat, and select one or more days when it should run.

      Monthly

      The schedule will repeat every month, or every number of months, on a certain day. Enter a day for when the schedule should run, and a number of months for how often it should repeat.

      Yearly

      The schedule will repeat every year on a certain day. Enter a day for when the schedule should run.

    5. Optional: If you have entered a recurrence pattern, enter a date for when the schedule should Start.

  6. Optional: To add filters to the report click the Filters tab. If the report already has filters you can set their values here. See Filters for instructions.

    Tip
    Chained Reports do not support schedule filters.

  7. Optional: To email this schedule to addresses from a database, see Emailing personalized reports.

  8. Click the Recipients tab. To send the report to a list of email addresses, select the Email Reports check box and fill out the following fields:

    1. Enter the recipients' email addresses in the To, Cc, and Bcc fields.

    2. Optional: Enter an email subject in the Subject field.

    3. Optional: Enter an email message in the large text field.

    If you do not want to email the report, clear the Email Reports check box. The report will be saved to disk instead. You may have to ask your administrator for the file location.

  9. Optional: If this is a batch schedule, an Attach Report Output to Email check box is available. Clear this check box if you want to use this report to send alerts, and you do not want to email the report itself.

  10. Click Finish to save the schedule.

Schedules can be edited after they are created. See Viewing schedules for more information.

Viewing schedules

To see the schedules that have been created click the Schedule Manager [image] icon. Each schedule is on a row with its most recent and forthcoming run times, and a status indicating its condition:

Ready

The schedule will run on its next run time.

Running

The schedule is currently running.

Transmitting

The scheduled event has finished running, and the report is being sent to the user.

Completed

The schedule has completed its final event, and will not run again. It will be removed from the list when the cache is flushed.

Deleted

The schedule has been deleted, and will be removed from the list when the cache is flushed.

Abended

The last run failed due to an error. The schedule will not run again.

User Aborted

The schedule is running, but it was requested to be canceled. It will be marked as Deleted.

Scheduled events which have passed are copied to an archive row, so that there is a log of previous run times. Such events are labeled (archive). Deleting these rows will not affect the main schedule.

Schedules which are associated with a cached report are labeled (cache). See Execution Caching for more information.

Completed and canceled schedules and archives may be periodically removed from the list. Click [image] Flush to remove them immediately.

Managing schedules

To edit an existing schedule click the Edit [image] icon. Changes to the recurrence pattern will affect all forthcoming run times. You can reuse or reset deleted or completed schedules by editing them. This will set their status to Ready.

To delete a schedule click the Delete [image] icon. This sets its status to Deleted. If the schedule is already marked as Deleted, this removes the schedule from the list.

Emailing personalized reports

Batch scheduling allows you to send personalized versions of reports to a list of email addresses. The addresses must be defined in a data category, the unique key of which is used as a blanket filter for the report. Each recipient receives a customized version, filtered by their unique key.

For example, if you wanted to send out a customized sales review, or a pre-filled tax form, or even a set of customized forms for each employee, batch scheduling allows you use one report for every person. This also has the benefit of only needing to run the report once. So you are not making repeated calls to the database, which could be slow and system-intensive.

WARNING
Batch emailing should only be used as instructed by your administrator. If you are unsure about how to proceed, do not create a batch schedule. Ask your administrator for assistance.

Batch scheduling requires you to have access to a data category with a column of email addresses, that must be added in the report Categories menu. The category does not have to be in the body of the report.

To make a batch schedule:

  1. In the Schedule Report Wizard, click the Batch tab.

  2. Select the Run as Batch Report check box.

  3. Optional: In the To and Cc fields, enter addresses to send a summary email for each completed execution.

  4. From the Batch Email Field list, select the data field containing the email addresses for this schedule.

  5. Optional: Click the Recipients tab to enter a subject and message for the batch email.

    You can reference data values from the email address data row in the message body. Type the parameter @batch_column@, where column is the name of a data field in the address category. The value of the field replaces the parameter text in the message output.

    Tip
    The To, Cc, and Bcc fields are unavailable when using Batch.


Troubleshooting

FAQs

Why is a feature or button missing?

This feature is unavailable in your environment. Contact your administrator for more information.

Why are there so many tabs with the same name?

Each time you run a report, it opens a new instance of the Report Viewer. It is safe to close the tabs you are not using.

Why can't I edit a report?

This report is locked, or it belongs to someone else. Make a duplicate instead.

Why can't I run a report?

The Report Viewer is disabled. From the General Options menu, see if you can enable it for the report. If this does not work, then you have to export the report instead.

I downloaded a report, but I could not view or run the file. Why?

If you clicked Download, then you downloaded the report "definition". You cannot view or run this file on your desktop. Export the report instead.

Why are some data fields not in the Sorts or Filters menu?

These data fields are unsortable or unfilterable. Try to find a substitute field.

Why are there so many repeated or blank rows?

There is a one-to-many join between two or more categories in the report. There are multiple rows joined to each data field, but you will not see the data in those rows if you do not have a field from that category on the report. See Suppressing duplicates for more information.

Why do I have a bunch of junk after my data?

The report has multiple one-to-many relationships, and there is a sort on a field without associated data for a number of rows. The report does not know how to sort these rows, so it puts them all at the bottom. You can suppress these rows by setting Must constraints in the Joins menu.

What are #E0FFE0 and similar patterns?

The six letter and number patterns that start with a pound sign (#) are called color codes. This code is one of the ways that computers represent different colors. For example, white is #FFFFFF, black is #000000, teal is #008080, and so on. Wherever you see a color code or color field, there is a picker that lets you visually select a color from a palette - this way you don't have to worry about the codes themselves. However, matching codes is a good way to match colors in different reports or in different parts of a report.

Errors

An error has occurred. Please contact your administrator.

This message shows when the application is unable to determine the cause of an error. Save your work, then try to replicate the circumstances that led to the error message. Then let your administrator know what happened.

No Data Qualified for this Report

The report was not able to return any data. Either there are too many conflicting filters, or the chosen fields have no data. If you still want to see the report with no data, open the General Options menu, then from the No Data Qualify Display Mode list select Show Report.

Session has timed out; browser page will need to be reloaded or browser restarted

There was an error with the web server or with your internet connection. You may have to refresh the page, or log out and log in again.

Report has Abended

The report is trying to use an invalid data cache. To refresh the cache, edit the report, then click the refresh [image] icon.

This report contains errors. Please review the errors, and click OK to continue editing, and Cancel to close.

There are problems with this report. The file may have been corrupted, or the administrative settings may have changed unexpectedly. Contact your administrator, then try to recreate the report.

There are errors with this report, which prevents it from being executed or scheduled. Please edit report to view and fix errors.

The Chained Report is missing component reports. They may have been renamed, moved, or deleted. Try to locate the missing reports, then re-add them to the Chained Report.

Invalid template bookmark: Name; paragraphs, tables, and other Word objects cannot be contained within bookmark content that is mapped to a data field. Please see user guide for information on Word document templates.

You are trying to use two or more different Word based templates in the same Chained Report, which is not supported.

ERROR: / #VALUE!

There are problems with the formula in this cell. Check for errors such as missing parentheses, improper value types, or missing arguments. The Formula Editor is helpful for determining formula errors.