Tutorial 9-5. Using Shared Variables

To illustrate how to use shared variables, let’s modify the example report shown earlier that lists the customer sales and credits issued to the customer. That report showed the detail sales entries and credit items, but doesn’t tie the two together. We want to modify this report so that it subtracts the credits from the total sales amount to give you the net sales figure. The subreport will pass the total credits to a shared variable and this shared variable will be used on the main report to calculate the net sales.

1. Open the report ‘Customer Credits.rpt’ and save it as Customer Net Sales.rpt.
2. The first thing we want to do on both reports is show the subtotals. Not only is it important to see the total sales and credits for each customer, but showing them on the report will make it easy for us to verify that the net sales amount is accurate.
2. In design mode on the main report, right-click on the Order Amount field and select Insert > Summary.
3. On the Insert Summary dialog box, look for the Summary Location property near the middle and change it to Group #1. This puts the subtotal in the group footer so that you can see it for each customer. Click the OK button to save your changes.
4. Double-click on the Customer Credits subreport so that it opens in design mode.
5. Right-click on the Amount field and select Insert > Summary.
6. On the Insert Summary dialog box, keep the default settings and click the OK button to save changes. This inserts a total of all the credits in the report footer.
7. Since the sales amount is on the main report and the credits are listed on the subreport, a shared variable is needed so that the two reports can share their data. We have to create a formula on both the main report and the subreport that declares the same shared variable.
7. On the subreport, create a new formula called Net Credits. Enter the following code for the formula.

Shared CurrencyVar TotalCredits;
TotalCredits := Sum({Credit.Amount});

1. This formula assigns the total credits to the shared variable TotalCredits. However, Crystal Reports won’t calculate the formula’s value unless it is placed somewhere on the report. We already have the total listed on the report and the formula is just duplicating this information. So, we are going to put it on the report and then suppress it. This tells Crystal Reports to calculate the formula’s value, but not show it on the report.
8. Save the formula and return to design mode of the subreport.
9. Drag and drop the formula onto the report in the report footer.
10. Right-click on the formula and select Format Field. Go to the Common tab and click the Suppress property. Click the OK button to save your changes. The formula will no longer be visible, but it will still get calculated.
11. Click on the design tab of the main report.
12. Add a new formula to the main report called Net Sales. Enter the following code for the formula.

Shared CurrencyVar TotalCredits;
CurrencyVar NetSales;
NetSales := Sum ({Orders.Order Amount},
{Customer.Customer ID}) + TotalCredits;

1. This formula is adding the total credits shared variable to the total sales figure. Since the TotalCredits value is negative, it will be subtracted.
13. Save and close the formula to return to the design view of the main report.
14. Drag and drop the formula into the section Group Footer #1b.
15. Unfortunately, there isn’t enough room in the group footer to fit the formula within the gray box. Click on the bottom edge of the box to make it larger.
16. Let’s add a description of the net sales formula so that the reader knows what is being calculated. Select the menu options Insert > Text Object. Enter the text Net Sales.
17. Click on both the text object and the formula and make them bold. This will make it easier for the reader to see the amount.
18. The bottom portion of the report in design view should look similar to the following figure.

18. Preview the report and the bottom portion should look like this.

The Net Sales subtotal at the bottom uses the shared variable to combine the total sales less the net credits.