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.
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.
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.
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.
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
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 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.
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:
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.
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.
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 are simple reports which comprise vertically expanding data records and groups, and an optional visualization.
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 are the flagship report type of the application.
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 are a special type of Advanced Report.
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 are a simpler version of Advanced Reports.
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 combine one or more reports onto a design canvas, providing a way to create a personalized arrangement and display of related reports.
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 combine multiple reports into a single multi-page document.
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.
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.
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 ().
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.
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.
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.
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.
Using the ‘Summarize By’ box, you can display subtotals, grand totals, or headers for the values of a Data Field.
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.)
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.)
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.
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.
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 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.
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.
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.
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.
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.
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.
The scheduler allows you to run reports regularly at specified times. This can be useful for reports which are based on periods of time, such as weekly sales reports or quarterly earnings reports.
Schedules are created and edited with the Schedule Report Wizard, which is a tool designed to streamline schedule creation.
To schedule a report:
Click the Menu icon and select
Schedule Report.
Enter a Name for the schedule.
Choose which file type to save the report as.
Optional: For PDF or Excel, enter a Password to secure the report.
Select a date and time for the schedule, and choose how often it should run:
In the Schedule Time field, enter a time for when the schedule should run.
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.
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.
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.
Optional: If you have entered a recurrence pattern, enter a date for when the schedule should Start.
Optional: To add filters to the report click the Filters tab. If the report already has filters you can set their values here. See Filters for instructions.
Tip: Chained Reports do not support schedule filters.
Optional: To email this schedule to addresses from a database, see Emailing personalized reports.
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:
Enter the recipients' email addresses in the To, Cc, and Bcc fields.
Optional: Enter an email subject in the Subject field.
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.
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.
Click Finish to save the schedule.
Schedules can be edited after they are created. See Viewing schedules for more information.
To see the schedules that have been created click the Schedule Manager icon. Each schedule is on a row with its most recent and forthcoming run times, and a status indicating its condition:
Ready
The schedule will run on its next run time.
Running
The schedule is currently running.
Transmitting
The scheduled event has finished running, and the report is being sent to the user.
Completed
The schedule has completed its final event, and will not run again. It will be removed from the list when the cache is flushed.
Deleted
The schedule has been deleted, and will be removed from the list when the cache is flushed.
Abended
The last run failed due to an error. The schedule will not run again.
User Aborted
The schedule is running, but it was requested to be canceled. It will be marked as Deleted.
Scheduled events which have passed are copied to an archive row, so that there is a log of previous run times. Such events are labeled (archive). Deleting these rows will not affect the main schedule.
Schedules which are associated with a cached report are labeled (cache). See Execution Caching for more information.
Completed and canceled schedules and archives may be periodically removed from the list. Click Flush to remove them immediately.
Managing schedules
To edit an existing schedule click the Edit icon. Changes to the recurrence pattern will affect all forthcoming run times. You can reuse or reset deleted or completed schedules by editing them. This will set their status to Ready.
To delete a schedule click the Delete icon. This sets its status to Deleted. If the schedule is already marked as Deleted, this removes the schedule from the list.
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:
In the Schedule Report Wizard, click the Batch tab.
Select the Run as Batch Report check box.
Optional: In the To and Cc fields, enter addresses to send a summary email for each completed execution.
From the Batch Email Field list, select the data field containing the email addresses for this schedule.
(v2017.3.8+) Fields can contain a list of email addresses, separated by semicolons (;).
Optional: Click the Recipients tab to enter a subject and message for the batch email.
You can reference data values from the email address data row in the message body. Type the parameter @batch_column@, where column is the name of a data field in the address category. The value of the field replaces the parameter text in the message output.
Tip: The To, Cc, and Bcc fields are unavailable when using Batch.
The ExpressView designer is data discovery and reporting tool that simplifies grouping, sorting, filtering, and aggregating data with a drag-and-drop interface. A chart can be added with a single click, and ExpressViews can be styled and saved as PDF, RTF, CSV, or Excel files.
The ExpressView Designer
ExpressViews allow you to quickly see data and make reports without concern for the minutiae of old-fashioned report building.
To make an ExpressView:
Click the New Report icon and select
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 icon to expand or collapse the Data pane.
Expand a category by clicking the arrow icon. This shows the fields in that category.
Drag a field onto the Design pane to add it to the ExpressView. This expands the field into a data column. It will only show placeholder data initially.
Dragging a field onto the Design pane
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.
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
in the toolbar.
Truncated results displayed
This icon and message indicates that the report has only returned a partial data set. There are fewer detail rows than the full set, and aggregate formulas and visualizations apply only to the data that was returned to the report.
To get more data, click the icon and select either:
Generate +number to get the next number of data rows and add them to the existing report.
Generate All to get the full data set.
Click the Save 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 icon on the top left of the column or group.
Using the radial menu
Each radial menu has four options, arranged in a circle around the center. When you are prompted to select a radial menu option, you will be asked to "use radial>direction". Direction is one of the four cardinal directions corresponding with one of the four options: left, right, up, down.
For example, "use 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.
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 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:
Click the Formatting and Style icon to open the Formatting page.
Click the data rows to format. They will highlight in blue.
Selecting data to format
Click the Data Format tab.
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.
Choosing a date/time format
Text
Do not apply any formatting to the data, and show it exactly as it appears in the database
If the data field cannot be formatted as a number or date, then selecting one of those options will have no effect on the appearance of the data.
Sorting allows you to set the order that the data rows appear in each section. Click the Sorts 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:
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 for ascending, down
for descending.
To change a field's sort direction, either:
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 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:
On the Filters page, click the Standard tab.
To add a data field to filter, either:
Use radial>right on a data column or group.
Drag a data field from the Data pane to the Meet all of the following conditions pane.
Note. This allows you to filter any accessible data field, not just those on the ExpressView.
Choose a filter operator from the list. See Filters for details.
Enter a filter value or values, or select them from the list.
Repeat steps 2-4 for every filter condition that the data must satisfy in order to show.
To show only the data that satisfies at least one of several conditions:
On the Filters page, click the Standard tab.
To add a data field to filter, either:
Use radial>right on a data column or group. Then drag the filter to the meet any of the following conditions pane.
Drag a data field from the Data pane to the meet any of the following conditions pane.
Note. This allows you to filter any accessible data field, not just those on the ExpressView.
Choose a filter operator from the list. See Filters for details.
Enter a filter value or values, or select them from the list.
Repeat steps 2-4 for every filter condition, of which the data must satisfy at least one in order to show.
Adding a field as a Standard filter
To show only the top or bottom values, for either data fields or data summaries:
On the Filters page, click the Top/Bottom tab.
Select the Limit the report to the top/bottom values check box.
Select either Top or Bottom, for whether you want to show the top or bottom values.
Enter a number for how many values you want to show.
Choose how you want to limit your data:
If you want to show the rows with the top or bottom data values for a field or group, select Values from the list.
If you want to show the groups with the top or bottom summary values for a parent group or the ExpressView, select one of the summary calculations, Sum, Avg, Min, Max, Count, or Distinct Count, from the list.
Note. Only numeric data fields support Sum and Avg calculations.
Select the data field or group field to filter from the Of list.
Optional: To show the top or bottom values for each iteration of a group:
Click Add Group.
Select a group field from the For Each list.
Adding a Top/Bottom filter
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 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 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, or type its name into the Formula Builder. You can use the Search field in the to filter the functions by name.
To add a data field to the formula, drag it from the Data Pane to the Formula Builder window, or type its name into the Formula Builder.
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.
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 icon to save the formula.
To edit an existing formula column, click Selected Cell , open the Formula tab, then click the column to reopen the Formula Builder.
See Formulas for more details on building formulas.
ExpressViews can be styled in a variety of ways. Click the Formatting and Style icon to see your available options.
If there are any available, you can select a premade theme to use for the ExpressView, or to use as a baseline for further customizations. On the Formatting page, click the Theme tab. Then select a theme from the Theme Selector list:
Legacy
This is the default theme.
Custom
This option indicates that you are not currently using a premade theme.
Selecting a premade theme overrides any custom styling you have. Be sure to save the ExpressView first, so that you can retrieve your styling if you accidentally override it.
Selecting a premade theme
Data columns and group columns can be styled independently, using the Style tab on the Formatting page. You can style the data cells, as well as group headers and footers.
To style a data cell:
Selecting an element to format
You can also use the Formatting page to edit the header names of the data columns. To do so:
Changing header text
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:
Choosing data row 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:
Choosing radial menu colors
Charts allow you to showcase your data in a visual format. They allow you to quickly scan your data for patterns and trends. Charts can be easily made and customized in ExpressViews, in as little as one click.
An ExpressView Visualization
To add a chart to an ExpressView:
Click the Show Visualization icon. A bar chart is added and populated with the data from your ExpressView.
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.
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 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:
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:
Drag a field onto the visualization or onto the Data tab to add it as a value
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.
Grouping data in an ExpressView
To create a group from a column, use radial>up. 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 radial>left. This turns the group column back into a data column.
Groups can be created inside other groups. These are called nested groups. To make a nested group, 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.
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.
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 radial>up on the group column.
Proper grouping of data fields
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.
Choosing an aggregate
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:
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.
The data pane is organized into data categories, which you can expand to see their fields. Click the Choose Data icon to expand or collapse the data pane.
Viewing the available data fields
To locate a specific data field, type the name of the field into the Search field and press Enter. To see all the data fields, click 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 radial>left to remove a data column.
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:
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.
You can make some customizations to the appearance that the exported files will take. With the Settings page open, click the Export Settings tab to see the available options:
Page Options affect the size which PDF, RTF, and Excel exports will show on a screen and on print.
General Options allow you to restrict available export types, and choose a default type.
Other Options:
Include Setup Info: Choose whether to include some information about the fields, sorts, and filters in the body of the file.
Use Group Color Styling: Choose whether to include the group header colorings in the output file.
'No Data Qualified' Mode: If the ExpressView returns no data, choose whether to render an empty file, or show a user message instead.
Flatten Groups in Excel and CSV: For Excel or CSV files, choose whether to automatically ungroup, or "flatten" all group columns into data columns. This may make the output more suitable for data analysis.
Keep Cell Styling in Excel: For Excel files, choose whether to show styling, such as font and row shading, or to show only the bare data.
When you run an Advanced Report, Express Report, or Crosstab Report, the output is shown in a paged, interactive format called the Report Viewer. The viewer shows you the report populated by all of its data. You can page through the data and search for specific items. You can also make some additional adjustments to the report, such as styling cells or changing column sorts.
The interactive Report Viewer
To run a report in the Report Viewer:
If you are in the Report Designer, click Run Report.
Tip: The Run Report button also saves the report.
If you are in the folder tree, click the Run icon, or click the Menu
icon and select
Run Report.
The Report Viewer opens in a new tab, indicated by the viewer 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
. Search through the report output by typing into the Find field.
If you want to save the output, you can export from the Report Viewer to a PDF, RTF, CSV, or Excel file.
Truncated results displayed
This icon and message indicates that the report has only returned a partial data set. There are fewer detail rows than the full set, and aggregate formulas and visualizations apply only to the data that was returned to the report.
To get more data, click the icon and select either:
There are a number of ways to interact with reports in the Report Viewer. Your available options depend on how the report was designed and the type of content in it.
Tip: If your environment has Action Events, you may have access to more interactive features than are listed in this section.
Drilling into data
Some reports allow you to drill down into cells or charts to see some additional data related to the field you selected. If a cell or chart has a drilldown, you can click on one of the values to drill down into the related data.
Drilldowns are actually other reports that you can interact with or even export to a file. Drilldowns may even have drilldowns of their own!
Drilling down into a data cell and a chart
Expanding hidden data
Some reports have hidden data that can be seen by expanding certain rows. Expandable rows are indicated by an arrow icon . Click on the icon to expand the row. Click the icon again to collapse it. Right-click the icon to open a menu to do the following:
Expand / Collapse
Expand or collapse the outer group
Expand / Collapse Group
Expand or collapse the group and any inner groups
Expand / Collapse All
Expand or collapse all outer and inner groups on the report
Expanding a data row
Chart features
Charts have a number of interactive features depending on the type of chart. For example, line charts and bar charts allow you to hide series, multi-axis charts allow you to turn axes on or off, and pie charts allow you to rotate the chart and pull out slices. Charts can also show some additional information if you hover over a point or series.
Toggling a chart series on or off
Drilling into maps
On a Google Map, use the mouse wheel or click the Zoom in and Zoom out
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.
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 decoration, Alignment, Word wrap. Styling applies to all cells of the same type in the group; affected cells are outlined by a yellow band.
Right-clicking a cell to change its styling
For group cells with dynamic data from fields or formulas, use the Conditional Filter list to apply styling to only the cells that meet a certain condition. For each of the following options, styling applies to the cells of the same type in the group that meet the specified condition:
Apply to all
All cells
Apply to equal values
All cells with the same value as the selected cell
Apply to unequal values
All cells with different values than the selected cell
Apply to greater values
All cells with values greater than the selected cell (larger number, later date, or alphabetically subsequent)
Apply to greater than or equal values
All cells with values greater than or equal to the selected cell
Apply to lesser values
All cells with values less than the selected cell (smaller number, earlier date, or alphabetically preceding)
Apply to less than or equal values
All cells with values less than or equal to the selected cell
Tip: Select a condition before selecting the styling.
Styling charts
Right-click a chart to open the chart menu. Using this menu you can swap to a different Type of chart, change the color Theme, change the location of the Legend, and change the Sort order of the data series.
Right-clicking a chart to change its type
Managing data columns
Each column on the report has a light gray control box at the top of the page. Change the width of the columns by dragging the control left or right. Sort by a column by clicking the control. To hide a column, right-click the control and select Hide Column from the list.
Resizing a column and right clicking a column to hide it
Setting interactive sorts and filters
Reports can have some additional filters and sorts saved on top of the base report. Click the splitter 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 icon to add a filter for that field. To remove a filter, click the Delete Item
icon.
The order of sorted fields can be swapped between ascending and descending
.
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 icon. Conditional filters are shown in the sidebar.
Adding a conditional filter
The New Report Wizard opens at the start of each new Express Report, Advanced Report, or Crosstab Report. The wizard lets you add and configure data, and lets you quickly set a layout for your report before you dive into the full designer.
The Express Report Wizard Layout page
What are Express Reports?
This report type is made entirely using the Report Wizard. If you do not need the additional capabilities of the advanced Report Designer, Express Reports are an easy way to design tabular reports. To open an Express Report in the Advanced Designer, you need to convert it to an Advanced Report.
Using the Report Wizard
The Report Wizard has several pages, which you should progress through from left to right. The following articles detail how to use each page in the wizard.
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.
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 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.
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 icon.
Data categories are tables of data, which are organized by rows and columns. Columns are also known as data fields. A row of data has entries for one or more columns in the category. When you add a data field onto a report you are seeing the information in one column of data for every row in the category.
For example, a data category for Employees could have columns for the first and last names of each employee, an identification number, and a home phone number. Each row represents a person, and each column contains a specific type of information such as Last Name or Phone Number.
Example of a data category for employee records
You add entire categories at a time to a report, but in the report view you select only the columns you want to see. When you add a data field to the report design, even though you only see one column, the rest of the table is still present behind the scenes. You will never lose the connections between items in each row, and you can always add more fields.
On the Categories page, add data categories to the report. Later on, you can select which fields you actually want to see in the report layout.
Dragging a category to the Category Name pane
Tip: As you add categories, unrelated categories will become unavailable.
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 Add SQL. From the Custom SQL Object window, add the following:
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:
[ ] { } . , @
Data Source - Select the data source to retrieve the data from.
Not every data source you can access may support custom SQL categories.
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 icon to check if the SQL is valid.
When you have finished writing the SQL, click the Unique Key Fields list and select the unique keys for the category.
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 next to its name in the Categories window.
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 Lower values Higher values Desc
Date
Asc Past Future Desc
Text
Asc A Z Desc
Sorting Employees by the Department field, ascending
A report can have multiple sorts. This can be useful when you want your highest precedence sort to affect a data field where the values for multiple rows may be the same.
For example, imagine a large company with many employees. There could be multiple people with the last name Buchanan. With only a sort on LastName, you do not know how all the people with the last name Buchanan will be ordered amongst themselves. If this matters, then you can add a second sort on, say, the FirstName field, so that people with the same last names will be ordered by their first names.
Adding sorts
On the Sorts page, add data fields to sort. The precedence of the sorts starts with the highest row and moves down the list. Drag the rows up or down to change the precedence.
Dragging a field to the Sort By pane
Tip: Sorts are not applicable for Crosstab Reports.
Sometimes you may only want to see a portion of rows in a data category, rather than its entirety. Filters allow you to narrow the scope of your reports by restricting the amount of data by specified criteria.
For example, imagine a category containing a row for every single sale your company has ever made. You can use a filter to limit the report to only sales from the past month. This also has the benefit of speeding up your reports.
Filters in the Report Wizard, called Standard filters, limit data by only showing rows where the values for a field meet a certain condition.
Adding filters
On the Filters page, add data fields to filter.
Dragging a field to the Filter By pane
For each data field, select a condition. When the report is run, the field value for each row is checked against the condition for that field. Only the rows where the field satisfies the condition will show on the report.
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:
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 |
Enter a filter value or values, or select them from the list of existing values.
Choosing a filter value
Optional: If you have multiple filters, you can choose how they should be grouped.
Optional: If you want the report to prompt the user to enter a filter value when the report is run, select Prompt For Value.
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:
The Report Designer is the main editor for Advanced Reports. At your fingertips is a full suite of reporting and design tools with an interface that is familiar and easy to understand.
The Advanced Report Designer
The Report Designer interface is based on cells, like a spreadsheet. Cells can contain text, images, charts, widgets, and formulas. More importantly, cells can contain multiple rows of data. A cell in a Detail section will expand into as many rows as are needed to show all the data. A cell in a Group section will repeat for every group. And a cell in a Page section will repeat for every page on the report.
A Detail cell repeats for every row in the "Employees" data category
All of this is dynamic, meaning that the report will adapt its look based on the data that is returned. For more information on the different types of report cells, see Sections.
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.
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 icon.
If the report has a custom SQL category then it cannot have any other categories. You can click the SQL icon to edit the SQL statement. See SQL categories (advanced users) for more information.
Data categories are tables of data, which are organized by rows and columns. Columns are also known as data fields. A row of data has entries for one or more columns in the category. When you add a data field onto a report you are seeing the information in one column of data for every row in the category.
For example, a data category for Employees could have columns for the first and last names of each employee, an identification number, and a home phone number. Each row represents a person, and each column contains a specific type of information such as Last Name or Phone Number.
Example of a data category for employee records
You add entire categories at a time to a report, but in the report view you select only the columns you want to see. When you add a data field to the report design, even though you only see one column, the rest of the table is still present behind the scenes. You will never lose the connections between items in each row, and you can always add more fields.
Dragging a category to the Category Name pane
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.
Be judicious when adding data categories. If you find that your report has unexpected duplicate values or empty rows, the cause is most likely that you have a one-to-many join to a category that you are not using.
For example, this report has Employees and Orders categories. There is a one-to-many join from Employees to Orders, indicating that each Employee row is joined to one or more Order rows. Even though we are not using Orders on the report design, there are duplicate Employees because our join setup causes us to have a row for each Order, instead of each Employee.
Unexpected duplicate Employee values
There are several ways to eliminate these duplicates. You can suppress duplicates for the Employees category, which will show blank rows for consecutive duplicates. In the Categories window, select the Suppress Duplicates check box for the Employees category.
Duplicate-suppressed category
You can also suppress duplicates for the cell, which will hide unnecessary duplicate rows. Select the cell and click the Suppress Duplicates icon. If a field from Orders is on the report, the behavior will be the same as suppressing duplicates for the category.
Duplicate-suppressed cell
Or, if you do not think you will need the Orders category, remove it from the report. In the Categories window, click the Delete Category icon next to Orders to remove the category. You can always add it again later if needed.
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 Lower values Higher values
Desc
Date
Asc Past Future
Desc
Text
Asc A Z
Desc
Sorting a category by Department
A report can have multiple sorts. This can be useful when you want your highest precedence sort to affect a data field where the values for multiple rows may be the same.
For example, imagine a large company with many employees. There could be multiple people with the last name Buchanan. With only a sort on LastName, you do not know how all the people with the last name Buchanan will be ordered amongst themselves. If this matters, then you can add a second sort on, say, the FirstName field, so that people with the same last names will be ordered by their first names.
In the Sorts window, add data fields to sort. The precedence of the sorts starts with the highest row and moves down the list. Drag the rows up or down to change the precedence.
Dragging a field to the Sort By pane
Sorts are a prerequisite for making groups. Here's why.
Sorting puts data in order so that data rows which share common values for the sort field are next to each other. This is essentially what grouping does as well. Grouping simply takes those common values, pulls them out of the rows, and makes sections for each group of rows which share that value. Sorts tell the report how you want to your data to be grouped.
Grouping a category by the Department sort
Tip
Set the sort precedence so that nested groups are in order of their grouping level. The outermost group should have the highest precedence, with the next levels following in order. If the precedence is set incorrectly, it could result in inconsistent data groups.</blockquote >
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 Add Formula, then use the Formula Editor to make a composite field to sort on.
Sometimes you may only want to see a portion of rows in a data category, rather than its entirety. Filters allow you to narrow the scope of your reports by restricting the amount of data by specified criteria.
For example, imagine a category containing a row for every single sale your company has ever made. You can use a filter to limit the report to only sales from the past month. This also has the benefit of speeding up your reports.
There are three types of filters available in the Filters window:
Standard filters limit data by only showing rows where the values for a field meet a certain condition.
In the Filters window, add data fields to filter.
Dragging a field to the Filter By pane
For each data field, select a condition. When the report is run, the field value for each row is checked against the condition for that field. Only the rows where the field satisfies the condition will show on the report.
To create a filter condition:
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 |
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.
(v2017.3+) Click the settings icon to select whether to search for values that either Start With or Contain the typed text.
Choosing a filter value
Optional: If you have multiple filters, you can choose how they should be grouped. See Grouping filters together.
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.
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.
When a data row is checked against the report filters, the values for each of its data fields are checked against all of the filters for the data fields. By default, it must satisfy every filter condition to show on the report. The combined statement used to filter the data rows is shown in the Summary field.
Example of a combined filter statement
You can specify that a data row needs only to satisfy one filter, or a selection of filters, to show on the report.
To specify that a data row needs to satisfy any one of several filters:
On the Filters page, drag the filter rows next to each other.
Select the first filter.
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.
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:
On the Filters page, drag the filter rows such that the filters are nearest to their group mates.
Select the first filter in the first group.
Select Group With Next Filter.
You should notice that in the Summary field, there are now parentheses around this filter and the next.
Repeat steps 2-3 for every filter except the last in the first group.
Select the last filter in the first group.
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.
Repeat steps 2-6 for every group of filters. Skip step 6 for the last filter in the last group.
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.
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.
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.
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.
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.
Caution: Top/Bottom filters require the report to have a Detail section. Otherwise the report will return incorrect data.
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:
On the Filters page, click the Top/Bottom tab.
Select the Limit the report to the top or bottom values of a data set check box.
Select either Top or Bottom, for whether you want to show the top or bottom values.
Enter a number for how many values you want to show.
Choose how you want to limit your data:
If you want to show the rows with the top or bottom data values for a field or group, select Values from the list.
If you want to show the groups with the top or bottom summary values for a parent group or the report, select one of the summary calculations, Sum, Avg, Min, Max, Count, or Distinct Count, from the list.
Note. Only numeric data fields support Sum and Avg calculations.
Select the data field or group field to filter from the Of list.
Optional: To show the top or bottom values for each iteration of a group:
Click Add Group.
Select a group field from the For Each list.
The following options are available in the General Options window:
Include Setup Info: Select Top or Bottom to display the data categories, sorts, and filters at either the beginning or end of the report.
Allowed Export Types: Enable or disable output types for the report.
Default Export Type: Specify the default output type for the report.
Report Tree Shortcut: Specify whether the Run icon for the report in the report tree runs the report in the Report Viewer or exports it in the default output type.
Filter Execution Window: Select which type of Filter menu displays when running the report with prompting filters:
Default – Display the default type of filter execution window.
Standard – Display the standard filter execution window.
Simple with Operator – Display a simplified filter execution window that only allows the operator and value to be changed.
Simple without Operator – Display a simplified filter window that only allows the filter value to be changed.
Always Show Filters in Report Viewer: Show the filter menu and allow changes to be made every time the report is run.
No Data Qualify Display Mode: Select what to display if no data qualifies for the report.
Show Message – Display the standard no data qualified message.
Show Report – Display the Page Header, Page Footer, Report Header, and Report Footer sections of the report. Any cells containing Data Fields will not be displayed.
Suppress Formatting: Export only the report data to Excel, without the formatting.
Freeze Rows: Freeze the top number of rows when exporting to Excel, so that the rows stay anchored to the screen when scrolling through the report.
Freeze Columns: Freeze the left number of columns when exporting to Excel, so that the columns stay anchored to the screen when scrolling through the report.
Page Size: Select the page size of the report output.
Page Orientation: Specify whether the report runs in Portrait or Landscape orientation.
Fit to Page Width: Fit the width of the report to the width of the page.
The following options are available in the Report Viewer Options window:
Show Grid: Show or hide grid lines.
Simulate PDF: Have the report appear as if it was on a page.
Allow Hide/Show Columns in Report Viewer: Enable or disable the ability to hide columns.
Show Toolbar in Report Viewer: Select whether to show or hide the interactive toolbar in the Report Viewer.
To allow users to select interactive filters in the Report Viewer, add data fields as filters. For each filter you can select the following options:
Title: Enter text to appear in place of the data field name.
Type: Specify the type of ability the user has to choose filter values:
Single Choice - A drop down menu with all possible filter values. Users can choose one value.
Multiple Choice - A check list with all possible filter values. Users can choose multiple values.
Single Slider - Users can choose one value by sliding a point along a scale.
Range Slider - Users can choose multiple values between two points on a scale.
Value Sort Direction: Whether the filter values should display in ascending or descending order.
Filter Value Format: Select how the filter values should display.
Initially Display Filter on Panel: Display the filter choice automatically when the report is run.
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.
You can use reports to dynamically fill out fields in templates and forms.
Template report with repeating data fills a set of form templates
To use a report to fill out a template:
Enter field data in cells on the report. For repeating data:
Repeating cells mapped to static template fields make a new instance of the template for each repeat.
Repeating cells mapped to limited-repeating template fields make a new instance of the template each time the number of values exceeds the limit. This is often undesirable - ensure that there are appropriate constraints on your data and enough fields to fit it all.
Repeating cells mapped to unlimited-repeating template fields never make a new instance of the template. Overflowing data is cut off.
From the Report Options >
Template window, select an existing template or click the Upload Template
icon and add a new one.
For each template field, select either:
A report cell containing text or data. Images, visualizations, and other widgets are not supported.
Leave as is - Any bookmark text shows as-is in the output
Nothing - The field is blank
Click OK.
To remove a template from a report:
From the Template window, select the blank option from the template list.
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 theReport Options > General Options window.
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 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:
Open a PDF in a PDF editor program, such as Adobe Acrobat or PDFescape.
Add form fields where you will insert report data. For fields where text may span multiple lines, select the multi-line property.
Give each form field a unique name, as follows:
For static fields, which appear only once per template instance, use any name, with the exception of the format reserved for repeating fields.
For repeating fields, which are mapped to consecutive values in a repeating cell, use the following naming format:
Name.0, Name.1,...
Where Name
is shared by the repeating fields, and 0
maps to the first value, 1
maps to the next value, and so on.
Save the PDF. Then upload it to the report.
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:
Open a Word file in Microsoft Word or a compatible document editor.
Add text where you will insert report data.
Select the text and add a bookmark.
Give each bookmark a unique name, as follows:
For static fields, which appear only once per template instance, use any name, with the exception of the formats reserved for repeating and conditional fields.
For limited-repeating fields, which are mapped to consecutive values in a repeating cell, use the following naming format:
Name_0, Name_1,...
Where Name
is shared by the repeating fields, and 0
maps to the first value, 1
maps to the next value, and so on.
For unlimited-repeating fields, which are mapped to all the values in a repeating cell, use the following naming format:
RepeatForEach_Name
Where Name
is a unique name.
Optional: To conditionally show or hide text:
Select the text and add a bookmark with the following naming format:
KeepIF_Name
Where Name
is a unique name.
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 )
Save the file. Then upload it to the report.
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:
Open an Excel file in Microsoft Excel or a compatible spreadsheet editor.
The first worksheet is used for dynamic report data. Repeating cells are mapped to columns in the worksheet. For each column where you will add report data, enter a unique name to the topmost cell in the column. All following cells must be empty.
Formatting an Excel file to be used as a template
Save the file. Then upload it to the report.
Caution
The Joins window is recommended for advanced users only.
Joins describe how the categories on a report are related to each other. When two categories are joined, a field in the first category is associated with a field in the second category. Wherever a value in the first category's field matches a value in the second category's field, that value's rows from each category come together to form a composite row. The table produced by all the composite rows is the resulting data that appears on the report.
For example, take the following categories, Orders and Products. The Orders.ProductId field corresponds with the Products.Id field. When the categories are joined from Orders.ProductId to Products.Id, the rows are connected wherever those two fields have matching values.
Categories joined on Orders.ProductId >> Products.Id
The result of this join is the following composite rows. These categories have a one-to-one relationship, because each row in the "left" category joins at most one row in the "right" category.
Joined categories. Products.Id is omitted.
Tip
Categories could be joined along more than one set of fields; composite rows are formed only when all sets have matching values.
For two categories to be copresent on a report, there must be a join path between them. They are either directly joined, or there is a path through one or more intermediate categories. You do not have to configure joins manually - they already exist in the environment. However, if you want to learn how to add or adjust joins on a per-report basis, this topic will explain the options that are available.
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 Report Options >
Advanced >
Joins window, select the Products data that does not have Orders data check box.
Left outer join
This changes the join between these categories to a left outer join, because all rows from the left category are included. The following rows result:
Joined categories with all Products rows. Products.Id is omitted.
Similarly, selecting the Orders data that does not have Products data check box changes the join to a right outer join, which includes all rows from the right category. Selecting both check boxes includes all rows from both categories; this is a full outer join.
There are two types of join relationships: one-to-one and one-to-many.
In the previous example, the relationship between the categories is one-to-one, because each row in the left category joins at most one row in the right category. Some categories have a one-to-many relationship, where each row in the left category joins zero or more rows in the right category.
A one-to-many relationship from categories X to Y is represented in the following diagram:
Each X is joined to one or more Y
Reports with a single one-to-many join are well suited to grouping by the left category. The data in these reports is generally well-formed and understandable.
However, when a report has multiple categories with one-to-many joins, data can appear more disorganized and confusing. For example, the following diagram represents data from three categories, X, Y, and Z, where the relationships between X - Y and X - Z are both one-to-many:
Each X has 1 or more Y, and 1 or more Z
Because Y and Z are not directly related to each other, there are many rows with only Y or only Z. This can cause the report to be significantly larger, and to be difficult to read and interpret. This occurs even with inner joins, the most restrictive type, because by default there is no logic that deals with the relationship between Y and Z.
Read on for different ways of improving the structure of a report with multiple one-to-many joins.
You could fill the blank spaces with supplementary data by disabling Special Cartesian Processing from the Joins window. Blank cells are filled in with data that is repeated directly from the previous row. The following diagram demonstrates how this works:
Disabling Special Cartesian Processing
The shaded cells represent data that has been repeated from the previous row. This can make the report more readable. However, this approach poses a problem: Blank cells indicate a lack of a relationship between two fields, so filling in these spaces with artificial data can obfuscate any relationship between Y and Z. This can decrease the accuracy of the report.
There are better ways to improve the readability of such a report without sacrificing accuracy:
Use repeating groups to show the X - Y and X - Z relationships in entirely separate sections. This is suitable if any relationship between Y and Z is irrelevant or nonexistent. See Sections for more information.
Hide some or all of the rows which do not have data for both Y and Z. This is suitable if you want to highlight an implicit or indirect relationship between Y and Z. This is done by imposing Must constraints. Read on for more information.
Although Y and Z are not directly joined, they are both related to X, so there is an implicit relationship between them. If you examine Y and Z alone, you will notice that they technically exhibit a full outer join.
Y and Z, without X
Because all the rows from Y and Z that relate to X are shown, there are rows with both Y and Z, or with only one of either. Must constraints allow you to change the implicit join type, and in doing so, eliminate rows that lack data from one or both categories.
To set Must constraints, from the Joins window, locate the MUST panel for the applicable categories:
Setting Must constraints
Do one of the following:
Select the Y check box - Left outer join: Any rows without Z are removed
Select the Z check box - Right outer join: Any rows without Y are removed
Select the Y and Z check boxes - Full outer join: Any rows without Y or Z are removed
Effect of setting various Must constraints
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:
Click Add.
Click 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.
Click OK.
To remove conditions, click the Delete icon next to the condition to delete.
To modify a join's fields:
Click the Edit icon next to the join to edit.
Add, remove, or modify conditions.
Click OK.
To remove a join, click the Delete icon next to the join to delete, then click OK.
To restore the default joins, click Recreate, then click OK.
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.
Instead of joining between two columns, one or both sides of the join may instead be an arbitrary expression, constant, or SQL sub-query that you specify.
To change the expression type for one side of a join condition, select one of the following from the Type list:
Value: One or more constant values separated by commas
Expression: Formula or calculation
SubQuery: SQL query
Then enter the value in the Value field.
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:
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.
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.
If a cell has a numeric, date, or time value, then you can use Number formatting to choose how the value should appear on the report. For example, you could add a dollar sign ($) to monetary values and separate each three digits to make values easier to read.
Numeric values with currency styled formatting
The following options for Number formatting are available:
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.
Format the data as a number, currency, or percentage.
Optional: Choose how the number displays:
In the Decimal Places field, enter a number for how many decimal places to display. Then, in the field to the right, enter a symbol to use as the decimal mark.
To show a delimiter every three digits, select Use 1000 Separator. Then, in the field to the right, enter a symbol to use as the delimiter.
To show a currency symbol before the number, select Use Currency Symbol. Then, in the field to the right, enter the symbol to show.
To show a percent sign (%) after the number, select Append Percent Sign.
To show no value if the number is 0, select Blank When Zero.
To show a minus sign (-) in front of negative numbers, select Show Negative Symbol.
To show parentheses ( ) around negative numbers, select Show Parenthesis.
To show negative numbers in a different color, enter a color code in the Color field or use the color picker to choose a color.
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 |
Do not apply any formatting to the data, and show it exactly as it appears in the database.
Alter the width and color of the cell borders. To set a color for a cell border, enter a color code or select a color from the picker. To set the width of the border, enter a pixel value, or use the arrows to make the border thicker or thinner.
To set all the cell borders to the same color and width, select Make Borders Uniform.
Tip
If gridlines are enabled for the Report Viewer, then cell borders will show in addition to the gridlines.
Choosing border colors and widths
A conditional format allows you to format a cell according to its output data. The cell and text styles can depend on its data value, and you can even conditionally hide rows or entire sections. This can be useful for highlighting certain values in a data set, such as outliers from a trend.
Conditional formatting uses a formula to set the condition. The formula must evaluate to True or False: If True, the formatting will be applied, and otherwise it will not. Conditional formulas are often based on data in the cell, but they can also be based on other cells, data fields, or other information about the report.
Example of a formula that evaluates to True or False
To set or modify the format of a cell based on a conditional formula:
Click Add to create a new condition.
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.
Click the formula 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 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 and Move Row Down
icons to reorder the precedence of the conditions.
A cell with multiple conditional formats
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.
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.
A report can contain any variety of sections desired. To add, remove, or change sections, click a section title, to the left of the row numbers in the report grid. The section menu will appear.
The section menu
To add a section, hover over 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 Modify Section.
To change the order of sections, click Move Section Up or
Move Section Down.
To delete a section, click Delete Section. This will delete every row in the section.
To add alternating background colors for each row in a section, click Section Shading. See Section Shading for details.
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.
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 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.
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.
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.
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.
Position of Group sections
Group sections are the primary way in which data is grouped into "buckets" that each share a unique attribute. Detail rows are arranged into the groups they belong to. The common attribute is defined by a preexisting sort. In order to make a group, first add a sort in the Sort window.
For example, if you wanted to group a set of rows by each Product, first add a sort on the Products.ProductName field.
A sort is required for a group
The main difference between the header and footer is that the data comes after the header, but before the footer. This means that you can only use aggregate formulas in the group footer, since the header is not aware of the data in its group.
When you add a group section, you are asked which sort to base the group on. For each sort, you can choose to group on either the sort field or the sort category.
If you group on a field, the group breaks at each new value of the field.
If you group on a category, the group breaks at each new instance of the category's unique identifier.
The group window appears when adding a group section
For example, instead of grouping by each Product, you may want to instead group by product quantity, so that products that are low or out of stock are grouped together. First add a sort on the Products.Quantity field, then group by that field.
You can have multiple group sections, if you wanted to add additional levels of stratification to the report. Each group requires a corresponding sort.
Tip
The order of the sorts has an effect on the way the data is arranged. Data is sorted by the first field, then any ambiguities are sorted by the second field, then any remaining ambiguities are sorted by the third, and so on. In general, you should order group headers by their associated sorts, and group footers in inverse order.
Position of Repeating Group sections
Repeating groups are a way to organize and display data which contains multiple one-to-many relationships. Each repeating group has data from a "many" category, grouped by the "one" category. The following diagrams demonstrate how this works.
Two categories, X and Y, with a one-to-many relationship from X to Y, will display like so in a Detail section.
Tip
Category X has duplicates suppressed - this is similar to grouping on X.
Each X has 1 or more Y
If there is a third category, Z, with a one-to-many relationship from X to Z, there is not necessarily a relationship between Y and Z. So there may be blank rows with only Y or only Z.
Each X has 1 or more Y, and 1 or more Z
There are two potential solutions. If there is a relationship between Y and Z, then you can use Advanced Joins to impose additional constraints so that only the rows with both Y and Z are shown. For more information, see Joins.
On the other hand, there may be no relationship between Y and Z - only between X and Y, X and Z. Or there may be a relationship, but it does not matter for this data set. You can use repeating groups to ignore that relationship and simply organize the data by X instead.
To do so, create two repeating groups for X, and put Y in the first, and Z in the second:
Organizing Y and Z into repeating groups for X
Each X repeats twice: once for Y, once for Z
Each repeating group section has its own group header, detail, and group footer. These follow the same principles as their general purpose equivalents, but within the repeating groups. For example, to calculate aggregate data for Y for each X, use an aggregate formula in the footer for the repeating group with the Y data.
Section shading 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:
Click a section title to open a menu for that section. Select Section Shading.
Click New to add a new shading color.
For each shading color, select a color with the color picker, or enter a color code.
Use the up and down
arrow icons to rearrange the order of the colors.
Click OK.
Adding two alternating background colors to a Detail section
Tip
Section shading is overridden by the cell background color and by conditional formatting.
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.
The report can be saved by clicking the save button (). The report will also be saved anytime it is executed.
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.
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.
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.
The Number Tab allows you to set the format of numbers and dates.
The Border Tab allows you to alter the width and color of the cell edges.
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’.
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.
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 (
).
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 ().
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.
In the design grid, you can:
Sections dictate how the data appears in a report. There are five types of sections: page, report, details, group, and repeating group.
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.
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.
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/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 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.
Sections can be added, deleted, modified, moved, and assigned shading.
Columns and rows of cells can be added, modified, or removed as described below.
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.
Cells are the containers for all the information in a report. Cells may contain text, images, charts, or links to other reports.
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.
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.
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.
Linked reports allow you to add custom drilldowns to cells or charts. Drilldowns are a means of exploring data points by "drilling down" into their background data.
Tip
Drilldowns work in the Report Viewer and Dashboard Viewer, not in exported reports.
Drilling down into a cell and a chart
Drilldowns require you to have a child report, which is an Advanced or Express report that contains the background data for the parent report. You can create a drilldown on a Dynamic cell or a chart by linking the child report to the parent report cell. Each data value, or each chart series, when clicked, filters the child report by its respective value before opening the resulting report in a window at the cursor.
Since drilldowns are themselves reports, they can have interactive sorts and filters, dynamic visualizations, they can be exported, and they can even have their own drilldowns.
By default, the data category that corresponds to the linking cell filters the closest joined category on the linked report. See Joins for more information. If there is no join path, then you must set the linked fields manually in the Fields page.
For example, a parent report links an Employees data field to a child report with an Orders category. The two categories are joined on Orders.EmployeeId >> Employees.Id
. For each Employees row, its Employee.Id value filters the linked report down to the Order rows with matching Orders.EmployeeId values.
Orders linked report filtered by Employee Id
Tip
Filters on the parent report or containing dashboard do not cascade down to linked reports. Linking filters do not cascade down to grand-children, or child reports of the linked report.
To add a linked report drilldown:
Make an Advanced or Express report that contains the drilldown data. This will be the linked child report.
Linked reports typically open in a small window, so the child report should be simple and concise. Avoid large fonts, too much static content, or making it too large in size. You should also set the General Option for No Data Qualify Display Mode to Show Report, to show an empty drilldown instead of a popup window for links with no data.
In the parent report, select the cell to link, then click the Link Reports icon. The cell can contain a data field, formula, or a column-based or row-based chart.
Select the child report. Then click OK.
Caution
Because the linked report is a separate report from the parent, if you move the linked report to another folder, or remove the linked category, the link will be lost. You will have to edit the parent report and add it again.
To remove a linked report drilldown:
Click the link icon, or select the cell and click the Link Reports
icon.
Click the Remove Link icon.
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.
You may want to link on different fields or categories than the default join. The Fields page allows you to specify which categories and fields are used to determine the drilldown data.
The Fields page is suitable for the following situations, among others:
The default join is not the link you want to use
Example: Linking on related fields other than the Id field, such as "Region"
Employees.Region >> Orders.ShipRegion
No join exists between the From and To categories
Example: Categories have related fields but are not joined, such as Orders and Suppliers
Orders.ShipCity >> Suppliers.City
The From and To categories are the same
Example: Fields are related to other fields in the category, such as Employee X supervises Employees Y and Z
Employees.Id >> Employees.ReportsTo
Tip
The From fields from the parent report filter the To fields on the child report.
To specify the linked fields:
Select the From Category and To Category from their respective lists.
Click 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.
For each set of linked fields, select the From Field and To Field.
When finished, click OK.
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.
Add a Data Field by dragging and dropping it into the 'Formula' box or double-clicking it. Or enter it manually using the following format: {DataCategory.DataField}.
Caution
Linked report formulas support only one data field. If multiple data fields are used, all but the first will be ignored.
Add a Parameter by entering it manually using the following format: @ParameterName@.
Add a function by dragging and dropping it into the 'Formula' box or double-clicking it. Or enter it manually.
When you run a Dashboard, the output is shown in a new tab called the Dashboard Viewer. Reports and charts on the dashboard are interactive, as are any embedded web pages. The dashboard refreshes periodically, or you can refresh it manually. You can also filter the dashboard with the filter sidebar or with filters on the dashboard design itself.
Viewing an interactive dashboard
To run a dashboard:
If you are in the Dashboard Designer, click Run Dashboard.
Tip: The Run Dashboard button also saves the dashboard.
If you are in the folder tree, click the Run icon, or click the Menu
icon and select
Run Report.
The Dashboard Viewer opens in a new tab, indicated by the viewer 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.
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 or down
icons to search for the text in the report. Page through the report by using the navigation icons
.
Use the Show Chart and
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:
Select Expand to show a report in the full screen. Select Collapse to return to the dashboard.
Select Refresh Report to reload the data in the report.
Click the refresh 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 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 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.
Dashboards are a way to combine a several related reports into one unified viewing space. You can add preexisting reports to a dashboard, but you can also create new ExpressViews and Visualizations directly in the dashboard itself. You can also add images, text, and embed other web sites side by side with your data. And you can add interactive filters which can work on multiple reports all at once.
The Dashboard Designer
Double-click a dashboard to open it in the Dashboard Designer, or click the Menu icon and select
Edit.
For more information about the types of content that can be added to a dashboard, see the following links:
Each element on a dashboard is a rectangular tile that can be resized and dragged to the proper location. Tiles are arranged onto a grid, and they will snap into place next to each other.
Adding tiles
To add a new tile to the dashboard, drag the New Tile icon onto the dashboard grid. You can drag to an empty location to fill the space, to the side of an empty location to take up a portion of the space, or over another tile to place it adjacent and resize the other tile to fit. You can then drag the resizing handles for fine grained control over the tile size.
A new tile placeholder will be added. Select what type of content should be on the tile:
New Visualization
Create a new ExpressView visualization right in the tile. Choose between a chart or a tabular ExpressView. See ExpressView Visualizations for more information.
URL
Embed another web page inside the dashboard. Enter the URL, or web address, to the web page.
Caution: Some web pages may not be embeddable.
Image
Upload an image from your computer. Drag the image onto the tile or click browse your files and locate the image.
Text
Enter text into a field that can be formatted and styled.
Filter
Add several styles of interactive filters, which can affect multiple reports on the dashboard. See Interactive Filters for more information.
Existing Report
Drag an existing report onto the tile. See Adding Reports for more information.
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 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
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 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.
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 icon to access some additional options for managing tiles.
You can Delete a tile from the dashboard. You can Copy a tile then Paste it next to another. You can Expand a tile to temporarily fill the screen, then Collapse it back into place. And you can Refresh the data in Reports and Visualizations.
ExpressView visualizations can be saved as new ExpressViews by clicking Save as ExpressView. You will be asked to name the report, then taken into the ExpressView Designer.
Tile style
Select a tile, then click the Style tab to access the options for changing its appearance.
Click Tile Fit to choose how the tile scales to fit the screen. This option is not available for Filters or ExpressView Visualizations.
Click Tile Header to add header text to the tile. Then choose the text font and formatting.
Click Background and Border to change the background color of the tile and tile header, and the border color and style.
More information
See Adding Reports, ExpressView Visualizations, and Interactive Filters for more information about their unique features and options.
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.
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 Filters or
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 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.
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.
You can create tabular reports and visualizations directly on the dashboard with ExpressView Visualizations. To do so, either:
Drag the New Tile icon onto the dashboard and select Visualization.
Drag a field from the Data Pane directly onto the dashboard.
A KPI chart is the default visualization for a single data field. To add data fields, drag fields from the Data Pane onto the visualization. To swap to a tabular report, click the Show Table
icon. To swap back to a chart, click the Show Chart
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 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.
Interactive filters are filters that appear as tiles directly on the dashboard itself. Filter tiles have several different styles to choose between, and can resize and scale just like any other tile. Filters can apply to as many or few reports as needed.
Type
You can choose between four styles of filters and two orientations:
Single Choice - A drop down menu with all possible filter values. Users can choose one value.
Multiple Choice - A check list with all possible filter values. Users can choose multiple values.
Single Slider - Users can choose one value by sliding a point along a scale.
Range Slider - Users can choose multiple values between two points on a scale.
Select whether the filter values are oriented horizontally or vertically.
Reports
Select which reports the filter applies to. Only reports that include the filter data field are valid.
Data
Select which data field the filter applies to. The filter is automatically populated with values from the data field. You can select which value or values are selected by default when the dashboard is run. Select Prompt for value to prompt users to enter a value when running the dashboard.
Text and Format
Choose how the filter value labels are styled and formatted.
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.
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 ().
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.
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.
A Data Field is a single attribute within a category. E.g., Orders.OrderID is numeric value that identifies a specific order.
In the Sorts Tab, specify which Data Fields will be used to determine the order of data on the report.
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.
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.
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.
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.
To display information on the top of each page, click 'Page Header' below the 'Summarize By' box. A Page Header Menu will appear.
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.
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.
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.
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.
The Options Tab allows you to control various report option settings.
General Export Options
HTML Options
Excel Options
Page Options
In the Advanced Options, an Express Report can be converted to an Advanced Report.
IMPORTANT. This CANNOT be undone.
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.
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 ().
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.
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.
A Data Field is a single attribute within a category. E.g., Students.ID is the numeric value that identifies a specific student.
In the Sorts Tab, specify which Data Fields will be used to determine the order of data on the report.
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.
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.
Using the ‘Summarize By’ box, you can display subtotals, grand totals, or headers for each unique value of a Data Field.
Summary Functions:
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.
To display information on the top of each page, click ‘Page Header’ below the ‘Summarize By’ box. A Page Header Menu will appear.
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.
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 ().
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.
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 ().
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.
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.
A Data Field is a single attribute within a category. E.g., Students.ID is the numeric value that identifies a specific student.
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.
In the Layout Tab, design the CrossTab by moving Data Fields into the Row Header, Column Header, and Tabulation Data panels.
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 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 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.
The Theme dropdown can be used to quickly style the CrossTab using a predefined theme. Further styling can be done in the Report Designer.
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:
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 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.
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:
Click the New Report icon and select
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:
\ / : * ? " < > |
On the Reports page, add the reports to include in the Chained Report. Supported report types are Advanced Reports, Express Reports, Crosstab Reports, and ExpressViews.
The order of the reports in the list is the order they will appear in the output. Click the Up or Down
arrows to move a report up or down in the list. Click the Delete
icon to remove a report from the list.
Click the Save icon to save the Chained Report.
Note about export types
If one of the reports cannot export to a certain file type, then the Chained Report cannot export to that type. All of the reports must share at least one export type, or else the Chained Report will have no compatible type to run as. Supported types are PDF, Word (RTF), CSV, and Excel (XLS).
Reports with templates
Template reports can be added to a Chained Report. The Chained Report must be exported to the same file type as the template. Multiple templates of the same file type can be chained together, with the exception of Microsoft Word based templates.
If one or more reports has prompting filters or parameters, then you can set rules for what should happen when the Chained Report is run.
To edit a report's prompting options:
On the Reports page, click the Report Options icon.
For each prompting filter and parameter, the following options are available:
Common Prompt
All reports which contain this filter field or parameter will use the specified value. In the Data (Prompt Text or Value) field, enter the text to prompt the user for a value.
Report Prompt
Only this report will use the specified value. In the Data (Prompt Text or Value) field, enter the text to prompt the user for a value.
Assign Value
The Chained Report will not prompt for a value for this filter or parameter. In the Data (Prompt Text or Value) field, enter the value to use.
Tip: Values for prompting filters and parameters cannot be set when scheduling a Chained Report. If a Chained Report is intended to be scheduled, use the Assign Value option to specify values.
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.
Collation breaks up the reports in the Chained Report by a specified sort field, and then groups together the reports by each instance of the sort. Essentially, this turns a sort field into a common grouping for the reports, working in much the same way a group section would. This can allow you to use individual reports as pieces in a composite report.
Order of reports in a collated Chained Report
Report 1, filtered by group 1
Report 2, filtered by group 1
Report 1, filtered by group 2
Report 2, filtered by group 2
and so on.
Some common uses for Chained Report collation include:
Grouping disparate reports by order number, or employee number, or something else in common
Combining mostly identical reports, only with different filters or joins, into a composite report
Making a composite report with multiples of a feature which normal reports can only have one of, such as a detail section
Caution: Collation is recommended for advanced users only.
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. 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.
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, or double-click it, or select it and press .
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.
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:
Summary Functions |
|
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.
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 Finish to see the completed map on the report design grid.
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.
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 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 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 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 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:
(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. (Column and Stacked Column charts are not compatible with each other). Combination charts can have up to two Y-axes.
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.
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):
The Appearance Tab contains options for customizing how the chart will look.
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.
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:
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.
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:
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 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.
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.
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.
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.
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.
In this example, the label field is in cell A3, and the three value fields are in cells B3, C3, and D3, respectively.
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.
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.
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.)
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.
In the Type Tab, select the initial view, size, colors, and where to display the legend.
In the Locations Tab, specify which geographic locations should display on the map.
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:
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:
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.
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.
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.
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.
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:
Double-click a chart to open the chart wizard. Click the Appearance tab.
Click Conditional Colors.
Click Add to add a condition. Choose a color by entering a hex value, or using the color picker.
Click the formula icon to set the conditional formula.
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.
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.
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.
List of functions that match the text
Tip
You can search for data fields, report cells, and parameters in the same manner.
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.
An argument is a value that a function uses to do a calculation. Functions have different amounts and types of arguments. When a function is first entered into the Formula field, there are placeholder values for each required argument. Click on a placeholder to see the description of the argument.
Description of the condition argument of the If function
Some arguments are optional. Those are surrounded by brackets [ ]. Some arguments are a list of values. Those are followed by an ellipsis (...).
The Today function takes an optional argument. The And function takes a list of arguments.
Tip
Some functions take no arguments. These are formatted with empty parentheses:Function()
You need to supply values for all of the function's required arguments. Type a value into the argument space, or drag a data field or function over the placeholder.
Dragging a data field to an argument
Click OK when you are finished.
Formulas are used in several areas besides the report design: custom sorts and groups, drilldowns, conditional formatting for cells and charts, and custom crosstab fields, to name a few. In every area where you can use a formula you can click on a formula 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.
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.
You have the option of typing in your functions, data fields, parameters, and cell references manually. Use the following formatting guidelines.
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.
{DataCategory.DataField}
where DataCategory is the name of the data category
and DataField is the name of the data field
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 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.
If there are any typos in the function, the Formula Editor will underline the relevant section in red, and show a brief description of the problem. You need to fix the problem before running the report or the result of the formula will be an error.
This formula will not work without a concatenation operator "&"
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’.
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.’
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.
To modify the sort criteria of a report click ‘Sorts’ in the Toolbar drop-down menu.
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.
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 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 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.
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.
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.
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.
If you have been given permissions, additional options are available in the Advanced Menu.
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.
If given permission by your administrator the User Preferences button will appear in the top right corner. Click the User Preferences button () to open the User Preferences menu.
In the User Preferences menu set your preferences such as which reports should run at startup and/or what User Reports should be applied.
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.