Example 2: Customer Invoice List

1.	In the Report Designer, click on the Sample Customer List you just created. We are going to create a copy of the report and alter the copy to create a new, more complex report. We will now make a list of invoices by company, displaying the invoice numbers and totals.

2.	Copy and Paste the report into a custom folder.

3.	In the Report Item [New] window, type in the name of the new report: Sample Customer Invoices List. Click Save.

_img880

4.	In the Report Explorer, click the Design button to open the report in the Report Designer.

1.	Click on the Data tab.

2.	The Company dataset you selected before will already be there. We will now add the Invoice dataset.

3.	To add another dataset, follow the steps under Assign a Dataset to the Report in Example 1 above, except instead of selecting Company, you will select Invoice from the Available Tables.

Now, we have to link the two datasets together in the correct way, so that the report will display the invoices for each company.

1.	In the Invoice dataset, find the field called Companyid (about halfway down). In the Company dataset, find the field called Uniqueid (at the top).

2.	Click on Companyid in the Invoice dataset first, and drag and drop it onto Uniqueid in the Company dataset. A link will appear between the two fields.

IMPORTANT: You must link the fields in the correct direction so that there is a number 1 next to Uniqueid and the infinity symbol (∞) next to Companyid. This means that for one company there are potentially numerous invoices. If you connect the fields in the reverse direction, so that there are an infinite number of companies for one invoice, it does not make sense.

_img712

1.	Click on the Design tab. Your Company and Phone Number labels/fields will still be there.

2.	We will now add a Sub Report below to display all invoices for each company. The sub report will repeat every time there is a new company, displaying all of the invoices for that company.

3.	Click on the Sub Report icon and click in the detail band below the CompanyName and Phone1 DB text fields. The sub report component will initially read, “Sub Report 1: No Data Pipeline Assigned”.

4.	It is important to assign the Invoice dataset to this report. To assign the Invoice dataset to this report, click on the sub-report and select Invoice from the drop-down list in the upper left corner.

_img713

Click on the Sub Report: Invoice tab at the bottom of the page to view the sub report. There will be a Title band, Detail band and Summary band. The title will appear at the beginning of each sub report, the detail band will repeat for each record in each sub report and the summary will appear at the end of each sub report:

Company A 555-5555A

Sub Report Title

Sub Report Detail (Invoice 1)

Sub Report Detail (Invoice 2)

Sub Report Summary

Company B 555-5555B

Sub Report Title

Sub Report Detail (Invoice 1)

Sub Report Detail (Invoice 2)

Sub Report Detail (Invoice 3)

Sub Report Summary

_img882

We will now add the relevant fields to the sub report so that we can see the invoices for each company.

1.	Follow the steps from Add Data Fields to Report Layout in Example 1 to add an Invoice Number field and an Invoice Total field to the Detail band. Remember that you will select Invoice in the first drop-down list and Invoiceno or Totalamount in the second drop-down list.

Add Invoice Number and Invoice Total labels in the Title band (above ^Title). Review the steps from Add Labels to Report Layout in Example 1, if required. Place the labels close to the top of the title band and close up the space by dragging and dropping the title band. Large gaps in the sub report will break up the flow of the entire report.

_img776

Re-size, align and otherwise edit your labels and database fields as required. With sub reports, it is ideal to close up any blank spaces to minimize the length of the report and make it look better organized. Place the fields as close as possible to the top of the detail band, directly beneath the Title band. Then drag and drop the detail band beneath the fields so that there is little or no blank space. Hint: While you are creating a report, click the Preview tab after you add and adjust components to see what the final result will look like.

1.	We will now add a Summary to the bottom of the of the sub report so that we can see the total number of invoices for each company and the total amount of those invoices. We will create a Region in the summary band and add fields that will calculate totals from the fields.

2.	Click the Region icon and click in the Summary Band. Resize and move the region so that it fits in the band and aligns with the other components of the sub report. You will not see the text “Region1” in the actual report.

3.	Click the DB Calc Icon and click in the Region. From the drop-down lists, select Invoice and Invoiceno.

4.	Right-click the DB Calc field and select Calculations.

a.	In the Calculations dialog box, you can select the type of calculation you want to perform: Count, Sum, Minimum, Maximum or Average.

b.	In this case, we have selected the Invoice Number field and we want to determine how many invoices each company has, so we will select Count (counts the number of times the Invoice Number field repeats for each company).

Click OK.

_img714

5.	Add another DB Calc field in the region. This time, select the Totalamount field and for the Calculation, select Sum (add up all the amounts of each company’s invoices).

a.	Right-click the Totalamount field and select Display Format.

b.	In the Format dialog box, choose the appearance of the numbers. For the Total Amount of invoices, you probably want to select an option with a dollar sign and two decimal places.

6.	Add Labels above the calculation fields in the region, e.g. Total Invoices and Total Amount.

7.	Re-size and align the labels and calculations so that they are consistent with the fields in the detail band. Move the components so that there are no excessive spaces.

_img777

Finally, we will add a Title band to this report. Click on the Main: Company tab to return to the main report. Click Report | Title to add a title band above the header. Remember that the header will appear at the top of every page, but the title will only appear once at the beginning of the report.

2.	Place a label in the Title band and name the report, e.g. Sample Company Invoice List.

3.	Add the date of the report by clicking on the System Variable Icon and clicking in the title band. From the drop-down list in the upper left corner, select Date or DateTime. Place the date under the report title, or wherever you would prefer it to appear.

4.	To add page numbers, follow the same process, but choose PageNo from the drop-down list. If you want page numbers to appear at the bottom of each page, move the PageNo variable field into the Footer band.

5.	Add your company logo by clicking on the Image Icon and clicking in the title band. Right-click the image and choose Picture. In the Open dialog box, navigate to your logo and click Open. Re-size and position your logo in the report.

a.	You can also add your logo by adding a PSI DB Text field.

b.	From the drop-down lists, choose Setup Default and Logo.

6.	To add your company information to the report, use your EventPro license variables, such as User Name and User Address.

a.	Add a PSI DB Text field to the title band. Right-click on the field and select System Variable.

b.	In the System Variable box, choose the appropriate variable and click OK. Continue adding as many variables as you need, aligning and resizing the fields as necessary.

c.	If you want to be able to move your company information as one unit, add a region to the title band first, and add the variable fields inside the region.

Your report is now complete. The report layout will look something like this (click link to see screenshot)

_img778

Click the Preview tab to view the report. The preview will look something like this (click link to see screenshot)

_img779

If you are satisfied with appearance of the report, select File | Save to save your report.

Click the X in the upper right corner to close the Designer and return to the Report Explorer.

Help URL: http://www.eventpro.net/Manual/HTML/index.html?ch15_example_2_customer_invoice_list_.htm

Example 2: Customer Invoice List						CH15_Example_2_Customer_Invoice_List_ Dear EventPro Documentation Department,
Example 2: Customer Invoice List