Building Your First Report

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:

1. On the main menu in the top-left corner, click the Create New Report icon.
2. Click ExpressView to start the ExpressView Designer.
3. Add the necessary data fields to the canvas, by dragging them from the 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

   

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.
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.
   
6. Add a chart to the report, by clicking the Chart icon on the toolbar. Then, in the Properties Pane on the right, choose the Visualizations tab, and then Data.
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.
   
8. Switch to the Type tab, then click the Column icon to change the chart to a column chart.
   

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.

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

9. To style the groups and detail fields, click the Canvas 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.
10. Click the Canvas icon again to close the menu.
    
11. In the Properties Pane on the right, choose the Visualizations tab, then Appearance, then Chart Titles.
12. Enter a Main Chart Title, and choose a font.
    
13. In the Labels section, choose a font to match the others. Optionally, add an X-Axis Title and Y-Axis Title.
    
14. Change to the Chart Colors section, then choose a color theme from the dropdown. The example report uses the Sandstone theme.
    
15. 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
      
16. Each column can be given a custom name, font style and alignment. Click on a column header (e.g. CategoryName) and then in the 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.

    

17. 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.
18. Save the report by clicking the Save 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.
19. Click on the toolbar to switch to Live Data mode and see the new ExpressView in all its glory. Congratulations!

Additional Resources

• ExpressView Documentation

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:

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

• Setup the Report’s Layout (steps 1–8)
• Add Data and Content to the Report (steps 9–12)
• Apply Formatting to the Cells and Data (steps 13–16)
• Add the Chart (steps 17–20)

1. On the main menu in the top-left corner, click the Create New Report icon.
2. Click Advanced Report to 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.
   
   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 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.
      
   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 icon to open the Column Menu.
   3. Click Delete 3 Columns.
      
6. The Page Header section is also not needed for this example report. Click the Section Menu icon on the Page Header, then click Delete Section. The design grid should now look like this:
   
7. The chart will reside in the Report Header section. To add it:
   1. Click the Add Section button
   2. Click Report Header.
8. Add the Group Header sections for the Employee Name and Category Name:
   1. Click the Add Section button
   2. Click Group Header.
   3. In the Group Header dialog, select Employees.LastName from the dropdown list.
   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:
      
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 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.
       
    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 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:
       

12. Now is a good time to save the report. Click the Save 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 icon on the toolbar. Select a medium gray color. Click the Bold icon to increase the font’s weight.
    2. Repeat step 1 for cell B2, choosing a medium green color. Click the Horizontal Alignment icon, then choose Center .
    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.
    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:
    
    Click on the toolbar to execute the report to see the progress so far. In the Report Viewer, the report should look like this:
    
    Click on the Open Rows 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 icon. When the Format Cells dialog opens, switch to the Border tab.
    2. Check the Make Borders Uniform checkbox. Click the Color Chooser icon, then choose black.
    3. Click Okay to close the dialog.
    4. Select cell B2, then click the Format Cells icon. Apply a top, right and bottom border with color black by setting the individual border settings.
       
    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:
       
       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 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.
    
20. Select the new single cell on row 1, then add the chart:
    1. Click the Insert icon on the toolbar, then select 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.
       
    3. Click the 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.
       
    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:
    
    Run the report to see the completed report design in the Report Viewer. Congratulations!
    

Additional Resources

• Advanced Reports: Sections
• Advanced Reports: Design Grid
• Advanced Reports: Sorts
• Charts