Exago Logo
Search
Generic filters
Exact matches only

Building Your First Report

Choose the application version in use from the Viewing content for dropdown to see content relevant to that version.


In versions v2021.1+:
There are two report types well suited for building your first report: ExpressView and Advanced Reports.

ExpressView is a tool to quickly get insight into vertically expanding data records and groups. An ExpressView can optionally include a visualization.

Advanced Reports are the flagship report type of the application. Advanced Reports are made using an spreadsheet-like cell-grid interface. The most powerful reporting tools are available with Advanced Reports, including geographic maps; CrossTabs; repeating groups; complex join, filter, and sort logic; drilldowns to linked child reports, and more.

Review Report Types to learn more.

The examples below utilize the Northwind sample data set.

Creating an ExpressView

This section will walk-through creating an ExpressView report listing the unit prices of products organized into categories. A column chart comparing the average price on a per-category basis is also included. The report looks like this:

CFe03qdgK0.png

  1. On the main menu in the top-left corner, click the Create New Report MainLeftPaneNewReport.png icon.
  2. Click ExpressView ThumbnailExpressView.pngto start the ExpressView Designer.
  3. Add the necessary data fields to the canvas, by dragging them from the MainLeftPaneDataFieldsSelected.png Data Fields Pane on the left side of the screen and dropping them into desired position on the canvas:
    • Categories.CategoryName
    • Products.ProductName
    • Products.Discontinued
    • Products.UnitPrice

    vpUVM7f0WY.gif

  4. To organize the report by the product category, set Categories.CategoryName as a Group. Click-and-drag on the CategoryName header and drop it to the Add Group drop zone.1AlIDaBjBh.gif
  5. To calculate the average unit price for each category, on the Group Totals line, click on the Sum for UnitPrice dropdown and change to Avg for UnitPrice.
    BHLfLnCM2t.png
  6. Add a chart to the report, by clicking the Chart ShowViz.png icon on the toolbar. Then, in the Properties Pane on the right, choose the Visualizations tab, and then Data. 5n3UIrMzZp.png
  7. Since columns that are not totaled don’t appear in charts, remove the Values for Products.ProductName and Products.Discontinued by clicking the X icon in each element. The chart will appear.
    vBfKTRkIaj.png
  8. Switch to the Type tab, then click the Column ChartTypeColumn.pngicon to change the chart to a column chart.
    6k7NKji2Wa.png

At this point, the ExpressView very closely matches the same report at the beginning of this article. It should look something like the figure below.

H7j72BDZjX.png

Continue on to apply formatting such as color schemes, fonts and column names:

  1. To style the groups and detail fields, click the Canvas DropDownArrow2.pngFormatPaintbrush.png icon in the toolbar.
    1. Choose a color Theme from the dropdown. The Office Park theme is used in the example report. The color theme will immediately change on the canvas.
    2. Choose a Font from the dropdown. Bookman Old Style is used in the example report.
  2. Click the Canvas DropDownArrow2.pngFormatPaintbrush.png icon again to close the menu.
    h3oAYL0cmE.png
  3. In the Properties Pane on the right, choose the Visualizations tab, then Appearance, then Chart Titles.
  4. Enter a Main Chart Title, and choose a font.
    Nt6LEC2Iin.png
  5. In the Labels section, choose a font to match the others. Optionally, add an X-Axis Title and Y-Axis Title.
    C4IngGwKIp.png
  6. Change to the Chart Colors section, then choose a color theme from the dropdown. The example report uses the Sandstone theme.
    We9iJ9INnz.png
  7. Change to the Chart Data section, scroll down to Number Format. This section formats how the values in the chart axes appear. Since they are average dollar values, set these controls as follows:
    • Enter 2 for Decimal Places
    • Check the Use Currency Symbol checkbox
      Thu0WXvTWX.png
  8. Each column can be given a custom name, font style and alignment. Click on a column header (e.g. CategoryName) and then in the SelectedCellMenu.png Selected Section tab of the Properties Pane, enter a Display Name, Horizontal Align choice, Font Style and Underline as desired. Repeat for the ProductName, Discontinued and UnitPrice columns.

    Note

    The Horizontal Align property will apply to the column header and the data in the column. The Display Name, Font Style and Underline only apply to the column header.

    XYcmYUORLi.png

  9. The data in the columns, and the totals can also be styled. Click on the white area of the Unit Price column between the header and the total. In the Selection Section tab of the Properties Pane, choose a Data Format (Number should already be selected). Repeat this step for the Group Totals, which will also apply the settings to the Report Totals line.
  10. Save the report by clicking the Save Save.pngDropDownArrow2.png icon in the toolbar. Enter a Name, an optional Description and select a folder to save it to. The My Reports folder is a good choice for a first report.
  11. Click Run on the toolbar to switch to Live Data mode and see the new ExpressView in all its glory. Congratulations!

Additional Resources

Creating an Advanced Report

Tip

A quick way of getting an Advanced Report up and running is to start with an ExpressView, then export that ExpressView as an Advanced Report. Editing can then resume in the Advanced Report Designer.

This section will walk-through creating an Advanced Report from scratch with the Advanced Report Designer. The sample report lists the amount of revenue generated by each salesperson in a company, including the amount of revenue of each product category in a collapsible section. A multi-series column chart appears at the beginning of the report. The sample report looks like this:

MkOPuhzJkF.png

To jump through the steps of this tutorial, click one of the following links or start from step 1 below.

  1. On the main menu in the top-left corner, click the Create New Report MainLeftPaneNewReport.png icon.
  2. Click Advanced Report ThumbnailAdvancedReport.pngto start the Advanced Report Designer.
  3. The Add Data Objects dialog appears. These data objects are needed for this report:
    • Categories
    • Products
    • Employees
    • Orders
    • OrderDetails

    Not all of these data objects appear on the report, but they provide a join path between the objects that do.
    zKcmN034Al.png
    Click Okay.

  4. The report will group and aggregate (summarize) data by the employee name, and by the product category name. Sorts are required to create groups. To create sorts:
    1. Click the Sorts SortsMenu.png icon on the toolbar to open the Report Sorts dialog.
    2. From the dropdown on the left, choose Employees, then double click the LastName field. This will add Employees.LastName as a sort.
      IdkgjepthR.png
    3. Repeat step 2 to add Categories.CategoryName as a second sort.
    4. Click Okay to close the Report Sorts dialog.
  5. This report will show two columns: one with the employee’s name and category names, the other withe actual revenue values. The other columns can be removed from the grid
    1. Hold the Ctrl key on the keyboard and click the column headers for columns E, D and C.
    2. Click the Column Menu MoreOptions_Black_Small.png icon to open the Column Menu.
    3. Click DeleteReportSmall.png Delete 3 Columns.
      oSgFnGa66v.gif
  6. The Page Header section is also not needed for this example report. Click the Section Menu MoreOptions_Black_Small.png icon on the Page Header, then click DeleteReportSmall.png Delete Section. The design grid should now look like this:
    glahf0GH9D.png
  7. The chart will reside in the Report Header section. To add it:
    1. Click the AddNew.png Add Section button
    2. Click ReportHeader.png Report Header.
  8. Add the Group Header sections for the Employee Name and Category Name:
    1. Click the AddNew.png Add Section button
    2. Click GroupHeader.png Group Header.
    3. In the Group Header dialog, select Employees.LastName from the dropdown list.twQ02ZeHOx.png
    4. Click Okay to close the Group Header dialog.
    5. Repeat steps 1–3 for Categories.CategoryName.
      The design grid should now look like this:
      Q05PrLQzbZ.png
  9. Add the data to the cells on the design grid:
    1. Add Categories.CategoryName directly from the Data Objects Pane by dragging it to cell A3 on the grid.
    2. Type the word Revenue into cell B2.
  10. Since the employee’s first and last names are contained in two different fields, a formula can be used to concatenate or join the fields together. Here is how:
    1. Select cell A2 by clicking it.
    2. Place the cursor into the FormulaLarge.png Formula Bar just below the toolbar.
    3. Type an equals sign =.
    4. Begin typing Employees. A tooltip will appear with all of the fields that contain the word Employees as guidance. Use the mouse or arrow keys to highlight Employees.FirstName then press Enter. The entire data field name will be added to the formula bar. Notice it is enclosed in { curly braces }. Data fields must always be enclosed in curly braces in formulas.
      YUa1KdhtZC.png
    5. Type &. This is called the concatenation character and tells the system to join two things together.
    6. Type ” “. This will add a space between the first and last names.
    7. Type another &.
    8. Type Employees again, this time highlighting Employees.LastName, then press Enter. The complete formula should look like this:
      ={Employees.FirstName} & " " & {Employees.LastName}
  11. Use another formula to calculate the revenue for each category:
    1. Select cell B3 by clicking it.
    2. Place the cursor into the fx Formula Bar just below the toolbar.
    3. Enter the formula exactly as it appears below:
      =AggSum({OrderDetails.UnitPrice}*{OrderDetails.Quantity})

      This formula uses an aggregate function to calculate sum of the revenues of all sales, by multiplying the quantity of each product sold by its unit price, then adding them all together. The breaking down of revenues into product categories, and for each employee happens automatically since the aggregate function is contained in the group sections created earlier.The design grid should now look something like this:
      tzKJNOru11.png

  12. Now is a good time to save the report. Click the Save Save.pngDropDownArrow2.png icon on the toolbar. Enter a Name, an optional Description and select a folder to save it to. The My Reports folder is a good choice for a first report.
  13. Apply the color formatting to the cells to match the example report.
    1. Select cell A2, then click the Background Color BackgroundColor.png icon on the toolbar. Select a medium gray color. Click the Bold StyleBold.png icon to increase the font’s weight.
    2. Repeat step 1 for cell B2, choosing a medium green color. Click the Horizontal Alignment AlignLeft.pngDropDownArrow2.png icon, then choose Center AlignCenter.png.
    3. Select cell A3, apply a light blue background color. Select cell B3 and apply a light green background color. Set cell B3 for a center alignment as well.
    4. Select both columns A and B, then place the cursor between the columns and drag to resize both of them.qvhBwDPHwR.gif
    5. To make the categories list collapsible, click the header for row number 2 to open the Row Menu. Click Collapse Rows.
    6. Delete the Detail section from the report, it won’t be needed.
  14. The design grid should now look like this:
    pK2LuT2xRM.png
    Click Run on the toolbar to execute the report to see the progress so far. In the Report Viewer, the report should look like this:
    M7qzxIYeUF.png
    Click on the Open Rows OpenRows.png icons to open or close the collapsed rows to see the revenue figures.
  15. Close the Report Viewer tab and return to the Report Designer.
  16. Apply borders and advanced numeric formatting to the data. These changes don’t appear on the Design Grid, but they affect how the data is presented in the Report Viewer and when exported to output files such as PDF or Excel.
    1. Select cell A2, then click the Format Cells FormatCells.pngicon. When the Format Cells dialog opens, switch to the Border tab.
    2. Check the Make Borders Uniform checkbox. Click the Color Chooser color-picker-icon.png icon, then choose black.
    3. Click Okay to close the dialog.
    4. Select cell B2, then click the Format Cells FormatCells.pngicon. Apply a top, right and bottom border with color black by setting the individual border settings.
      LC19CASv9P.png
    5. Repeat step 4 for:
      • cell A3 — apply a left, bottom and right border
      • cell B3 — apply a bottom and right border
    6. With cell B3 still selected, and the Format Cells dialog still open, switch to the Number tab to apply advanced numeric formatting to the revenue values contained within it:
      aYT2zLGuYz.png
      1. Under Category, select Number.
      2. Ensure Decimal Places is set to 2.
      3. Check the Use 1000 Separator and Use Currency Symbol.
      4. Click Okay to close the dialog.
  17. Run the report again to observe the changes that the formatting options have made to the report output. When satisfied, close the Viewer tab and return to the Designer.
  18. Hold the Ctrl key on the keyboard and click cells A1 and B1 to select both cells. Click the Merge Cells MergeCells.png icon on the toolbar to combine the cells together.
  19. Place the cursor between the headers for rows 1 and 2, then drag the mouse down to increase row 1’s height.
    lhBD0U6kXT.gif
  20. Select the new single cell on row 1, then add the chart:
    1. Click the Insert Insert.pngDropDownArrow2.png icon on the toolbar, then select Type.png Chart to start the Chart Wizard.
    2. On the Type tab, select the Stack Column chart type. Then, click Next to advance to the Data tab.
      QQ05tQeR41.png
    3. Click the ApplyBtn.png Data Layout… button. Since all of the revenues that appear on the chart are in a single column but different rows, click Row Based Chart then click Okay to return to the Chart Wizard.
      OHy4s8uJ44.png
    4. Under Data for Chart, make the following choices, then click Next to advance to the Appearance tab.
      • Data Values — choose the formula =AggSum({OrderDetails.UnitPrice}*{OrderDetails.Quantity}). This setting determines the size of the colored segments in each column.
      • Data Labels — choose the formula ={Employees.FirstName}& ” ” & {Employees.LastName}. This setting determines the labels that appear along the X-axis of the chart.
      • Series Labels — choose the cell value Categories.CategoryName. This setting determines the labeling of each of the Data Values.
    5. Under Colors, choose Blue Grey as the Theme.
    6. Under Labels, enter Employee Sales by Category for the Chart Title.
    7. Click Finish to close the Chart Wizard.
  21. Save the report with the chart.
  22. The report design should now look like this:
    PpHeaILRvk.png
    Run the report to see the completed report design in the Report Viewer. Congratulations!
    Ovp0cEO0n8.png

Additional Resources

In versions pre-v2021.1:
This section will walk users through the New Report Wizard and demonstrate how to create a new report from start to finish.

  1. In the main menu, click the Create New Report MainLeftPaneNewReport.png icon.
  2. There are several types of reports, the most common being an Advanced Report.

Note

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

The Report Wizard will open. The Report Wizard has five tabs: Name, Categories, Sorts, Filters and Layout. The Name and Categories tabs must be completed while the other tabs are optional.

Name Tab

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

The report name can be up to 255 characters long. The following special characters may not be used: ? : / * “ < >

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

screen.ar_nametab.png

Categories Tab

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

Data Category – A Data Category is a data object that has several attributes. For example, 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. For example, Students.ID is the numeric value that identifies a specific student.

screen.ar_categoriestab.png
  • To add a Data Category, either drag and drop it to the Category Name column, use the Add button, or double-click the category.

Note

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

  • To search for a specific Data Category or folder, type its name into the Search box.
  • To see what Data Fields are in a Data Category, click on a Data Category and then click the information button.
  • Check the Suppress Duplicates box to suppress any repeated records from that category.
  • To remove a Data Category, click the delete button.

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.

Sorts Tab

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

screen.ar_sortstab.png
  • To sort by a Data Field either drag and drop it to the Sort By Column, use the + Add  button, or double-click the field.
  • You can sort each Data Field in Ascending (A-Z, 0-9) or Descending (Z-A, 9-0) order.
  • Use the Move Item Up ^ and Move Item Down v icons to indicate the sort priority.
  • To remove a sort, click the Delete x icon.

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.

Filters Tab

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

screen.ar_fitlerstab.png

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

  • To filter a Data Field, either drag and drop it to the ‘Filter By’ column, use the button or double-click it.
  • Use the Move Item Up ^ and Move Item Down v icons to indicate the filter priority.
  • To remove a filter, click the Delete x icon.
  • Set the operator (equal to, less than, one of, etc…) by selecting it from the Operator dropdown.
  • Set the filter value by either entering it manually or selecting a value from the dropdown. If the Data Field is a date, the calendar and function buttons can be used to select a value.
  • Choose AND With Next Filter to require that the selected filter and the one below it both evaluate to true. Choose OR With Next Filter to require that either or both be true.
  • Check Group With Next Filter to group the filters. Filters can be nested indefinitely by using the following keyboard shortcuts while a filter is selected:
    • Ctrl + [ adds an open-parenthesis before the selected filter.
    • Ctrl + ] adds a close-parenthesis after the selected filter.
    • Ctrl + Shift + [ removes an open-parenthesis from before the selected filter.
    • Ctrl + Shift + ] removes a close-parenthesis from after the selected filter.
  • Check Prompt for Value to prompt the user for a new filter value at the time the report is executed.

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.

Layout Tab

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

screen.ar_layouttab.png

Display Data

  • To place a Data Field on the report, either drag and drop it to the Data Field column, use the Add + button, or double-click the field.
  • Use the Move Item Up ^ and Move Item Downv arrows to indicate the order the Data Fields should appear on the report. The Data Field at the top will appear on the report as the left-most column.
  • The Summary Function column is used to make subtotals and grand totals.
  • To remove a Data Field, click the Delete x icon.

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

Subtotals and Grand Totals

  • To display subtotals, check the box of the category you want subtotals for in the Summarize By box. Then, for each Data Field you want totaled, select a Summary Function (see below).
  • To display grand totals, check the Grand Total box. Then for each Data Field you want totaled, select a Summary Function (see below).
screen.ar_summarizeby.png

Summary Functions

  • Sum: Totals the all of the data in the Data Field.
  • Count: Returns the number of rows in the Data Field.
  • Average: Takes the mean of the data in the Data Field.
  • Minimum: Displays the lowest value in the Data Field.
  • Maximum: Displays the highest value in the Data Field.

Data Headers

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

  • To include a Header, check the Include Header at the beginning checkbox. In order to select the text that will appear as the header value, use the Header dropdown to select a Data Field or use the Formula Editor fx icon to create a formula.
  • Use the Summarize by each unique dropdown to specify if the header should repeat based on a specific field or fields within a Category.
  • Check the Include Total at the end checkbox to have a subtotal created for this Category.

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

  • To see the report in the Report Designer, click Finish.

The Report Designer will display the report like this.

screen.ar_reportdesigner.png

Note

For information on the Toolbar and all its features, see the Using the Toolbar article

To add the next layer of intricacy to your articles you’ll want to create grouping within the present data. To read more, continue to Grouping Basics.

Was this article helpful?
4.5 out of 5 stars
5 Stars 0%
4 Stars 100%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Table of Contents