This series covers the basics of creating a report in the Web Report Designer. Previousy, you learned about groups and creating group bands.
In this article, we will look how to combine data from multiple tables using Detail Reports and also how to filter the data.
Detail reports are nested reports. They draw on additional tables associated with the base table. In other words, a detail table must have dropdown connected to the base table.
For example: You can create a detail report to display all invoices associated with a customer within a customer list. The invoice table has a dropdown field connected to the customer table.
To create the above example, we will start from scratch and create a brand new report.
Create a Customer Report with Invoices Detail
In this section, you will create the report and its header as outlined in the first two lessons of this series.
Create a new report. Fill it out as shown below:
Name: Customer Invoices
Description: A list of invoices for each customer
Base Table: Customer
Detail tables: Invoice via Customer
Click Launch Builder.
Insert a Report Header Band.
Add a new label and have it say "Customers' Invoices" Style it as you want.
Click the database icon to go to the Fields list, expand your company, and then expand the Customer table. There will be three tables listed, so make sure you choose the correct table.
Drag the Customer's Name field into the Detail1 band. Feel free to style it. I also made the band smaller to fit the name field.
If you were to preview this now, you would get a list of names. To make sure they are sorted by name, we'll add a sort order to the Details1 band. Select the Details1 band, and in its properties, expand the Sort Fields property under Actions.
Click the plus ( + ) button. For the sort field, choose the Name field. You can choose ascending or descending with the arrow, but keep it at the default of ascending.
Your report should look similar to the following image:
Add Detail Report
In this section, we will add the the detail report. Deselect any controls you may have selected by selecting a band or clicking somewhere on the report.
Insert a Detail Report.
A new band appears. Select DetailReport1, but make sure you don't select Detail2.
With the DetailReport1 band selected (this is important to have this band selected), change your Data Source to your Method account (if not already selected). In this example, my Method account is called
best4youservicesonqbo.
Expand Data Member and expand the Customer table. DO NOT choose the Invoices table (the detail table we chose for the report).
Selecting the wrong table will not show the right data! Instead, expand the Customer table and you will see an Invoice.Customer field. Select this one.
In almost every report you create with multiple detail tables, you must select the proper table. It should never be the lone table separated from main base table.
Go to the Fields List, and make sure you find the embedded Invoice.Customer which is inside of the Customer table.
Expand the Invoice.Customer table. Again, make sure you expand the correct one, the one within the Customer table.
Drag in four fields from the Invoice.Customer table into DetailReport1. You must make sure it's from this table. Do NOT drag in fields from the Customer table, nor the Invoice table.
TxnDate
RefNumber
ARAccount
Amount
Style the DetailReportBand1 and the fields.
Save the report, and then preview it. Of course, your report will look different, since the report depends on the data in your account.
Underneath each of the customers, it lists out the invoices they have, however, it also lists out customers who have no invoices.
Can we fix that? Yes. We will filter the list to not include those items.
In the next article, you will learn about filtering the report.