Report Designer

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

The Advanced Report Designer

Cells

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

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


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


Toolbar


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

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

Rename

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



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

 



Description

 

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

 

The description of a report

 

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

 

Filter description

 

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

  

Categories

 

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

 

What are data categories?

 

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

 

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

 

Example of a data category for employee records

 

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

 

Dragging a category to the Category Name pane

 

Relationships between categories (advanced users)

 

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

 

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

 

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

 

Suppressing duplicates (advanced users)

 

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

 

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

 

Unexpected duplicate Employee values

 

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

 

Duplicate-suppressed category

 

You can also suppress duplicates for the cell, which will hide unnecessary duplicate rows. Select the cell and click the 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.

  

Sorts

 

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

 

Numeric

 

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

 

Adding sorts

 

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

 

Dragging a field to the Sort By pane

 

Relationship between sorts and groups

 

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

 

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

 

Grouping a category by the Department sort

 



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

 


Sort formulas (advanced users) 

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

 

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

 

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

 

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

 

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

 

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

 

Sorting by aggregates (advanced users)

 

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

 

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

 

To sort by a summary calculation:

 

  1. Ensure that your report has the appropriate aggregate formula in a group footer cell. The cell should return a numeric value in the report output, which you want to sort the groups by.
  2. From the  Report Options menu, click  Filters. Then click the Top/Bottom tab.
  3. Select the Limit the report to the top or bottom values of a defined dataset check box.
  4. From the Top/Bottom list, select Bottom to sort in ascending order, or Top to sort in descending order.
  5. In the # field, type 2147483647.
  6. Why this number? We cannot enter "infinity", so instead we want to enter an arbitrarily large number like 99999. This is the largest number that can fit without causing a report error.
  7. From the Value list, select the group footer cell with the aggregate formula.
  8. If there is a For Each group, click to remove it.

 

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

   

Filters

 

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

 

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

 

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

  

Standard

 

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

 

Adding filters

 

In the Filters window, add data fields to filter.

 

Dragging a field to the Filter By pane

 

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

 

To create a filter condition:

 

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.

 

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

 

Condition
Type
Filter Value(s)
Matching Value(s)
Not Matching Value(s)
Equal To (=)
Any
July 4, 2016
July 4, 2016
July 4, 2016 12:00 AM
July 4
July 4, 2016 2:00 PM
Less Than (<)
Number, Date/Time
July 4, 2016
July 3, 2016
July 5, 2016
Greater Than (>)
Number, Date/Time
July 4, 2016
July 5, 2016
July 4, 2016 5:00 PM
July 3, 2016
Starts With
Text, Number
203
2035550224
8458081120
Ends With
Text, Number
224
2035550224
7188044606
Contains
Text, Number
555
2035550224
2038081120
Between
Any
1, 4
1, 1.5, 3, 4
0.999, 5
One Of
Any
1, 2, 3, 4
1, 2, 3, 4
1.1, 9

 

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

 

Choosing a filter value

 

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

 

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

 

Prompting for values

 

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

 

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

 

Grouping filters together

 

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

  

Example of a combined filter statement

 

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

 

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

 

  1. On the Filters page, drag the filter rows next to each other.
  2. Select the first filter.
  3. Select OR With Next Filter.
  4. You should notice that in the Summary field, the And between this filter and the next has changed to Or.
  5. Repeat steps 2-3 for every filter except the last.

 

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

 

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

 

  1. On the Filters page, drag the filter rows such that the filters are nearest to their group mates.
  2. Select the first filter in the first group.
  3. Select Group With Next Filter.
  4. You should notice that in the Summary field, there are now parentheses around this filter and the next.
  5. Repeat steps 2-3 for every filter except the last in the first group.
  6. Select the last filter in the first group.
  7. Select OR With Next Filter.
  8. You should notice that in the Summary field, the And between this filter and the next has changed to Or.
  9. Repeat steps 2-6 for every group of filters. Skip step 6 for the last filter in the last group.

 

Nesting filter groups (advanced users)

 

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

 

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



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


  

Group Min/Max

 

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

 

Entire Data Set

 

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

 

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

 

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

 

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

 

Min/Max for each Group

 

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

 

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

 

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

 

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

 

Min/Max with Multiple Groups

 

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

 

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

 

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

 

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

  

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

 

Top/Bottom

 

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

 

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

 

  1. In the Filters window, click the Top/Bottom tab.
  2. Select the Limit the report to the top or bottom values of a defined dataset check box.
  3. From the Top/Bottom list, select whether you want to show the top or bottom values.
  4. In the # field, enter a number for the maximum number of values to show.
  5. From the Value list, select a cell to filter by.
    1. Choose a Detail cell to show only rows with the top or bottom values in a field.
    2. Choose a Group cell to show only groups with the top or bottom values in the report.
  6. Optional: To show the top or bottom values for each of a group:
    1. Click  Add Group.
    2. Select a group field from the For Each list.

 



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

 


 

General Options

 

The following options are available in the General page:

 

General Options

 

  • Allow Execution in Viewer: Allow or prevent the report from being run in the Report Viewer.
  • Include Setup Info: Select Top or Bottom to display the data categories, sorts, and filters at either the beginning or end of the report.
  • Allowed Export Types: Enable or disable output types for the report.
  • Default Export Type: Specify the default output type for the report.
  • Report Tree Shortcut: Specify whether the Run icon for the report in the report tree runs the report in the Report Viewer or exports it in the default output type.
  • Filter Execution Window: Select which type of Filter menu displays when running the report with prompting filters:
    • Default – Display the default type of filter execution window.
    • Standard – Display the standard filter execution window.
    • Simple with Operator – Display a simplified filter execution window that only allows the operator and value to be changed.
    • Simple without Operator – Display a simplified filter window that only allows the filter value to be changed.
  • Always Show Filters in Report Viewer: Show the filter menu and allow changes to be made every time the report is run.
  • No Data Qualify Display Mode: Select what to display if no data qualifies for the report.
    • Show Message – Display the standard no data qualified message.
    • Show Report – Display the Page Header, Page Footer, Report Header, and Report Footer sections of the report. Any cells containing Data Fields will not be displayed.

 

Excel Options

 

  • Suppress Formatting: Export only the report data to Excel, without the formatting.
  • Freeze Rows: Freeze the top number of rows when exporting to Excel, so that the rows stay anchored to the screen when scrolling through the report.
  • Freeze Columns: Freeze the left number of columns when exporting to Excel, so that the columns stay anchored to the screen when scrolling through the report.

 

Page Options

  • Page Size: Select the page size of the report output.
  • Page Orientation: Specify whether the report runs in Portrait or Landscape orientation.
  • Fit to Page Width: Fit the width of the report to the width of the page.

   

Report Viewer Options

 

The following options are available in the Report Viewer page:

 

General

  • Show Grid: Show or hide grid lines.
  • Simulate PDF: Have the report appear as if it was on a page.
  • Allow Hide/Show Columns in Report Viewer: Enable or disable the ability to hide columns.
  • Show Toolbar in Report Viewer: Select whether to show or hide the interactive toolbar in the Report Viewer.

 

Filters

 

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

 

  • Title: Enter text to appear in place of the data field name.
  • Type: Specify the type of ability the user has to choose filter values:
    • Single Choice - A drop down menu with all possible filter values. Users can choose one value.
    • Multiple Choice - A check list with all possible filter values. Users can choose multiple values.
    • Single Slider - Users can choose one value by sliding a point along a scale.
    • Range Slider - Users can choose multiple values between two points on a scale.
  • Value Sort Direction: Whether the filter values should display in ascending or descending order.
  • Filter Value Format: Select how the filter values should display.
  • Initially Display Filter on Panel: Display the filter choice automatically when the report is run.

 

Sorts

 

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

 

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

  

Template

 

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

 

Template report with repeating data fills a set of form templates

 

To use a report to fill out a template:

 

  1. Enter field data in cells on the report. For repeating data:
    • Repeating cells mapped to static template fields make a new instance of the template for each repeat.
    • Repeating cells mapped to limited-repeating template fields make a new instance of the template each time the number of values exceeds the limit. This is often undesirable - ensure that there are appropriate constraints on your data and enough fields to fit it all.
    • Repeating cells mapped to unlimited-repeating template fields never make a new instance of the template. Overflowing data is cut off.
  2. From the Report Options > Template window, select an existing template or click the Upload Template  icon and add a new one.
  3. For each template field, select either:
    • A report cell containing text or data. Images, visualizations, and other widgets are not supported.
    • Leave as is - Any bookmark text shows as-is in the output
    • Nothing - The field is blank
  4. Click OK.

To remove a template from a report:

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



Tip

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


  

 

Making templates

 

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

 

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

  

PDF Templates

 

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

 

To make a PDF template:

 

  1. Open a PDF in a PDF editor program, such as Adobe Acrobat or PDFescape.
  2. Add form fields where you will insert report data. For fields where text may span multiple lines, select the multi-line property.
  3. Give each form field a unique name, as follows:
    • For static fields, which appear only once per template instance, use any name, with the exception of the format reserved for repeating fields.
    • For repeating fields, which are mapped to consecutive values in a repeating cell, use the following naming format:
    • Name.0, Name.1,
    • Where Name is shared by the repeating fields, and 0 maps to the first value, 1 maps to the next value, and so on.
  4. Save the PDF. Then upload it to the report.

 

Microsoft Word Templates

 

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

 


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


 

To make a Word template:

 

  1. Open a Word file in Microsoft Word or a compatible document editor.
  2. Add text where you will insert report data.
  3. Select the text and add a bookmark.
  4. Give each bookmark a unique name, as follows:
    • For static fields, which appear only once per template instance, use any name, with the exception of the formats reserved for repeating and conditional fields.
    • For limited-repeating fields, which are mapped to consecutive values in a repeating cell, use the following naming format:
    • Name_0, Name_1,
    • Where Name is shared by the repeating fields, and 0 maps to the first value, 1 maps to the next value, and so on.
    • For unlimited-repeating fields, which are mapped to all the values in a repeating cell, use the following naming format:
    • RepeatForEach_Name
    • Where Name is a unique name.
  5. Optional: To conditionally show or hide text:
    • 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 )
  6. Save the file. Then upload it to the report.

 

Microsoft Excel Templates

 

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

 

To make an Excel template:

 

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.