Navigating the Application

This application consists of two sections. On the left is the Main Menu and on the right are Tabs. The Main Menu displays the available reports, folders, and buttons. Tabs can contain the New Report Wizard, report outputs, design windows, or help pages.

Main Menu and Tabs

 

Main Menu

Through the Main Menu, you can:

Click the splitter icon to hide the Main Menu. This is located in the top left corner of the application's interface between the Main Menu and the Tabs.

 

The Main Menu can be broken down into two components: the Report Tree and the Report Descriptions Window.

 

 

Report Tree

The Report Tree contains the individual user's view of their applicable Folders/Sub-Folders and the Reports contained within them. Folders that are marked as "Read Only"() cannot be modified in any way (as designated by the administrator), with the Reports and Sub-Folders within inheriting the same permissions (unless otherwise specified).

A user can view a list of a folder's contents by double-clicking the folder name or by left-clicking the dropdown arrow next to the folder name. Right-clicking the folder name will bring bring up an options menu.

NOTE. Right-Click options are location- and item-specific.

Right-Clicking on Folders

If a user right-clicks on a non-"Read Only"() folder within the Report Tree, a menu with the following options appears:

NOTE. A folder and all of its sub-folders must be empty in order to delete the folder.

 

Right-Clicking on Reports

If a user right-clicks on a report, a prompt with the following options appears:

 

NOTE. If one of these export types is unavailable, it may have been disabled by your administrator.

NOTE. If neither 'Schedule Report' or 'Email Report' show up as options, Report Scheduling may not be enabled. For more information, please see Scheduling a Report.
NOTE. If a user renames a Report that is used within a Dashboard, Chained Report, or a Scheduled Report, an error message will appear when attempting to Execute or otherwise modify the aforementioned Report types

 

Report Descriptions Window

This window displays the description of the selected report. If the report was never given a description, this window remains blank when the report is selected in the Report Tree. For more info on Report Descriptions, please see Report Types.

 

 

Tabs

Tabs make up the application's main interface. This is where users build, design, and view reports as well as access dashboards and help pages. Tabs can be closed by clicking the () to the right of the tab name.

 

Tabs can be rearranged by clicking and dragging them left or right. 

 

Right-clicking on a tab displays additional Tab options, such as closing the tab or closing all other tabs.

 

Toolbar

Once a report has been created and opened in the report designer, the user may interact with it using the Toolbar.

The Toolbar enables users to:

 

User Preferences

In the User Preferences () window, the Startup Reports tab allows the user to manage which reports run upon opening the application. The User Reports tab allows users to manage reports with user adjustments saved on top of them. User Preferences are stored through cookies, server events, or an external interface per the Administrator's settings.

 

Help

Clicking on the custom help tool () walks the user through specific portions of the application. This context-sensitive help menu is indexed and completely searchable, granting the user easy access to all information.

 

To learn more about getting started, continue with Report Types.

Report Types

There are six different report types available in the reporting application. Four are standalone types, and two are composite types which combine reports together. By default, ExpressViews, Advanced Reports, and Dashboards are enabled in the end-user interface.

ExpressViews

ExpressViews are simple reports which comprise vertically expanding data records and groups, and an optional visualization.

ExpressView_2x.png

Built with an intuitive drag-and-drop interface, ExpressViews are intended to be a way to get started quickly and begin viewing data with as little friction as possible. There are no separate designer and viewer; instead, when executed, data populates the report inline. ExpressViews can be converted to Advanced Reports in order to gain the additional capabilities of that format.

ExpressViews can be scheduled, added to Chained Reports and Dashboards, embedded into web pages, and exported to multiple formats.

Visit the ExpressViews documentation.

Advanced Reports

Advanced Reports are the flagship report type of the application.

AdvancedReport_2x.png

The most powerful reporting tools are available with Advanced Reports, including geographic maps, crosstabs, repeating groups, complex join, filter, and sort logic, linked child reports, and more. Advanced Reports are made using an Excel-like grid-based interface. The Advanced Report Designer allows users to build reports without requiring knowledge of SQL or the underlying data model. Advanced Reports can be executed to a browser-based Report Viewer interface, which allows for interactivity, additional changes to be made without re-running the report, and for saving user-specific customizations.

Advanced Reports can be scheduled, added to Chained Reports and Dashboards, embedded into web pages, used to populate document templates, and exported to multiple formats.

Visit the Advanced Reports documentation.

Crosstab Reports

Crosstab Reports are a special type of Advanced Report.

CrossTabReport_2x.png

Also known as pivot tables, Crosstab Reports display and summarize data in a way that expands dynamically both vertically and horizontally.

Visit the Crosstab Reports documentation.

Express Reports

Express Reports are a simpler version of Advanced Reports.

ExpressReport_2x.png

Express Reports are built using a "Wizard" interface instead of a grid. This is meant to streamline the report design process. Most of the features of Express Reports are available in ExpressViews, which have a more modern interface.

Visit the Express Reports documentation.

Dashboards

Dashboards combine one or more reports onto a design canvas, providing a way to create a personalized arrangement and display of related reports.

Dashboard_2x.png

All standalone report types can be added to a dashboard, and visualizations can be created directly on the canvas without needing to make a separate report. Dashboards can also have text, borders, images, visual filters, and embedded web pages.

Dashboards run in the browser, can have interactivity such as dynamic filtering and drilldowns, and can refresh on a timed interval.

Visit the Dashboards documentation.

Chained Reports

Chained Reports combine multiple reports into a single multi-page document.

ChainedReport_2x.png

This can simplify running and distributing a group of related reports at once. They also support collating reports along a common sort field, to create a super-grouping of common report data.

Chained Reports can be scheduled and exported to multiple formats.

Visit the Chained Reports documentation.

Building Your First Report

This article will walk users through the New Report Wizard and demonstrate how to create a new report.

NOTE. This article will focus on building an Advanced Report. For information on the other types of reports, see Report Types.

The New Report Wizard will come up. The New Report Wizard has five sub tabs. The Name and Categories tabs must be completed while the other tabs are optional.

Name Tab

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

The report name can be up to 255 characters long. Avoid special characters such as ? : / \ * “ < >.

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

NOTE. You cannot create a report inside a folder that is read-only ().

 

The Categories Tab

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

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

Data Field – A Data Field is a single attribute within a category. E.g. Students.ID is the numeric value that identifies a specific student.

 

NOTE. When one Data Category is added, other Data Categories that are not joined to it become unavailable by default.

For this report, we've selected Categories and Products.

NOTE. For each category selected, a user can Suppress Duplicates within the data by ticking the check box that appears next to the category name. This will suppress repeated items in the given category for the final report.


The Sorts Tab

In the Sorts tab, specify which Data Fields will be used to determine the order of data on the report.

 

For this report we have Sorted on Categories.CategoryName in descending order.

NOTE. Sorts are not mandatory in order to create a report. Sorts allow for more complex organization of a report but do not bar the Report Wizard from continuing if left blank.


The Filters Tab

In the Filters Tab, create statements that will be used to filter the data when you run a report.

 

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

For this report, an Equal To filter on Category Name has been created in order to limit the data on the final report. 

NOTE. Like Sorts, Filters add complexity to a report but, but their completion is not mandatory.

IMPORTANT. If a filter is chosen, the above fields must be completed or the report will not execute.

The Layout Tab

In the Layout Tab, select the Data Fields that will appear on the report. For each Data Field chosen, the report will automatically create a column header and place the Data Field in the detail section. Additionally, subtotals, grand totals, and a page header/footer can be created.

 

Display Data

Using the ‘Summarize By’ box, you can display subtotals, grand totals, or headers for the values of a Data Field.

Subtotals and Grand Totals

 

Summary Functions:

Data Headers

A checkbox will appear in the Summarize By box for each Data Category in the Sorts tab. To display a header for each value of a Data Field, click on the associated Data Category in the Summarize By box. Click the Data Category name next to the checkbox, and the will appear.

For this report, the Data Fields Products.ProductName, Products.ProductID,Products.UnitPrice, and Products.QuantityPerUnit have been selected. 

 

NOTE. For information on the Toolbar and all its features, see Maneuvering the Toolbar
For ease of viewing, some light styling has been added. (The data present is the same as it would have been without the styling.) 

 

Grouping Basics

Understanding Groups

Groups break up a data set into sections of related information, allowing for aggregation and/or the removal of repeated values from the tabular detail. We see grouping in lots of settings, but restaurant menus provide a useful example. We never see menus like this: 

 

Or like this:

 

In the above example, the menu is stored by dish type, but there are repeated values in the "Type" column, which makes the menu difficult to read at a glance. For this reason, menus typically group on dish type:

 

Grouping works a bit differently in the application, but its function is the same. In either case, the first step in creating a group is sorting on the appropriate data field.

Creating Sorts

Users may either build sorts from the report Wizard or from the report Designer. 

The Sorts Tab is the third tab in the report Wizard. To create a sort, drag and drop a data field into the sorts pane. Select "Ascending" or "Descending" from the Sort Order drop-down menu. Add as many sorts as you like, and change sort priority by using the up () and down () arrows. 

Once in the report Designer, users may adjust their Sorts from the settings menu (). 

 

 

The Designer Sorts menu functions much the same as the Wizard Sorts menu does.

 

Once you've sorted on a field, you're ready to group on that field. 

NOTE. Nested groups should reflect the sort order. The outermost group header should be the highest priority sort, and the footer order should be reversed. Find more information on this Understanding Header/Footer Grouping

 

Sorting By Formula

To sort and group by information that may not be contained within an individual data field, you can use Formulas. See Formulas for additional help.

Pressing the  button or the Formula Editor () button opens the Formula Editor window:

For example, say I had a data field containing a full date and time, and I wanted to analyze my sales by each month of the year over a multi-year period.

I could use the formula Month({Orders.OrderDate}) to return only the Month component of each date. Then I could sort my sales by Month.

Adding Group Sections

In order to add the group sections, click anywhere in the sections tab in the report designer. From here, select Add Section and then add Group Header

In the Group Header Menu, use the drop down to select the proper sort field. For this report, Categories.Category Name has been selected. 

 

NOTE. Groups can be sorted by formula instead of a Category or Field by adding a formula to the report Sorts, then selecting it in the group dropdown menu.

After adding the Group Header, repeat the same steps above to create a group footer by selecting Group Footer from the menu. 

Group Header and Footer Content

Headers and Footers are designed to display content that will appear once per unique value in the group. Group Headers appear above the group detail, and Group Footers appear below the group detail. Unlike Group Headers, Group Footers may be used to perform aggregate functions on the group detail.  

In this example, there is a group header on Categories.CategoryName, and there are three rows in that header. The first row is to create space between groups. The second contains the Categories.CategoryName field, which will repeat once for each unique Category Name in the Categories object. The third contains column titles for the details section. They will also appear once for each unique Category Name in the Categories object. 

 

In the footer section, we are taking the average of the products' unit prices in each category. This function, like the values in the Group Header, will appear once for each Category Name in the Categories data object.

NOTE. Because headers are not designed for content that will produce many different results for each group, it is best to select content that will only change with each group. For example, if OrderDetails.Quantity is placed in a group header, it will only display the first record for each group break. Additionally, placing this field in a group footer will display only the last record in the group.

Upon execution, the report now shows breaks for each food category name and displays aggregate information for the unit prices in each category.

 

The next step in understanding the basics of report building is to gain an understanding of Formulas. See Formula Basics next.

Reprinting Group Headers

Beginning in version 2018.2, you have the option to reprint Group Header rows at the top of the page if the detail section of any given group spans multiple pages. This option is only available on Group and Repeating Group Header sections. To reprint a Group Header row when its detail section continues onto another page, click the row number in the Report Designer and select Repeat Row. If the Header has been set to repeat, two blue lines will display next to the row number.

repritnheader.png

 

Formula Basics

Formulas allow users to create complex calculations, parse strings, and insert images within the application. Formulas can be comprised of functions, parameters, strings, data fields, and cell references. All formulas begin with an equal sign (=).

In the Report Designer, users can either key their desired formulas directly into a cell or enter them with the assistance of the built-in Formula Editor.

NOTE. This application comes with a standard set of functions, but the number of available functions may differ based on the environment as set by the Administrator.

 

Adding Mathematical Calculations

Totals for each data field are not automatically calculated in an Advanced Report, so we must add calculations to the report in order to display totals. A great way to do this is through the use of footers, which are designed to aggregate the data contained in the section above them. Group Footers allow the totaling or counting of data contained in the group, and Report Footers allow the totaling of the full contents of a report.

In the example below, there is a group footer on Products.ProductName, and the Detail section of the report has been suppressed. Suppressed rows do not display on the executed report. The end goal is to have this report display one row for each confection product, each row containing the product name, total order quantity, unit price, and revenue generated by each product, with a grand revenue total at the end.

 

Looking more closely at the footer, we can expect the cells to display as follows:

If our goal is to calculate the revenue generated by each confection product, the first step would be to calculate the total quantity of orders for each product in cell B5. 

 

Entering a Formula

To enter a formula into B5, we click on the cell and either enter =aggsum({Order Details.Quantity}) or select AggSum and the data field from the Formula Editor. To access the Formula Editor, select the cell and press the formula editor () button in the toolbar.

 

Now we can add a column to the right-hand edge of the table and calculate the product revenue in D5.

 

Calculating with Cell References

Revenue is the product of all order quantities (B5) and unit price (C5). Instead of completely rewriting the aggsum formula we just created in B5, we can use a cell reference to quickly and easily allow this inclusion.

Cell references are formulas, so they will have to start with an equal sign (=). To reference a cell, enclose the coordinates in square brackets ([ ]). We'll enter =[B5]*[C5] in D5 to calculate the revenue for each product.

 

Calculating in the Report Footer

To calculate a grand total of the Revenue column, we will add a new group to the report, this time a Report Footer. The Report Footer aggregates values contained in the whole report, as opposed to a particular group within it. Again, we will use cell references for simplicity.

 

 

Upon execution, the above report displays the following:

 

NOTE. Instead of using the Formula Editor, it is possible to aggregate using the AutoSum button () on the toolbar. Just select the target cell, enter an equal sign followed by the value(s) you wish to aggregate, and click the AutoSum button. (In the above example, the grand total formula would read =[D5] with the AutoSum button depressed.)

Concatenating Strings

Concatenation is the act of combining multiple fields or strings into a single string. For more on concatenation see the full list of formulas and descriptions.

In this report, we can use concatenation to create a label for our revenue grand total and specify what food category it pertains to.

Concatenation can be done by using an ampersand (&) between each string or by using the concatenate function in the Formula Editor.

 

In the example below, the concatenate function has been entered into the cell manually. Note the ampersands. 

 

Note: To add space between elements in your concatenation, insert spaces at the beginnings and/or ends of your strings.

With a little added formatting, the executed report now looks like this. The "Confections" has replaced Categories.CategoryName in our concatenation formula.

Now that the report has become more intricate, it may help to have a better way to see the data presented. This can best be done through visualizations. Click here for more information. 

Adding Visualizations to a Report

About This Guide

This guide is designed to walk users through adding a visualization to the example report. For this example, we'll create a chart visualization, keeping in mind that GeoCharts, Google Maps and gauges are other possible visualization types.

More info on GeoCharts, Google Maps, and gauges can be found in their respective articles.

 

Adding a Chart

To add a chart, first select a cell in in the Report Footer and press the Chart Button () in the toolbar above the Design Grid. 

 

Or, right-click a cell in the Report Footer, hover over 'Insert' in the drop-down menu, and select 'Chart' from the second drop-down menu.

 

NOTE. Charts rely on aggregating data and are therefore best placed in a group or report footer.

 

The Chart Wizard

Appearance Tab

 

The Chart Wizard opens on the Type tab, where users may select the type of chart they wish to create. The charts are broken up into four groups: Line; Bar and Column; Pie and Other Single-Series; and Scatter and Bubble charts.

For this report, the desire is to show a chart that allows direct visual comparison of total revenue generated by each product. This is best accomplished with a bar or column chart.

 

Data Tab

The Data Tab is where data is refined for the selected chart and where the interaction of the data is determined. A data value and a data label for the chart must be set before proceeding.

For this chart, set the X-Axis Labels to Products.ProductName, and set the Series Values to the formula for product revenue, =[B5]*[C5], using the dropdown menus. Under Other Options, we will set the data values to sort in ascending order for quick comparison.

 

NOTE. Data Values must be numeric values.

 

Appearance Tab

In this tab, we can customize the chart's appearance by applying a theme, labels, number formatting, benchmark lines, and other features. 

 

NOTE. The values set in the Appearance Tab are optional.

 

Size and Preview Tab

After the chart has been formatted, we can either press the Finish button to dismiss the chart wizard or move on to the Size and Preview Tab. The preview displays dummy data formatted according to the user's specifications and gives users the option to resize the chart.

 

Reading The Chart

Click Finish and execute the report to HTML. Below is the visualization we've just constructed:

 

 

After execution, it is clear that Tarte au Sucre has generated the most revenue and that Chocolade has generated the least.

 

Interactive HTML

In the HTML viewer, users are able to make adjustments to the chart without having to re-execute the report. Right clicking on the chart will bring up a series of menus.

 

These menus include:

Now that the report is near complete, it is possible that users may need to schedule this report for use. For information on this process, continue to Scheduling a Report.  

Scheduling Reports

The scheduler allows you to run reports and dashboards regularly at specified times. This can be useful for reports and dashboards 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 screen.report_tree_menu.png icon and selectschedulereport.pngSchedule 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.

    Note: In versions prior to v2018.2, Chained Reports do not support scheduled 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 Managerschedulemanager.pngicon. 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 Flush.png Flush to remove them immediately.

Managing schedules

To edit an existing schedule click the Edit edit.pngicon. 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 DeleteItem.png 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. Fields can contain a list of email addresses, separated by semicolons (;), as of v2017.3.8+.

  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 (in lowercase text). 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. Dashboards can not be scheduled as a Batch report.

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.

screen.expressview_demo.png

The ExpressView Designer

Making an ExpressView

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

To make an ExpressView:

  1. Click the New Report MainLeftPaneNewReport.png icon and select NewExpressViewItem.png ExpressView.

    Info. 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 Untitled.png icon to expand or collapse the Data pane.

  2. Expand a category by clicking the arrow OpenRows.png 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.

    screen.expressview_drag_field.png

    Dragging a field onto the Design pane

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

    Note. 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 Live Data to populate the ExpressView with data. You can now page through your data using the navigation icons PageHome.png PageUp.png PageDown.png PageEnd.png in the toolbar.

    AlertToolbarMedium.png 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:

  6. Click the Save ExpressViewSave.png 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 just basic reports. You can make groups, charts, calculations, and customize the look of the report. And it is all designed to be easy to use. The articles in this section will describe how to use these powerful features.

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 custom.RadialButtonBlue.png icon on the top left of the column or group.

  screen.RadialClosed.png screen.RadialOpen.png screen.RadialSelect.png

Using the radial menu

Each radial menu has four options, arranged in a circle around the center. When you are prompted to select a radial menu option, you will be asked to "use custom.RadialButtonBlue.png radial>direction". Direction is one of the four cardinal directions corresponding with one of the four options: left, right, up, down.

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

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

Interactive Editing

With the introduction of interactive editing to ExpressViews in v2018.2, you can customize your report by simply right-clicking on the desired section while in design or live mode.

rightclickhtmlEV.png

Right-click options include all of the capabilities of the radial menu, conditional filters, and formatting options such as text alignment, font type, and font color.

ExpressView: 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 data

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 FormatMenu.png icon to open the Formatting page.

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

    screen.expressview_format_highlight.png

    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. Continue to step 5.

    Date

    Format the data as a date, time, or date and time. Skip to step 6.

    screen.expressview_date_format_menu.png

    Choosing a date/time format

    Text

    Do not apply any formatting to the data, and show it exactly as it appears in the database
  5. Optional: If you selected Number you can choose how the number displays. You can customize the following options:
    1. In the Decimal Places field, enter a number for how many decimal places to display. In the field to the right, enter a symbol to use as the decimal mark.
    2. 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.
    3. To show a symbol before the number, indicating that this is currency, select Use Currency Symbol. Then, in the field to the right, enter the symbol to show.
    4. To show a percent sign after the number, indicating that this is a percentage, select Append Percent Sign.
    5. To show no value if the number is 0, select Blank When Zero.
    6. To show the negative symbol in front of negative numbers, select Show Negative Symbol.
    7. To show parentheses around negative numbers, select Show Parenthesis.
    8. To show negative numbers in a different color, select Color, then enter a hex value or use the color picker to choose a color.
  6. Optional: If you selected Date, you can choose which date and time components to display, and how to show them.

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.

Reordering data rows

Sorting allows you to set the order that the data rows appear in each section. Click the Sorts SortsMenu.png 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:

screen.expressview_reorder_sort.png

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 SortDescending.png for ascending, down SortAscending.png for descending.

To change a field's sort direction, either:

Narrowing your data

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 iteration. These are Top N filters.

Click the Filters FilterMenu.png 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:

  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:

  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.

screen.expressview_drag_field_to_filter.png screen.expressview_filter_select.png

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:

  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 iteration of a group:

    1. Click AddBtn.png Add Group.

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

screen.expressview_topn.png

Adding a Top/Bottom filter

ExpressView: Formula columns

Note: Formula columns are available beginning with version 2018.1.

Formulas can be used to create custom data columns in ExpressViews. Formulas are calculated once per each row, based on the other data values in that row. For each row calculation, a formula value is returned for that row, which populate a new column of data. You can use these columns just like any others - format the data, add to a visualization, or group based on formula columns. They are treated the same as data columns.

To add a formula column to an ExpressView, click the Choose Data MainLeftPaneDataFieldsSelected.png icon to open the Data Pane, then click + Add Formula. A blank column will be added to the ExpressView, and the Formula Builder will open.

In the right pane, the Formula tab of the Selected Cell SelectedCellMenu.png page shows the available built-in functions and parameters. Hover over a function to see its description and an example of how to use it. To add a function or parameter to the formula, drag it from the Formula page to the Formula Builder window, type its name into the Formula Builder, or double-click the name of the function while the formula editor dialog is open. You can use the Search field in the to filter the functions by name.

formulaeditor.png

Formula editor dialog

To add a data field to the formula, drag it from the Data Pane to the Formula Builder window, type its name into the Formula Builder, or double-click the field name while the formula editor dialog is open.

Note: Double-clicking the field names will place the fields wherever the cursor is in the formula editor at that time. Double-clicking enabled in v2018.2 and later.

Tip
To use a data field, function, or parameter as a function argument, drag it to the argument placeholder until the placeholder turns blue. Or click the placeholder and type the name of the desired argument.

screen.expressview_drag_field_to_formula.png

Dragging a field to a function argument

Typing in the Formula Builder shows a list of functions, parameters, and data fields that match the text. Click on an item, or use the up and down arrow keys, to highlight an item and see its description. To add the selected item to the formula, press the Enter key.

When you are finished, click the Apply Changes custom.FormulaCheckmark.png icon to save the formula.

To edit an existing formula column, click Selected Cell SelectedCellMenu.png, open the Formula tab, then click the column to reopen the Formula Builder.

See Formulas for more details on building formulas.

ExpressView: Customizing Appearance

ExpressViews can be styled in a variety of ways:

formattingpane.png

styleeditorpanel.png

rightclickhtmlEV.png

 

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

screen.expressview_theme_select.png

Selecting a premade theme

Styling data cells

Cells, columns, group headers, and group footers can be styled using the Report Formatting and Style pane, the Selected Section pane, or by right-clicking sections of the report. Each of the three options is used for different purposes:

selectedcelldateformat.png

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:
  3. Enter a hex value or use the color picker to set each color.

screen.expressview_row_colors.png

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:
  3. Enter a hex value or use the color picker to set each color.

screen.expressview_group_colors.png

Choosing radial menu colors

ExpressView: Making 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.

screen.expressview_viz.png

An ExpressView Visualization

To add a chart to an ExpressView:

  1. Click the Show Visualization ShowViz.png 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 VizMenu.png icon, then click the Data tab. There are two fields: Labels and Values.

Labels

The Labels field is a group of data values, each iteration of the group represented as 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:

screen.expressview_drag_field_to_chart_label.png screen.expressview_drag_field_to_data_label.png

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 iteration of 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:

screen.expressview_drag_field_to_chart_value.png screen.expressview_drag_field_to_data_value.png

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

Interactive Editing of ExpressView Visualizations

You can invert the data, change the Type, Theme, Legend Location, or Sort of ExpressView visualizations by simply right-clicking with the introduction of interactive HTML to ExpressViews in 2018.2.

rightclickchartchange.png

You can learn more about interacting with reports here and more about visualization Types here.

 

 

 

ExpressView: Grouping and summarizing data

Grouping a column of data breaks up the rows into sections which share a common trait. Each iteration of a group is a unique row in a data column. Grouping data allows you to easily identify rows with common factors. You can perform calculations on groups, such as counting the rows, or adding up the data in each iteration.

custom.GroupDemo.png

Grouping data in an ExpressView

Making a group

To create a group from a column, use custom.RadialButtonBlue.png radial>up or right-click the column and select Group in the dropdown menu. This turns the data column into a group column, and organizes the other columns by each unique row in the group. Each row in the data column becomes a group section, or iteration, and the rows in other columns are grouped by the section which they are related. The group is also given a new color to distinguish it from the data rows. Remember to turn Live Data on to see your actual data and verify that this is the grouping you want.

To ungroup a column, use custom.RadialButtonBlue.png radial>left or right-click and select Ungroup from the dropdown menu. 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, simply add another group to an ExpressView which already has one. This creates another grouping inside the existing group. Additional levels of nesting can be made as needed.

Note: Right-click interactions added in v2018.2.

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.

custom.GroupNestWrong.png

Improper grouping of data fields

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 custom.RadialButtonBlue.png radial>up on the group column or right-click the group header and select Move Group Up One Level.

custom.GroupNestRight.png

Proper grouping of data fields

rightclickgroupingmenu.png

Dropdown menu 

Summarizing group data

Each iteration has a footer, which contains summary calculations, also known as aggregates, for each column. The ExpressView also has a report footer, which calculates the aggregate across all the groups. You can choose between several options for which calculation you want to appear in the footer 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 iteration. Only available for numeric fields.

Min

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

Max

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

Count

Counts the number of values in the iteration.

Distinct Count

Counts the number of distinct values in the iteration.

Avg

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

None

(v2017.3+) Show no summary calculation.

screen.expressview_choose_agg.png

Choosing an aggregate

Hiding data rows

If you only want to see the summary calculations, you can hide the data rows, either per-iteration, or for every group in the ExpressView. This does not remove the data or alter the summaries. It only hides the rows from view.

To hide the rows for one iteration or several iterations, click the group header for each iteration to toggle whether its rows are shown or hidden.

To hide or show all the rows in the ExpressView:

  1. Click the Expand/Collapse GroupStateMenu.png icon.
  2. Click GroupStateMenuCollapseAll.png Hide All Group Content to hide all the rows, or GroupStateMenuExpandAll.png Show All Group Content to show all the rows.

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

(v2017.3+) 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.

ExpressView: Data categories and fields

The data pane is organized into data categories, which you can expand to see their fields. Click the MainLeftPaneDataFieldsSelected.png Choose Data icon to expand or collapse the data pane.

screen.expressview_data_pane.png

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 AccordionDeleteItem.png 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:

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 custom.RadialButtonBlue.png radial>left to remove a data column.

ExpressView: Exporting to other types

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 SettingsMenu.png 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.

Note: Beginning in v2018.2, Advanced Reports converted from ExpressViews will retain reprinting of group headers by default if detail section spills to the next page.

evtoadvconvertreprintheaders.png

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

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:

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.

screen.reportviewer.png

The interactive Report Viewer

To run a report in the Report Viewer:

The Report Viewer opens in a new tab, indicated by the viewer TabInteractiveSelected.png 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 PageHome.png PageUp.png PageDown.png PageEnd.png. 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.

AlertToolbarMedium.png 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:

Report Viewer: 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!

screen.reportviewer_chart_drilldown.png screen.reportviewer_drilldown.png

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 OpenRows.png. 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

 screen.reportviewer_expanded_row.png screen.reportviewer_expanded_row.png

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.

screen.reportviewer_toggle_chart_series.gif

Toggling a chart series on or off

Drilling into maps

On a Google Map, use the mouse wheel or click the Zoom in custom.GoogleMapZoomIn.png and Zoom out custom.GoogleMapZoomOut.png 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.

Report Viewer: 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 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 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 decorationAlignment, Word wrap. Styling applies to all cells of the same type in the group; affected cells are outlined by a yellow band.

screen.reportviewer_cell_menu.png

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.

screen.reportviewer_chart_menu.png

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 DeleteItem.png Hide Column from the list.

screen.reportviewer_column_menu.png screen.reportviewer_resize_column.png

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 GrabVert.png 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 Add2.png icon to add a filter for that field. To remove a filter, click the Delete Item DeleteItem.png icon.

The order of sorted fields can be swapped between ascending SortAscendingButton.png and descending SortDescendingButton.png.

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 Filter.png icon. Conditional filters are shown in the sidebar.

screen.reportviewer_conditional_filter.png

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.

screen.reportwizard.png

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 articles detail how to use each page in the wizard.

NameCategoriesSortsFiltersLayoutOptions

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.

Report Wizard: 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 TreeReportLock.png 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.

Report Wizard: 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 Information.png 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.

custom.TableOfEmployees.png

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.

screen.reportwizard_drag_category.png

Dragging a category to the Category Name pane

Tip: As you add categories, unrelated categories will become unavailable.

SQL categories (advanced users)

Note: This feature is available in versions 2018.1 and above.

You may have the ability to define a custom data model for the report without needing to use the predefined data categories. For databases which support unique or unusual behaviors that are not supported in the main interface, you can use custom SQL to supplement or bypass the standard Categories, Sorts, Filters, and/or Joins. Only new reports, created with the Report Wizard, can have a custom SQL category. You cannot add a custom SQL category to an existing report.

Caution
Writing custom SQL requires knowledge of the underlying databases and their relevant SQL query language. It is only recommended for advanced users.

To add a custom SQL data category, click Add2.png Add SQL. From the Custom SQL Object window, add the following:

  1. Object Name - Unique name for the custom category. It cannot be the same as an existing category. It cannot contain white space or the following characters:

    [ ] { } . , @

  2. Data Source - Select the data source to retrieve the data from.

    Not every data source you can access may support custom SQL categories.

  3. Enter the full SQL statement in the code window. Note that this will be inserted into a subquery when it is sent to the database for processing.

    Optional: Parameters are system variables that contain different values depending on factors such as the person running the report. To include parameters in the SQL statement, select them from the Parameters list then click Add. Or enter the parameter name surrounded by At Signs (@).

    Tip
    A custom SQL category can only be the sole category on a report. A report cannot contain multiple custom SQL categories, or a mix of custom SQL and standard categories. Therefore, to include multiple tables on a report with custom SQL, you must retrieve multiple tables and join them in the SQL statement. If field names conflict, you can alias them in the SQL statement, or else the application will append a number to the end to preserve uniqueness.

    Click the Test Checkmark.png icon to check if the SQL is valid.

  4. When you have finished writing the SQL, click the Unique Key Fields list and select the unique keys for the category.

  5. Click Okay when done. If you have already sorted and filtered in the SQL statement, you can skip these menus.

Once added, you can edit the SQL category by clicking the SQL icon SQL.png next to its name in the Categories window.

Report Wizard: 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

custom.arrow_fore.png Asc  Lower values     Higher values  Desc custom.arrow_back.png

Date

custom.arrow_fore.png Asc  Past     Future Desc custom.arrow_back.png

Text

custom.arrow_fore.png Asc  A     Z Desc custom.arrow_back.png

custom.DepartmentsUnsorted.png custom.DepartmentsSorted.png

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.

screen.reportwizard_drag_sort_field.png

Dragging a field to the Sort By pane

Tip: Sorts are not applicable for Crosstab Reports.

Report Wizard: 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.

screen.reportwizard_drag_filter_field.png

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.

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

    Matching Values

    Not Matching Values

    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.

    screen.reportwizard_filter_value_menu.png

    Choosing a filter value

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

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

Report Wizard: 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.

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.

screen.reportdesigner.png

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.

screen.reportdesigner_dynamiccell.png  screen.reportdesigner_dynamicoutput.png

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. See Advanced Reports for all of the available features.

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 Information.png icon.

If the report has a custom SQL category then it cannot have any other categories. You can click the SQL icon SQL.png to edit the SQL statement. See SQL categories (advanced users) for more information.

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.

custom.TableOfEmployees.png

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.

screen.reportwizard_drag_category.png

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.

screen.reportdesigner_dynamiccell.png screen.reportdesigner_onetomanyoutput.png

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.

screen.reportdesigner_suppresscategory.png

Duplicate-suppressed category

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

screen.reportdesigner_dynamicoutput.png

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 DeleteItem.png 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 custom.arrow_fore.png Lower values     Higher values custom.arrow_back.png Desc

Date

Asc custom.arrow_fore.png Past     Future custom.arrow_back.png Desc

Text

Asc custom.arrow_fore.png A     Z custom.arrow_back.png Desc

custom.DepartmentsUnsorted.png custom.DepartmentsSorted.png

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.

screen.reportwizard_drag_sort_field.png

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.

custom.DepartmentsSorted.png custom.DepartmentsGrouped.png

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.</blockquote >

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 Add2.png Add Formula, then use the Formula Editor to make a composite field to sort on.

Filters

Sometimes you may only want to see a portion of the 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 Filters

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

Adding standard filters

In the Filters window, add data fields to filter.

screen.reportwizard_drag_filter_field.png

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 iconSettingsMenu__1_.pngto select whether to search for values that either Start With or Contain the typed text (v2017.3+) .

    screen.reportwizard_filter_value_menu.png

    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.

Formula Filters

As of v2018.2+, the application supports using formulas in the string of a filter, allowing for much more powerful filter statements.

As an example using Northwind data catagories, previously filter statements would be limited to statements such as:

{Orders.OrderDate} > 01/01/2015

Where here, only one object in the filter string is being compared to a value, in the following example filter string that makes use of data objects in a formula could be the following:

{OrderDetails.Quantity}*{OrderDetails.UnitPrice} > 300

Here, the formula {OrderDetails.Quantity}*{OrderDetails.UnitPrice}, which would represent the revenue of the order, is being compared using a filter condition to a numeric value.

As shown in this example, formula filters allow you to make filtering decisions not just based on data objects, but any quantity and value that can be created based on your data objects in a formula, including any possibilities enabled by the formula builder.

Advanced Report designer

In the Advanced Report designer, formula filters are available in the Filters window of the Report Options. Clicking the Add Formula  button in the lower left will open a Formula Editor window, which will allow for the creation of a formula to use within the filter.

Capture1.PNG

Report Filters window in Advanced Report designer with the Add Formula button

Any appropriate custom functions will also be available to use in the Formula Editor window for the formula filter.

ExpressView designer

In the ExpressView designer, adding a formula filter requires that the formula exist as a column on the report.

Note: Any objects needed for a formula in an ExpressView must also exist as their own column on the report as well.

Capture2.PNG

Add Formula button in the ExpressView designer

Once the formula is created as a column, select the Filters pane and drag the formula column over to the Filters pane to begin creating a filter on the formula. For more information about building formulas in an ExpressView, see the Support Site article on Formulas in ExpressViews.

Note: Certain function are available in the Formula Editor that are not compatible with the context of formula filters, such as CellValue(), FilterValue(), Hyperlink, LoadImage(), PageNumber().

 

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.

screen.reportwizard_filter_summary.png

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:

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

 

 

Group Min/Max Filters

When assigning filters to an Advanced Report from the report designer, users have the option of assigning Group Min/Max filters to the report. Group Min/Max filters will cause the report output to display detail containing either the highest or lowest values in a field for either one group, multiple groups, or an entire data set. (These operate differently from the Min and Max functions, which are used to manipulate specific data fields as part of formulas.)

This tool is especially useful if you are only interested in viewing the highest or lowest values--such as the most recent hire date or highest revenue figure--in a given set. Group Min/Max filters are compatible with standard filters, and there is no limit to the number of group filters you may define.

To access Group Min/Max filters, navigate to the filters menu from the Report Designer and click ‘Switch to Group(MIN/MAX) filters' in the upper right-hand corner of the menu.

 

 

 

Applying Group Min/Max Filters

We will explore the several ways of applying this type of filter using the below sample report.

 

With no Group Min/Max filtering, this report executes to the following:

 

Note. The following features are available only in v2016.2.0 and later builds. 

 

Ignoring Other Groupings

To apply the filter to one group only, select a group from the dropdown menu and check the 'Ignore other groupings on report' box.

 

To best utilize this option, it is important to understand the difference between an inner group and an outer group. In the report designer, the topmost group (in this case, Orders.EmployeeID) is the outermost group and has first priority. The second group (in this case, Orders.CustomerID) is within the first and has second priority. Each subsequent group is nested into the previous one and grouped after the others.

If applying a Group Min/Max filter to an outer group, then checking the 'Ignore other groupings' box has no effect on the report output because the outermost group takes precedence anyway. If applying a Group Min/Max filter to an inner group, however, the button takes effect.

In our example, both Buchanan and King have sold to customer BONAP. If we apply a maximum filter on order quantity for each Customer ID and leave the 'Ignore other groupings box' unchecked, the output shows the detail containing each customer's max quantity sale per employee.

 

Checking the 'Ignore other groupings' box, however, returns the customer's max quantity sale for the whole report, ignoring the grouping on employee.

 

Because BONAP's order of Spegesild from King is greater than its order of Pavolova from Buchanan, BONAP appears only once on the report. If BONAP had ordered the same quantity from both Buchanan and King, it would appear under both names, even with the 'Ignore other groupings' box checked.

 

Filtering the Entire Data Set

Selecting 'Entire Data Set' from the group dropdown menu will disable the 'Ignore other groupings' option and return the record(s) containing the single maximum value for the selected field in the entire report.

 

In our example, selecting this option displays only BLONP's order from Buchanan because its quantity is the highest in the set.

Top/Bottom Filters

Version 2017.1 allows you to add Top/Bottom filters, also known as Top N filters, to a report. Top N filters allow you to filter data to only the rows with the top or bottom values, for either data fields or data summaries, per group iteration.

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 or bottom values of a data set 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:

  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 iteration of a group:

    1. Click AddBtn.png Add Group.

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

Report Options

The following options are available in the General Options window:

General Options

Excel Options

Page Options

Report Viewer Options

The following options are available in the Report Viewer Options window:

General

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:

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.

Templates

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

screen.templatereport.pngscreen.templatepreview.pngscreen.templateout.png

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:

  2. From the Options.png Report Options Template.png Template window, select an existing template or click the Upload Template Browse.png icon and add a new one.

  3. For each template field, select either:

Templateeditallow.png

Editing of fields in exported PDF templates available in v2018.2+

  1. 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 Options.png Report Options > 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:

  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:

  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.

    screen.exceltemplatefile.png

    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.

custom.joins_basic_demo.png

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.

custom.joins_basic_row.png

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 Options.png Report OptionsAdvanced.png AdvancedJoin.png Joins window, select the Products data that does not have Orders data check box.

screen.join_type.png

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:

custom.joins_left_outer.png

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:

custom.1M_detail.png

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:

custom.1Mx2_detail.png

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:

custom.1Mx2_no_cartesian.png

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:

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.

custom.1Mx2_YZ.png

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:

screen.join_must.png

Setting Must constraints

Do one of the following:

custom.1Mx2_mustY.png  custom.1Mx2_mustZ.png  custom.1Mx2_mustBoth.png

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 Add2.png Add.

  3. Click Add2.png 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 DeleteItem.png icon next to the condition to delete.

To modify a join's fields:

  1. Click the Edit Edit.png icon next to the join to edit.

  2. Add, remove, or modify conditions.

  3. Click OK.

To remove a join, click the Delete DeleteItem.png icon next to the join to delete, then click OK.

To restore the default joins, click Refresh.png Recreate, then click OK.

Advanced Joins

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

Note: Advanced Joins cannot be applied across different data sources.

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:

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:

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.

Cell Formatting

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.

screen.number_format.png

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:

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.

screen.reportdesigner_cellformat_border.png

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.

screen.conditional_formula.png

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 Add2.png 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 Formula.png 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 Add2.png 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 MoveGridRowUp.png and Move Row Down MoveGridRowDown.png icons to reorder the precedence of the conditions.

screen.reportdesigner_cellformat_condition.png

A cell with multiple conditional formats

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.

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.

screen.sections_menu_full.png

The section menu

To add a section, hover over Add2.png 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 Edit.png Modify Section.

To change the order of sections, click MoveGridRowUp.png Move Section Up or MoveGridRowDown.png Move Section Down.

To delete a section, click DeleteItem.png Delete Section. This will delete every row in the section.

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

Static Sections

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() function to number the pages, and a report footer with summary information.

Page Header/Footer

custom.page_sections.png

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 PageBreak.png 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 PageNumber() function 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

custom.report_header.png   custom.report_footer.png

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

custom.detail_section.png

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 report 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

custom.group_sections.png   custom.group_sections.png

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.

screen.single_sort.png

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.

screen.group_menu.png

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

custom.repeating_groups.png   custom.repeating_groups.png

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.

custom.1M_detail.png

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.

custom.1Mx2_detail.png

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:

screen.repeating_group_ex.png

Organizing Y and Z into repeating groups for X

custom.1Mx2_repeating_group.png

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.  

Reprinting Group Headers

Beginning in version 2018.2, you have the option to reprint Group Header rows at the top of the page if the detail section of any given group spans multiple pages. This option is only available on Group and Repeating Group Header sections. To reprint a Group Header row when its detail section continues onto another page, click the row number in the Report Designer and select Repeat Row. If the Header has been set to repeat, two blue lines will display next to the row number.

repritnheader.png

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 Shading.png Section Shading.

  2. Click Add2.png 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 MoveGridRowUp.png and down MoveGridRowDown.png arrow icons to rearrange the order of the colors.

  5. Click OK.

screen.sectionmenu_shading.png  screen.section_shading.png  screen.section_shading_out.png

Adding two alternating background colors to a Detail section

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

Using the Toolbar

The toolbar contains the buttons and menus used to modify the report. Modifications can include aesthetic formatting, inserting formulas and images, linking reports, and much more.

The toolbar begins with a dropdown menu. This menu controls changes, such as renaming and filtering, that affect the entire report. All other buttons on the toolbar require that a cell (or cells) in the design grid be selected.

 

Saving Reports

The report can be saved by clicking the save button (). The report will also be saved anytime it is executed.

 

Undo/Redo

Any action on a report can be undone by click () or pressing CTRL + Z. Undone actions can be redone by clicking () or pressing CTRL + Y.

 

Font & Alignment Options

The text of each cell can be formatted using dropdown menus and buttons in the toolbar. A cell or multiple cells must be selected for these tools to be used.

 

Font

 

Color

 

Alignment

 

Formatting Cells

Cells can be formatted in the Cell Format Window. To open the window, click the format cell button (). The window has three tabs: Number, Border, and Conditional.

NOTE. Cell formatting can be copied using the Format Paintbrush. Select the format you want to copy, click the format paintbrush button (), then click the cell you want to apply the formatting to.

 

Number

The Number Tab allows you to set the format of numbers and dates.

 

Border

The Border Tab allows you to alter the width and color of the cell edges.

 

 

Conditional Formatting/Suppression

The Conditional Formatting Tab allows you to set or modify the format of a cell based on formula you create.

NOTE. The formula must evaluate to True or False. For conditional formatting, the Formula Editor will have an add Cell Value () button. This button adds the function CellValue() to the formula. This function returns the value of the cell that conditional format is being applied to.

 

NOTE. The formula is still calculated with respect to the section of the cell. For example, for a cell in a report footer, the formula {Order.Profit} > 1000 will return True if the last Order of the detail section profited more than 1,000. To make the condition see if the total profit was greater than 1,000 use the formula Sum({Order.Profit})>1000’.

 

 

AutoSum

To quickly get a total on a Data Field, place the field in a Report or Group Footer and click the AutoSum button ().  Alternatively, a sum can be created with the aggSum or Sum functions. See Formulas for more information.

NOTE. Do not use AutoSum on a cell with an aggregate formula such as aggSum.

 

 

Images

An image from your computer can be added to a cell using the Insert Image button (). This opens the Insert Image window. Select the image you would like to insert and click ().

 

Functions

Complex calculations can be done using Formulas A formula can be added to a cell by keying it in manually or using the Formula Editor. To open the Formula Editor click the Formula Editor Button ().

 

Suppress Duplicates

You can suppress duplicate values of a Data Object from being displayed. Select the cell and click the Suppress Duplicate button ().

Ex. The two reports below are identical, except the second image has suppressed duplicates for the customer column.

 

Design Grid

In the design grid, you can:

 

Sections

Sections dictate how the data appears in a report. There are five types of sections: page, report, details, group, and repeating group.

 

Page Header & Page Footer

The rows in the Page Header section appear at the top of every page of a report, and the rows in the Page Footer section appears at the bottom of every page of a report. Typically, the Page Header section is used to designate column headers for a report, and the Page Footer section is used to display the page number and/or confidentiality notices for a report. 

NOTE. Page Headers and Page Footers are not intended to perform calculations or display data fields. For this reason, a Page Header populated with a data field will only return the first line of data in that field; a Page Footer will return only the last line of data.

 

NOTE. If you are printing a report, remember that Excel output does not have pages. Page Headers will appear only once at the beginning of the report. Reports run via the Report Viewer will display Page Headers similarly unless ‘Simulate PDF’ is checked in the Options menu.

 

Report Header & Report Footer

The rows in the Report Header appear at the beginning of a report. Typically, these rows display the title of a report. The rows in the Report Footer appear at the end of a report. Typically, the Report Footer displays grand totals and summary information for the report.

 

Detail

The Detail section is the main section of most reports. When the report is executed, the Details Section creates a row for each element in the Data Categories. For example, if the Detail section contains the Data Field Orders.OrderId, the report will display each Order Id on a separate row.

Group Header & Group Footer

Group Header/Footer sections require a sort on a Data Field. The rows in a Group Header section will appear above the Detail section for each unique value of the sorted Data Field. Typically, Group Header sections are used to display data as labels. For example, a report may contain a Group Header on Orders.OrderDate and display Orders.OrderId in the Detail section. The output would display each date with orders that occurred on that date below them.

The rows in a Group Footer section will appear below the Detail section for each unique value of the sorted Data Field. Typically, Group Footer sections are used to calculate subtotals. For example, a report may contain a Group Footer on Orders.OrderDate which displays the number of orders made on each date.

NOTE. Group Header/Footer sections can also be set to display rows for each value of a formula instead of a Data Field. (Ex. The report may be sorted on the Data Field Orders.OrderDate, but the report should show subtotals for each month. A Group Footer on the formula =Month({Orders.OrderDate}) will display rows containing subtotals for each month.)

 

Repeating Groups

Repeating Groups require a sort on a Data Field. Repeating Groups have their own header, detail, and footer subsections. Repeating Groups should only be used when the data has multiple one-to-many relationships and each should be rendered completely before the other.

Ex. Each Professor can teach multiple classes and advise multiple students. For each professor you want to see all the classes they teach and then all the students they advise.

 

Using Sections

Sections can be added, deleted, modified, moved, and assigned shading.

 

Adding Sections

  1. Click anywhere in the Section Column.
  2. Hover your mouse over Add Section, then select the type of section you would like to add.

 

Deleting Sections

  1. In the Section Column, click on the section you want to delete.
  2. Click ‘Delete Section’.

 

Modify Sections (Group Header/Footers and Repeating Groups only)

  1. In the Section Column, click on the section you want to modify.
  2. Click Modify Section. This will bring up a Modify Group Section Menu.
  3. Select from the dropdown the desired Data Field for the group to use.
  4. Click OK.

 

Section Shading

  1. In the Section Column, click on the section that you want to Shade.
  2. Click Section Shading. This will bring up a menu.
  3. Click New to add a color to the shading.
  4. Click the color box to select a color or enter a hex value.
  5. Click OK.

 

Columns and Rows

Columns and rows of cells can be added, modified, or removed as described below.

 

Columns

 

Sorting by Columns within the Report Viewer

While viewing reports in the Report Viewer, a user can click the bar at the top of the report to sort by a column. For Express Reports, this is handled automatically but must be enabled for Advanced and Crosstab Reports.

To make a column sortable:

NOTE. Column Sorts are applied AFTER any sorts defined in the Sorts Menu.

 

 

 

Rows

 

Cells

Cells are the containers for all the information in a report. Cells may contain text, images, charts, or links to other reports.

 

 

Using Page Breaks

For a Page Break to occur at the beginning of each element of a Data Field, place a page break on the top row of Group Header Section for that Data Field. See Sections for more detail on Group Header Sections.

 

Creating Collapsible Rows

A Group Section can be set to display as collapsed by default on HTML export. This causes the contents of the section to be suppressed and individually expandable for each change in Header. Collapsible rows are supported in the standard or Interactive HTML viewer. Non-HTML export formats will ignore Collapsible Rows.

Left-click on an arrow next to a Collapsible Row to expand or collapse the group. 

 

Right-click on an arrow next to a Collapsible Row to see additional display options:

To create a collapsible row, click on the desired row number in a Group Header Section and select Collapse Rows in the dropdown menu.

 

 

Properties of a Collapsible Row

Collapsible Rows have the following properties when exported to HTML:

NOTE. Collapsed or expanded states cannot be saved to the Interactive HTML User Report preferences.

 

See Interacting with the Report Viewer for more information on the Interactive Report Viewer.

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.

screen.link_chart.png   screen.link_cell.png

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.

screen.link_child.png   screen.link_parent_with_child.png

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 in order 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 LinkedReport.png icon. The cell can contain a data field, formula, or a column-based or row-based chart.

  3. Select the child report, then click Okay.

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 LinkedReportNotice.png icon, or select the cell and click the Link Reports LinkedReport.png icon.

  2. Click the Remove Link DeleteItem.png 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:

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 Add2.png 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 Okay.

Formula

Caution: The Formula tab is recommended for advanced users only.

The Formula tab allows you to specify a custom formula in order to further filter the data passed from the linked report. The formula must return True or False. The formula is evaluated for each row in the parent report, and if the condition is not met, the data is excluded from the linked report. See Formulas for help with using conditional formulas.

Caution: Linked report formulas support only one data field. If multiple data fields are used, all but the first will be ignored.

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.

screen.dashboardviewer.png

Viewing an interactive dashboard

To run a dashboard:

The Dashboard Viewer opens in a new tab, indicated by the viewer TabInteractiveSelected.png 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 MoveGridRowUp.png or down MoveGridRowDown.png icons to search for the text in the report. Page through the report by using the navigation icons PageHome.png PageUp.png PageDown.png PageEnd.png.

Use the TileModeVizSelected.png Show Chart and TileModeTabularSelected.png Show Table icons to swap between chart or tabular view for an ExpressView visualization.

Click the menu  icon in the top right corner for some additional view options for reports:

Click the refresh Refresh.png 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 Filter.png 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 ParametersMenu.png 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 on the Dashboard itself. You can also add images, text, embed other web sites side by side with your data, add interactive filters which can work on multiple reports all at once, and export the Dashboard as a Chained Report from the Dashboard Designer.

screen.dashboarddesigner.png

The Dashboard Designer

Double-click a Dashboard to open it in the Dashboard Designer, or click the Menu screen.report_tree_menu.png icon and select Edit.png 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. Filter tiles will not display in Dashboard exports. See Interactive Filters and Exporting Dashboards 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 FormatMenu.png 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 SnapActive.png 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 FormatMenu.png 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 screen.dashboard_tilemenu.png 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, Interactive Filters, and Exporting Dashboards for more information about their unique features and options.

Dashboard Designer: 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 Filter.png Filters or ParametersMenu.png 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 TreeReportLock.png 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.

Dashboard Designer: ExpressView Visualizations

You can create tabular reports and visualizations directly on the dashboard with ExpressView Visualizations. To do so, either:

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 TileModeTabularSelected.png icon. To swap back to a chart, click the Show Chart TileModeVizSelected.png 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 screen.dashboard_tilemenu.png icon and select Save as ExpressView. The visualization will open in the ExpressView Designer.

(v2017.3+) 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.

Dashboard Designer: 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:

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.

Exports (v2018.2+)

Interactive filter tiles will not display in exported Dashboards, which are exported in the form of Chained Reports. Though the tiles themselves will not display, the default values set for the filters will apply to their corresponding reports and visualizations in the exported file. Learn more about exporting Dashboards here.

Express Reports

The Express Report Wizard is an interactive tool which allows you to quickly create and edit Express Reports.

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

To Save an Express Report, click the save  button.

 

The Express Report Wizard has six sub tabs. The Name, Categories, and Layout tabs must be completed while the other tabs are optional.

 

Name Tab

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

The report name can be up to 255 characters. Avoid special characters such as ? : / \ * “ < >.

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

NOTE. You cannot create a report inside a folder that is read-only ().

 

 

Categories Tab

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

Data Category

A Data Category is a data object that has several attributes. E.g., Orders is a category; each order has an ID, a date, a customer etc.

Data Field

A Data Field is a single attribute within a category. E.g., Orders.OrderID is numeric value that identifies a specific order.

 

 

Sorts Tab

In the Sorts Tab, specify which Data Fields will be used to determine the order of data on the report.

 

 

Filters Tab

In the Filters Tab, create statements that will be used to filter the data when you execute the report.

 

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

 

Layout Tab

In the Layout Tab, select which Data Fields that will appear on the report. For each Data Field chosen, the report will automatically create a column header and the Data Field. Additionally, sub-totals, grand totals, and page header/footers can be created.

 

Display Data

For each Data Field added in the Sorts tab, a checkbox will appear in the Summarize By box. Using the ‘Summarize By’ box, you can display subtotals, grand totals, or headers for each unique value of a Data Field.

 

Subtotals and Grand Totals

 

Data Headers

To display a header for each value of a Data Field, click on the associated Data Category in the Summarize By box. Click the Data Category name next to the checkbox, and the Header Menu will appear.

 

Page Header

To display information on the top of each page, click 'Page Header' below the 'Summarize By' box. A Page Header Menu will appear.

 

 

Footers

To display information on the bottom of each page, click 'Page Footer' below the 'Summarize By' box. Click 'Page Footer' next to the checkbox, and the Page Footer Menu will appear.

 

 

Preview

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

 

Styling Express Reports

Above the preview is a toolbar. This toolbar can be used to stylize the Express Report. To utilize this toolbar, select the cell(s) you want to modify from the preview.

 

The following icons are available in the toolbar:

Undo/Redo – can undo or redo the last change made. You can also use Ctrl+Z /Ctrl+Y respectively. 

Layout Options – see Layout Options for more information. 

Font – see Font for more information. 

Foreground & Background Color – see Color for more information. 

Number/Date Format – see Formatting Cells for more information. 

Border Color – see Formatting Cells for more information. 

Alignment – see Alignment for more information. 

Theme – see Theme for more information. 

 

Layout Options

In Layout Options, you can hide the detail information and set row shading.

NOTE. Row Shading is only applied to the detail rows that contain Data Fields.

 

 

Express Report Themes

The Theme dropdown can be used to quickly style the report using one of the pre-defined themes. After selecting a Theme, styling can still be modified. See Styling Express Reports for more information.

 

Options Tab

The Options Tab allows you to control various report option settings.

 

General Options

 

 

Export Options

General Export Options

HTML Options

Excel Options

Page Options

 

Advanced Options

In the Advanced Options, an Express Report can be converted to an Advanced Report.

IMPORTANT. This CANNOT be undone.

Advanced Reports

The New Advanced Report Wizard is an interactive tool which will walk through the process of creating a new report. All of the selections made in the New Advanced Report Wizard can be modified in the Report Designer after the report is created.

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

 

The New Report Wizard has five sub-tabs. The Name and Categories tabs must be completed while the other tabs are optional.

 

Name Tab

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

The report name can be up to 255 characters. Avoid special characters such as ? : / \ * “ < >.

The report’s description appears at the bottom of the Main Menu when it is selected. The description text is also used when searching for a report.

NOTE. You cannot create a report inside a folder that is read-only ().

 

Categories Tab

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

 

Data Category

A Data Category is a data object that has several attributes. E.g, Students is a category; each student has an ID, a major, an adviser etc.

 

Data Field

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

 

Sorts Tab

In the Sorts Tab, specify which Data Fields will be used to determine the order of data on the report.

 

 

Filters Tab

In the Filters Tab, create statements that will be used to filter the data when you execute the report.

 

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

 

Layout Tab

In the Layout Tab, select the Data Fields that will appear on the report. For each Data Field chosen, the report will automatically create a column header and place the Data Field in the detail section. Additionally, subtotals, grand totals, and a page header/footer can be created.

 

Display Data

Using the ‘Summarize By’ box, you can display subtotals, grand totals, or headers for each unique value of a Data Field.

 

Sub-Totals and Grand Totals

Summary Functions:

 

Data Headers

A check box will appear in the Summarize By box for each Data Category in the Sorts Tab. To display a header for each value of a Data Field, click on the associated Data Category in the Summarize By box. Click the Data Category name next to the checkbox, and the Header Menu will appear.

 

Page Header

To display information on the top of each page, click ‘Page Header’ below the ‘Summarize By’ box. A Page Header Menu will appear.

 

 

Footers

To display information on the bottom of each page, check the ‘Page Footer’ box below the ‘Summarize By’ box. Click on 'Page Footer' next to the box, and the Page Footer Menu will appear.

 

 

Preview

At the bottom of the Layout Tab, a preview will display how the report will appear based on the fields that have been added.  You can increase the size of the preview or hide it all together by dragging or clicking the re-size button ().

CrossTab Reports

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

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

 

The New CrossTab Report Wizard has four sub tabs. The Name, Categories, and Layout tabs must be completed while the other tabs are optional.

 

Name Tab

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

The report name can be up to 255 characters long. Avoid special characters such as ? : / \ * “ < >.

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

NOTE. You cannot create a report inside a folder that is read-only ().

 

 

Categories Tab

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

 

Data Category

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

 

Data Field

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

 

 

Filters Tab

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

 

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

Layout Tab

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

 

Row Headers

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

NOTE. If there is more than one Row Header, the Header Options Menu for the topmost Row Header will have Options for subtotals of Tabulation Data.

 

Column Headers

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

 

NOTE. If there is more than one Column Header the Header Options Menu for the topmost Column Header will have Options for subtotals of Tabulation Data.

 

Tabulation Data

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

 

CrossTab Themes

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

 

CrossTab Options

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

 

General

 

Grand Total Row

Grand Total Column

 

Preview

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

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.

Dashboards are also exported in the form of Chained Reports in which each report or visualization tile is displayed on a single page in the report. See Exporting Dashboards for directions on how to export a Dashboard as a Chained Report.

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 Add2.png icon and select NewChainedReportItem.png 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 panel, add the reports to include in the Chained Report. Supported report types are Advanced Reports, Express ReportsCrosstab Reports, and ExpressViews.

    The order of the reports in the list is the order they will appear in the output. Click the Up MoveGridRowUp.png or Down MoveGridRowDown.png arrows to move a report up or down in the list. Click the Delete DeleteItem.png icon to remove a report from the list.

  3. Click the Save Save.png 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, RTF (Word), CSV, and XLS (Excel).

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 or scheduled.

To edit a report's prompting options:

  1. On the Reports panel, click the Report Options Edit.png 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.

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:

Caution: Collation is recommended for advanced users only. Dashboards do not have a collate option.

Google Maps

The Google Maps wizard allows you to insert interactive maps with highlighted data into your reports. To add a map, select a cell and press the  (Google Maps Wizard) button. Geographical areas can be pinpointed or highlighted dynamically depending on your report data.

Note: If you don't see this option, you may not have access. Please contact your administrator.
Note: Google Maps require an Internet connection.

The Google Maps Wizard has four tabs: Locations, Data, Appearance, and Size and Preview. You can navigate between the tabs by clicking on the tab, or using the Previous and Next buttons.

Locations

conditional_drop_pin_locationstab.png

In the Locations tab, select one or more types of locations to highlight on the map. Drag-and-drop a location type to the selection pane, double-click it, or select it and press arrow.png.

Certain location types may require other types to be added or may prevent other types from being added. Press  to remove a Location Type. For each Location Type, use the dropdown menu to select the report cell that contains the data for that type.

Note: County and zip code are only available in the United States.

Data

 datatab.png

In the Data tab, determine what metrics to see when hovering over a highlighted region. The Primary Metric amount is used to shade each region. Press  Add Metric to add additional metrics. Press  to remove a metric.

For each Metric:

 

Note: Use the summary function "None" when you want to display a cell value within the drop pin tooltip.
Note: You cannot select "None" as the primary metric.

Hyperlinks (v2018.2+)

Using the "None" Agg. Type, hyperlinks can now be added at the drop pin level. 

googlemaps_hyperlink.png

To do this, a hyperlink must be available for reference within the report.

googlemaps_hyperlink_enterdata.png

In this case, a Hyperlink formula referencing a Google search for the company name has been added to a suppressed footer that has been grouped on Customers.CompanyName

Note: Hyperlinks used for drop pins can be referenced from any section within the report; however, for dynamic linking, hyperlink information should be referenced from the detail section or a grouped section.

The hyperlink must then be added as a Metric with the Agg. Type set to "None." 

googlemaps_hyperlink_addmetric.png

Finally, add a Label for the hyperlink and click Finish.  

 

Appearance

appearance_tab.png

In the Appearance tab, determine the appearance of the metrics on the map.

Primary Metric Colors: Set the color range for region shading by either selecting a Theme or by setting a Linear Range of colors.

Metric Levels: Select which levels to calculate metrics. Select whether to display drop pins, which will appear on city, street address, or latitude/longitude locations.

Note: The summary function "None" requires a drop pin.

Conditional Drop Pin Colors: Dynamically modify a drop pin color based on its value (v2018.2+). This action is similar to conditionally formatting cells in a report. 

To begin, select the Conditional Drop Pin Colors option.

conditional_drop_pin_colors.png

 The following dialog box will open. 

conditional_drop_pin_colors_dialogbox.png

Click  Add to insert a new conditional statement. Select a color that the drop pin will become when a certain condition is met.

conditional_drop_pin_colors_selectcolors.png

In this case, let's add two conditional statements using the colors red and green in order to differentiate between companies by their revenue.

Now let's add the conditions which, if true, will cause the colors to be applied to the drop pins. Click the [fx] button to open the Formula Editor:

conditional_drop_pin_colors_formulaeditor.png

The left side contains a list of the Conditional Drop Pin Parameters, which include the metric label, metric value, and anything defined in the location tab. The right side contains all of Exago's built-in formulas. You can drag a data field or a formula to the Formula box, or type it in by hand. The editor also supports using parameters in the form @ParameterName@.

In this case, we are concerned with the revenue of each company, which are represented as individual metric levels. If we want to show green pins for high performing companies and red pins for companies at risk, we could input the respective formulas into the editor. 

conditional_drop_pin_colors_colorsandformulas.png

Since this is a Conditional Formula, we need to ensure that the formula we write will resolve to either True or False. So the formula must contain at least one logical operator. The supported logical operators are as follows:

Operator Symbol
Greater Than >
Less Than <
Equal To =
Not Equal To !=
Greater Than Or Equal To >=
Less Than Or Equal To <=

You can also use parentheses to indicate the precedence of the parts of your statement.

Note: If a metric returns a null value, the metric and label are not displayed on the drop pin tooltip.

Size and Preview

googlemapes_sizeandpreview_tab.png

In the Size and Preview tab, determine the size, initial view, and type of map to display.

Note: The preview uses placeholder data values. It does not reflect the actual region shading.

Set the initial view of the map by panning and zooming to a location. Drag-and-drop on the map to pan. Press the zoom buttons ( ) to zoom in or out.

To set the size, either drag-and-drop on the lower-right handle () of the map view, or check Set specific size and enter Height and Width values. To automatically fit the map to the report cell, check Fit map to cell.

Choose the type of map by selecting the desired type from the Base Map dropdown menu.

Press the Finish button to see the completed map on the report design grid.

Charts

Charts are a way of illustrating data in order to make it easy to spot trends and patterns. Most types of charts are simply a collection of points on a grid, with interstitial designs and labels that make them easier to read.

By default, charts are generated dynamically, based on data points that come from Data Fields. Each data field can be thought of as a "series" of data, which have a common association and are connected in some way. When we put one data field on a chart, we have a single-series chart, which is useful for comparing values to each other. When we put multiple data fields on a chart, we have a multi-series chart, which is useful for comparing trends.

Before creating a chart, make sure that your data exists in cells on the report. These cells don't have to be visible, so you can suppress them if desired. Charts are interactive in the Report Viewer, but will appear as static images in PDF, RTF, and Excel formats (CSV is incompatible).

To insert a chart into a report, select a Group Footer or Report Footer cell and press the  Chart Wizard button. The Chart Wizard dialog will open.

The Chart Wizard has four tabs: Type, Data, Appearance, and Size and Preview. You can navigate between the tabs by clicking on the tab, or using the  Previous and  Next buttons.

Chart Types

The Type Tab lays out all the available types of charts you can create. There are 20 types, sorted into five general categories. Click on a category header to see more information about that type.

Click on a chart for more information about each type. 

Line

         

 

Bar and Column

               

Pie and Other Single-Series

Scatter and Bubble

           

 

Combination Charts (v2016.3)

 

 

Line

Line charts display series of data points on a grid, connected by straight lines. They are often used to display a trend over time.

Each series on a line chart is represented as a colored line. Line charts can have up to three Y-axes.

Variations:

Bar and Column

Bar charts use rectangular bars which extend horizontally left to right to show comparisons between categories. Column charts use vertical bars which extend upward. The length of a bar represents the quantity of the data value.

Each series on a bar or column chart is represented by a colored set of bars.

Variations:                

Pie and Other Single-Series

Pie charts are used to show the relationship of data values in a series as portions of the total. The area of each slice is proportional to the quantity.

Each data value on a pie chart is represented by a colored "slice". Pie charts are single-series only.

Variations:

Scatter and Bubble

Scatter charts use pairs of data fields with a common relation to generate coordinates as points on a grid. They are often used to find relationships between two variables in a set of data. Unlike most other report types, scatter charts often map data from detail rows, instead of group rows.

Each series on a scatter chart is represented by a different shape and color combination.

Variations:

Combination Charts

(v2016.3) Combination charts are several different charts layered on top of one another. They comprise a combination of Column, Line, Area, and/or Stacked Column charts. (C­olumn and Stacked Column charts are not compatible with each other). Combination charts can have up to two Y-axes.

Data

The Data Tab is used to 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.

Add series to the chart by selecting a Data Field containing numeric values from the Series Values dropdown menu. 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.

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

Add additional series by pressing the  Add Series button (disabled for single-series charts). Give a Name to each series. Press  to remove a series.

Change the data layout by pressing the  Data Layout... button. This will open the Data Layout dialog. If you change the data layout, this section will change for you to add either individual points, or groups of series, instead of adding individual series. See Data Layout for details.

Use the Sort data by dropdown to determine how series data should be ordered:

You can sort data in Ascending (A- Z, 0-9) or Descending (Z-A, 9-0) order.

Use the Exclude values fields to ignore values that are too large and/or too small.

(Grid charts) Use the Data Axis Value fields to set upper and/or lower bounds for the data axis.

(Grid charts) Check Align Data Labels Across Series if you have multiple series with data points at common intervals.

(Pie charts) Use the Other Category Percent field to group data fields with small quantities into an "Other" category.

Data Layout

Your data may not fit neatly into series. This dialog accommodates different data layouts by allowing you to select from a couple of different ways to build a chart.

Column Based Chart is the default. This layout builds charts by taking data fields, and mapping selected values as Y-coordinates on the data axis. Determine which values are selected by specifying a data field with a common relation as the X-axis. This layout is useful if you want to plot one or more unrelated series in a group (e.g. Budget and Sales and Expenditures per Store).

Use Column Based Chart if... Your report contains a group with one or more elements. For example:

Row Based Chart is a little more complex. This layout still uses fields as series, but all your series are a group, nested within another group which determines the X-axis values. Data values are mapped per series per group. This layout is useful if you want to plot two or more related series in a group (e.g. Sales per Employee per Store).

Use Row Based Chart if...  Your report contains a group within a group. For example:

If you select this layout, the data selector will change to allow you to add all your series as a group, nested within an outer group for the data labels:

Cell Based Chart is the simplest option. This layout builds charts by taking pairs of static report values, and using them as (X,Y) or (label, value) coordinate pairs.

In order for the chart wizard to recognize report cells, they must be in Formula form, with a preceding = sign, text surrounded by quotes, and data fields surrounded by braces { }. Examples:

Use Cell Based Chart if... You want to build a chart point by point, and only have one data series. For example:

If you select this layout, the data selector will change to allow you to add points. This layout only supports one series of data (duplicating data labels will create duplicate axis labels):

Appearance

The Appearance Tab contains options for customizing how the chart will look.

Colors

Use the Colors dropdown to select a color theme to apply to the chart. Specify a custom range of colors by selecting the Linear Range option.

Check Use 3D Style to give your chart a three-dimensional look.

Labels

Chart Title – Enter the text you want to appear in at the top of the chart.

(Grid charts) X-Axis Title – Enter the text you want to appear on the X-Axis (horizontal axis).

(Grid charts) Y-Axis Title – Enter the text you want to appear on the Y-Axis (vertical axis).

Use the Point Labels dropdown to label the points on the chart:

Use the Legend Position dropdown to choose where to display the legend relative to the chart.

Use the Label Font dropdown to specify the font for the labels.

Use the  Number Format... dialog to specify how data and axis labels should be formatted:

(Line & combo charts) (v2016.3) Use the  Chart Axes button to add and format axes:

Use the  Benchmark Lines... dialog to add horizontal lines at specific sections of the chart:

Other Features

This section allows you to customize a variety of attributes. The following attributes are supported:

To add a customization, select an attribute from the dropdown menu and press  Add Attribute. Then enter a custom property into the attribute field or select from the attribute dropdown menu.

Press  to remove a customization.

 

Size and Preview

The Size and Preview Tab allows you to change the size of the chart and preview any customizations.

NOTE. Chart previews in the Wizard and on the Design Grid use placeholder data.

You can change the size of the chart in one of three ways:

Chart Data Layout Types

Charts illustrate our data to us so that we can spot patterns and trends easily, but it's important to remember that a chart is simply a collection of points on a grid. Each point has its label (typically along the x axis) and its value (typically along the y axis). 

There are several ways to represent this collection of points on a report, and the application must be able to transform them into a chart regardless of the design you've chosen. For this reason, the Chart Wizard comes with three Data Layout Types. Understanding the way you've designed your report and data will help you select the right Layout Type, which will in turn tell the application how to transform your data into a chart.

Note: Chart Data Layout Types were introduced in v2016.1 and are named Layout One, Layout Two, and Layout Three until v2016.3, when they are renamed Cell-Based Chart, Column-Based Chart, and Row-Based Chart, respectively. 

Layout One: Cell-Based Chart

Layout One is for building a specific type of single-series chart using a collection of static points on your report. This layout type is cell-based because each point's labels and values come from a specific cell in the report output.

 

How do I know this is the right Layout for me?

Take a look at your report design and determine whether the points you're interested in charting are static or dynamic. In the application, a dynamic data point is one that comes from a field. Fields are denoted by a little triangle in the upper left-hand corner of the cell.

 

In the example above, any data points coming from this cell will be dynamic because the cell contains a dynamic field (see the triangle in the blue box). The Employees.FullName field is a placeholder for all the employee names that will appear on the report. These names will be added to the report dynamically after you're done designing it and have clicked the "run" button.

 

Static data points, by contrast, are defined explicitly in the report design and do not contain dynamic fields.

 

In the above example, every cell contains a static value. The first, third, and fifth cells each contain a string formula that will return the text inside the quote marks. No matter what section these formulas are in, they will only ever return their respective strings of text. The second, fourth, and sixth cells contain calculation formulas returning a static value, in this case the minimum, average, and maximum number of days it takes to ship an order, respectively. When we chart this data, we'll need to use Layout One in order to pinpoint each of these cells and define them as either data labels or data values. In this case, the string formulas will serve as the labels, and the calculations will be the values. Together, they make up three sets of coordinates on the chart.

 

How would that look in the Chart Wizard?

 

What would the final report design look like?

 

How would the output look?

 

Layout Two: Column-Based Chart

Both Layout Two and Layout Three are for creating single and multi-series charts, but there are some important differences in the report designs they work with. Layout Two is great if you want to show two or more unrelated values for a single label. Values may be considered "unrelated" if they come from separate fields or columns. This layout is column-based because each column will add a new series of data to the chart.

 

How do I know this is the right Layout for me?

First, make sure you're working with dynamic data points. Second, make sure you're interested in calculations for just one group. If you have two columns representing values you'd like to plot against a column representing your labels, you're in the right place!

In the example above, each column becomes a new series charted against the label column. Since all the labels come from a single column, we know to use Data Layout 2. Another indication that Layout Two is correct to use in this case is that the series labels are static values rather than dynamic values that are part of the data fields. 

How would that look in the Chart Wizard?

 

The Chart Wizard makes it easy to see the how this Layout is all about adding values to a given label.

It helps a lot if your calculations are also in the same numeric range. For example, charting tree height (0 - 50 feet) on the same y axis as tree species population in a forest (0 - 50,000 trees) will make it hard to read smaller values. If your values aren't in the same numeric range, you can add one or more y axes in different numeric ranges on the appearance tab. Note: this feature is only available as of v2016.3; see Charts for more information.

What would the final report design look like?

 

In this example, the label field is in cell A3, and the three value fields are in cells B3, C3, and D3, respectively.

How would the output look?

 

Layout Three: Row-Based Chart

Layout Three is also for building single and multi-series charts, but when used to create a multi-series chart, it requires a bit more prep on the design end because it compares two or more related values for a given label. Values can be considered "related" when they come from the selfsame column or field. This layout is row-based because all series are coming from the rows of one column of data.

 

In the above example, the series labels all come from the same column and are dynamic values coming from a data field. Each unique value coming from that field, in this case Year, will become a new series.

 

How do I know this is the right Layout for me?

First, make sure you're working with dynamic data points. Second, make sure you have one group nested inside another. (See Understanding Sorting and Grouping for more information.) If you want to create a multi-series chart and you want to show a calculation per a group per another group, Layout Three is the setting you want. 

Still unsure? Here's a simple verbal test to show whether you're looking for Layout Two or Layout Three when you build your multi-series chart. Which group of statements sounds truest to your case? 

 

Group A

Group B

 

If your case is more like Group A, you're looking at Layout Three. If your case is more like Group B, you'll want to go with Layout Two.

How would that look in the Chart Wizard?

 

What would the final report design look like?

In this example, I want the report to show revenue per category per year. I've made this possible by nesting a Year group inside a Category group. (CategoryName is the primary sort, and Year is the secondary sort.)

 

How would the output look?

 

GeoCharts

A GeoChart can be displayed in a report to give a visual representation of geographic data. To insert a GeoChart, select a cell and click the GeoChart Wizard button (). The GeoChart Wizard has three tabs: Type, Locations, and Data.

NOTE. GeoCharts should only be placed into a Group Header, Group Footer, Report Header or Report Footer section.

Type

In the Type Tab, select the initial view, size, colors, and where to display the legend.

 

Locations

In the Locations Tab, specify which geographic locations should display on the map.

 

Data

In the Data Tab, specify which data determines the color of each country/region/city and the size of each marker.

For each Data Value:

 

Example

Take the following report as an example.

 

The subsequent steps show how to create a GeoChart in this report. The map will be colored based on the number of customers in each location and the markers will be sized based on how many orders have been placed in each location:

 

 

 

Report Designer:

NOTE. In the report designer, the GeoChart is always represented by the same image regardless of the size, color, or world view of the map that will be generated on the report.

 

Report Viewer:

Gauges

A Gauge can be displayed in a report to give a visual representation of the scale of a value. To insert a Gauge, select a cell and click the Insert Gauge button (). The Gauge Wizard will appear. The Gauge Wizard has two tabs: Appearance and Data.

NOTE. Gauges can be placed in any section of the report.

 

Appearance

In the Appearance Tab, select the Type and Dimension of the Gauge.

Type – Select the icon representing the type of gauge. Available types include: Angular, Linear, Bulb, and Thermometer.

There are three ways to set the size of the Gauge.

Note: Angular gauges have a minimum width of 120px.

 

Data

In the Data Tab, select the Data Values and Color Ranges for the Gauge.

NOTE. Percent Color Ranges must be in ascending numeric order.
NOTE. Thermometer Gauges can only have one color.

To change a color either use the drop-down () or enter a Hex value.

Conditionally Format Data in a Chart

Version 2017.1 allows you to use formulas to conditionally alter the colors of data on the chart. This is similar to conditionally formatting cells. The color of a data element depends on its value.

For example, you could specify that if any bar on a bar chart exceeds a certain value, then it will be colored red.

To conditionally format elements in a chart:

  1. Double-click a chart to open the chart wizard. Click the Appearance tab.

  2. Click Conditional Colors.

  3. Click Add2.png Add to add a condition. Choose a color by entering a hex value, or using the color picker.

  4. Click the formula Formula.png icon to set the conditional formula.

  5. Enter a formula which evaluates to True or False.

    In order to get the data element value, there are several special parameters that you can use in the formula. These parameters correspond with the fields on the Data page, which you use to select data for the chart.

    The exact parameters depend on the type of chart and the data layout:

    @data_label@: Corresponds with the Data Label field, or X-Axis field for scatter and bubble charts. Corresponds with the Point Label field for charts using the Cell Based data layout.

    @data_value@: Corresponds with the Data Value field, or Y-Axis field for scatter and bubble charts. Corresponds with the Point Value field for charts using the Cell Based data layout.

    @series_label@: Corresponds with the Series Label field. Available for charts using the Row Based data layout.

    @bubble_label@: Corresponds with the Bubble Label field for bubble charts.

    @bubble_size@: Corresponds with the Bubble Size field for bubble charts.

    Note. If you change the type of chart, some parameters may become unavailable. You may have to go back and edit the conditional formulas.

  6. Click OK. To add more conditions, repeat steps 3-6.

    If there are multiple conditions, they evaluate in order from the lowest row to the highest. To change the order, reorder the rows using the up and down arrows.

Note. You cannot use cell references, data field references, or parameter references in a chart conditional formula.

Formula Editor

Starting in version 2017.2, 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.

screen.formula_typetosearch.png

List of functions that match the text

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.

screen.formula_argumentdesc.png

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

screen.formula_optionalarg.png screen.formula_arglist.png

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.

screen.formula_dragfield.png

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 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:

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.

screen.formula_syntaxerror.png

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

General Options

Renaming Reports

To change the name of a report click ‘Rename’ in the Toolbar drop-down menu. Enter a new name and select the folder you want to save the report. Click ‘OK’.

     

Changing Description

The report description appears at the bottom of the Main Menu. Report descriptions are optional but they can be searched. To change a report description click ‘Description’ in the Toolbar drop-down menu. Write the description and click ‘OK.’

      

 

Changing Data Categories

Before explaining how to make Data Categories accessible it is important to clarify two terms: Data Category and Data Field.

Data Category – A Data Category is an object that has a group of attributes.

Ex. Orders is a category; each order has an ID, a date, a customer etc.

Data Field – A Data Field is a single attribute within a Data Category.

Ex. Orders.OrderID is numeric value that identifies a specific order.

 

To modify the Data Categories click ‘Categories’ in the Toolbar drop-down menu.

 

Changing Sorts

To modify the sort criteria of a report click ‘Sorts’ in the Toolbar drop-down menu.

 

Changing Filters

To modify the filter criteria of a report click ‘Filters’ in the Toolbar drop-down menu. There are three types of filters: Standard, Interactive and Group. Standard filters are based on values you specify. Interactive filters can be applied after executing the report to HTML. Group filters are based on the minimum or maximum value in the Data Field.

 

Standard Filters

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

 

Interactive Filters

Interactive Filters can be created in the Interactive HTML Options Menu. These filters can be enabled, disabled or modified after executing the report to HTML. For more information see Interactive HTML Options.

 

Group (Min/Max) Filters

Group filters are based on the minimum or maximum value in the Data Field. To modify group filters click ‘Switch to Group(MIN/MAX) filters. There is no limit to the number of group filters you may define.

 

 

General Options

Hover over ‘Options’ in the Toolbar drop-down and then click on ‘General’ menu to open the Report Options Window. This window allows you to control various settings including default export type and page orientation.

General Options

 

Excel Options

 

Page Options

 

Specify the size for the report in the ‘Page Size’ menu. Default is letter.Set the orientation for the report in the ‘Orientation’ menu. Default is Portrait.Check ‘Fit to Page Width’ to scale all columns to fit the width of the page.

Advanced Options

Click ‘Advanced’ in the Toolbar drop-down menu to open the Advanced Options window. This window allows you to specify additional information about how the Data Categories relate to each other.

If a report has two or more Data Categories, then information will only appear if it matches both categories. Using the check boxes, specify information that you want displayed even if it only exists in one of the Data Categories.

For example if a report has two Data Categories, Orders and Customers, then only customers who have made orders will appear. By checking the appropriate box you can include information on customers that have not made orders.

 

More Advanced Options

If you have been given permissions, additional options are available in the Advanced Menu.

Joins

 

When you click the 'Add' or 'Edit' buttons the Report Join Menu will appear. In this menu you can create or modify a Join for the report.

Ex. In the image below Customers.CustomerID corresponds to Orders.CustomerID so both Fields are at the top of their sections.

User Preferences and Context Sensitive Help

If given permission by your administrator the User Preferences button will appear in the top right corner. Click the User Preferences button userpreferencesicon.png to open the User Preferences menu.

userpreficon.png

Startup Reports

In the User Preferences menu set your preferences such as which reports should run at startup and/or what User Reports should be applied.

startupreports.png

 

Context Sensitive Help

Context sensitive help is available at any point in the application. Click the help button  and documentation will appear in a new tab. The guide will automatically open to the section discussing the feature you are using.

contexthelpicon.png

Tutorials

Disable or enable ExpressView Tutorials and Hints in the User Preferences menu (2018.2+).

tutorialmodeuserpreferences.png

TutorialModefirstimage.png

tutorialmodedontshowagain.png