In this series, you will learn the basics of creating a report in the Web Report Designer. This lesson builds on previous articles. To follow along, you will need to have the knowledge learned from previous lessons.
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.
For example: you can use a detail report to display all invoices associated with a customer within a customer list.
Let's create the above example. We will start with creating 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.
1. 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: InvoiceviaCustomer
2. Save and Launch Builder
3. Insert a Report Header Band.
4. Add a new label and have it say "Customers' Invoices" Style it as you want.
5. Go to the Fields list, and expand the Customer table. There will be three tables listed, so make sure you choose the correct table.
6. 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.
7. 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.
8. 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.
9. 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.
1. Insert a Detail Report.
2. A new band appears. Select DetailReport1, but make sure you don't select Detail2.
3. 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).
4. Expand Data Member. It's import NOT to choose the details table, in our example the Invoice table, on its own. Don't choose that one!
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 tables separated from main base table.
5. Go to the Fields List, and make sure you find the embedded Invoice.Customer which is inside of the Customer table. This is important, do NOT expand the Invoice table.
Expand the Customer table first...
Then expand the Invoice.Customer table.
6. You will now drag 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
7. Feel free to style the band and the fields.
8. 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.