This article will walk through creating a report listing customers and the items they have purchased.
In this article, we will first cover how to create the report:
And then we will show how to customize the Invoices List to show the report with filtering.
NOTE: This article will assume an intermediate knowledge of the Web Report Designer as well as customization. Objects you will use and properties of basic elements will not be explained in detail.
Create a New Report
Navigate to Account Settings ⇨ Reports and Print Templates.
On the Reports' All Templates screen, click New.
Name the report "
Item Report by Customer" with a description of "InvoiceLine report by Customer".For the Base table, select the InvoiceLine table. Any items a customer purchases will be stored in the InvoiceLine table, which keeps track of line items on an invoice.
Click Launch Builder to start creating the report.
List out Items in the Report
In this section, we will build an initial report to show all the items from the InvoiceLine table.
Select the database icon on the right menu, and expand your account and then the InvoiceLine table.
From the InvoiceLine table, drag these three fields into the Detail1 band: Item, Quantity, and Amount. Remember to resize the Detail1 band so each row is smaller in the report.
Feel free to preview the report to see if the items list properly.
Create a Customer Name Linked Field in the InvoiceLine table
In order to group the items by customer, the report needs the customer name in the InvoiceLine table, however the table does not contain the Customer name. It does, however, contain a dropdown field of the Invoice table which holds the customer name, so we'll add in a linked field to pull in the customer's name.
Save your report.
Open up a new tab or window and sign into your Method account.
Navigate to Account Settings ⇨ Tables & Fields.
Search for the InvoiceLine table and select it in the grid.
Add a new field:
Field Name: InvoiceCustomerName
Field Type: Linked
Linked Using: InvoiceRecordID
Linked Field: Customer
Group Items by Customer
Go back to your report and refresh the screen. This will ensure the new field you created will show up.
On the right menu, select the cog icon, and then click the icon to Insert Group Header Band.
On the right menu, select the database icon. Into the group header, drag in the new InvoiceCustomerName field you created within the InvoiceLine table.
Select the GroupHeader1 handle on the grid to bring up its properties.
Expand the ▶ GROUP FIELDS and then click the Add icon.
For Group By, select the InvoiceCustomerName field.
To make it easier to see, style the report.
Bold the Customer name field
Indent the fields in the Details1 band.
Make the rows shorter.
Format the Amount field: set its Text Format String to Currency.
Add a GroupFooter1 band (best practice to have matching group header and footers.
Preview the report to see if things list out to your satisfaction.
Summarize Items
You may have noticed in your preview each item is listed individually because they are taken from separate invoices. You can group all the items into one line.
Add another group header and footer to get GroupHeader2 and GroupFooter2.
Change the Level of GroupHeader1 to 1 so that it's the top header. I've also changed the name of this group header to "GroupCustomer".
Select and change name of GroupHeader2 to GroupItem. This header will group the items so the same items will be grouped together. Note, there are no fields inside of this band, it's empty.
Still with the GroupItem header band selected, add a new Group Field. Change the Group By to the Item field.
Of course, now that the items are grouped together, you only want to see one of each item, as well as a sum of their amounts and totals. This will be done in the footer band we created. Select all the fields in the Details1 band, and drag them to the footer below, GroupFooter1. Change the name of the band to "GroupItemSummary".
Fields that are placed in a group band can have summary functions applied to them. Select the Quantity field, and expand Summary. Change Running to Group, and keep the function at Sum.
Do the same for the Amount field. Select it, and change its Running to Group with the default Function of Sum.
When you preview this, items will be grouped under each customer with their total amount and cost.
Customer Total Amount
The second footer created will give a summary for the customer. Similar to the item summary, this will be a customer summary that will show the total amount spent.
Copy the Amount field which is in the GroupItem footer band.
Select the GroupFooter2 band, and paste a second Amount field. Feel free to rename the footer band to GroupCustomerSummary.
You can also drag in a line object and place it about the second Amount field.
Save the report!
With this, the basics of the report are done! Feel free to style the report with a proper report title, date field, page numbers, coloring, whatever you feel is necessary for your reports.
Customize the Invoice List Screen
When you are satisfied with your report, you will want it display results in Method, probably with filtering. In this example, we will display the report with date filtering, and set the parameters for the filter on the Invoice List screen.
Back within Method, customize the Invoice List screen and Create a copy of the screen.
At the bottom of the screen, drag in a new section and call it SectionReporting. Give it three rows and four columns. Change its Top Padding to 1x.
Select the first row and column, and set its Column Width to 25%.
Into their own rows, drag in two DateTimePicker objects and a Button object.
Name the first two DateTimePicker objects "From" and "To" respectively, and name the Button object "Generate Item by Customer Report".
Change the Display Format for the DateTimePickers to be Short Date.
Edit the Button's Click Event.
Add a Generate Report action.
For the report, select the report we created "Item Report by Customer".
Click Open Criteria Builder. and then ⊕ Add a Condition.
For the first condition:
InvoiceTxnDate for first field to compare to
Greater than or Equal to Operator
From for second field to compare to
Compare dates using Date.
Click ⊕ Add another Condition and for the second condition:
InvoiceTxnDate for first field to compare to
Less than or Equal to Operator
To for second field to compare to
Compare dates using Date.
Keep all the defaults for this action, but put in an internal note for this action.
Close the action list and also close the designer screen.
Your page is done! Feel free to add more buttons that filter by other customer, sales rep, item, etc.