Tutorial: Creating a Printable Report

In this tutorial we'll create a printable report with nested levels of detail.

For this report, we're going to use a database that tracks orders for a fictitious company. We have four tables. The Clients, Employees, and Products tables contain lists of our fictitious company's clients, employees, and products. The fourth table, Orders, tracks each order from a particular client for a particular products. Each order is also linked to a specific employee, indicating the salesperson who completed the order.

Step 1: Begin the report layout

In the Start menu, choose New Report. You'll get a new report editor with a blank grid. The grid works like a standard spreadsheet, except that sets of rows can be linked to data from your database and repeated. For example, you can mark off a set of rows, link it to your Employees table, and the whole set of rows will be repeated once for each row of data in the table. Repeating sections can also be nested, and we'll use that ability to create the nested detail levels in this tutorial.

Before we link any data, let's decide on a basic layout. We want our report to show all orders for a specified date range, broken down first by client, then by salesperson, finally showing each individual order. At the very top of our report, we'll put a header showing the report title and the date range. Then we'll put in some column headers to indicate where each particular piece of information will go. To enter data in a cell, click the cell and start typing.

We needed six columns, but the new report only had five columns by default. To change the number of columns, click the Edit button, choose Report Properties, and then change the Columns property in the property editor on the right. We've filled in column headers for each of the six columns and put a two-line header at the very top of the report. Notice that we've written dates in the second row--we'll see later how to replace those with the actual dates specified when the report runs. Finally, we've adjusted the widths of the columns; rows containing quantities and costs don't require as much space as those that will contain client and employee names. To change column widths, click in the separators between columns in the grid header and drag to resize.

Step 2: Define any report parameters

Because our report won't be very useful if it always shows the same data, we need to set up parameters that we can change each time we run the report. In this case, we'll make the date range be parameter-driven, so that we can run the report for different time periods. Click Edit->Report Properties and click in the Parameters field. In the pop-up parameter list, enter two parameters, named StartDate and EndDate. For convenience, we'll assign a default value to each parameter, so we don't have to keep entering values as we preview the report while we're designing it.

Step 3: Lay out report using placeholder text

Now we'll lay out the data-driven part of the report. As we do this, we must keep in mind that we're setting up sections that will be repeated for each row of data returned by the report's data source. So where we want a list of detail rows, we need only one template row that defines the row layout—the report engine will fill in many rows of data from a single template row. Imagine what your report would look like if you only had a single row for each data source—in our case, if we had only one client, one employee, one product, and one order.

Here we've filled in cells with placeholder text to indicate where we want various pieces of data to appear. Notice a few things: we've left some blank rows after each subtotal, to provide some visual separation between repeating sections. In the line-item Total cell (F7), we will need to multiply the quantity times the unit cost to come up with the total cost. Finally, we have totals in cells F8, F10, and F12 that will need to be calculated as the report executes.

Step 4: Define repeating sections

Now we're ready to define which parts of this report template will be linked to data. We have three different levels: one level shows all information for a single client; within that, another level shows all information for each salesperson, and finally, a final level within each salesperson group shows each individual order. Let's start at the highest level. Select rows 5 through 11 (click in the grey row number and drag to select rows). Once you've selected the rows, click the Edit button and choose "Create Repeating Section".

Now do the same for rows 6 through 9, and finally for row 7. When you're done, you should have three nested groups. Save your changes by clicking the Save button.

Step 5: Create queries to provide data

Now let's create the queries that will provide the data for the report. We'll start at the highest level, with the query that returns the list of clients. Our query will require parameters for the start and end dates, and we'll set their values to come from the values of the report parameters. The query will then return information for all clients who had an order within the specified date range.

Our second-level query finds all salespeople (employees) who had an order within the specified date range, for the given client. Remember that this query runs within the context of a specific client. To find out which client, we create a parameter with the expression =parentData["ClientID"]. This expression tells the query to find the ClientID value from the parent data source so we can use it to know which employees to return. Only one level of parent data is available—a section cannot access the data from its parent's parent. For that reason, this query passes along the ClientID value by returning it at the end of line 2, so the next query will have access to it.

Finally, the orders query returns all orders for the given client, for the given salesperson, in the given date range. To know which client to look for, it uses the ClientID value passed along by the employee query. The start date and end date are set up as parameters, just like in the other two queries. Finally, the EmployeeID is found by referencing the parentData object.

Step 6: Insert data into report template

Returning to our report template, we'll link our repeating sections to these queries by clicking the tab on the left to select the section, then in the Properties window setting the Data Source property. Once the section's data source has been set, we can click into a cell within that section and we'll see the "Insert data..." drop-down at the top become active. This drop-down list contains all the fields returned by the query's data source. Selecting one will enter a data reference into the cell text. Using this drop-down we can replace the placeholder text in cell A5 ("customer name") with the actual data containing the customer's name.

Follow these steps to link the other two sections to their respective queries, then replace the other data-driven cells with the correct cell references. Once you're done, only the totals cells should remain unfinished.

Step 7: Insert calculations

Some cells require more than simple insertion of a data value into the text of a field. For example, our line-item total in F7 requires multiplication of the unit cost by the quantity. We can write script expressions that perform calculations using data from our data source. Expressions must begin with an equals sign ("=") and refer to data with the form data["fieldname"]. For example, the expression for F7 is =data["Quantity"] * data["UnitCost"].

In addition to calculations on the data, we can use the SUM report function to access running totals. The SUM function is context-aware, so that it automatically sums all instances of the summed cell that occur within the current section. That means we can use the same expression in all three of our totals cells, and we'll get the right values when the report runs. Enter =SUM(F7) in cells F8, F10, and F12.

We've inserted data in a few other places. In the subtotal labels E8 and E10, we repeated the client and employee names. In row 2, we inserted the report parameter values, using the following expression:

="For period: " + report.parameters["StartDate"] + " to " + report.parameters["EndDate"]

Finally, we applied font styles to the report header, and right-aligned the numeric cells. For the cells containing dollar amounts, we selected a Format property that displays currency amounts formatted with a dollar sign. Here's what our final report template looks like:

Step 8: Run the report

Now you can use the Run button in the report toolbar to run the report in Screen (HTML) mode:

or Print (PDF):

That's the end of this tutorial! To become more proficient with reports, try experimenting with calculations, running totals, and text formatting.