Tutorial 3-6. Sales By Country and Product Type.rpt

Before getting into the tutorials, we first need to build the base report that is used for tutorial 3-7 and 3-8. This will be used for demonstrating how to modify an existing report so that it can handle dynamic groups.

1. On the Start Page click Standard Report Wizard to create a new report.
2. In the Data dialog box, select the Xtreme.mdb database as the data source. Do this by clicking on Create New Connection > Access/Excel > Make New Connection.
3. For the Database Name property, navigate to the Xtreme.mdb database that is installed in the Crystal Reports sample folders. Click the Finish button once you’ve selected it.
4. Open the Tables category and double-click the following tables to add them to the Selected Tables list at the right: Customer, Orders, Orders Detail, Product, Product_Type.
5. Click the Next button twice to go to the Fields dialog.
6. In the Available Fields list, select the following fields: Customer.Country, Product.Product ID, Product.Product Name, Product Type.Product Type Name, Orders_Detail.Unit Price, and Orders_Detail.Quantity.
7. Click the Next button to go to the Grouping dialog box.
8. Select the following fields to group on: Customer.Country and Product_Type.Product Type Name. Click the Next button.
9. On the Summaries dialog, remove all fields from the Summarized Fields list.
10. 10. Click the Finish button and go into design mode.
11. Right-click on the Formula Fields category in the Field Explorer window and select New.
12. 12. Enter the formula name SalesAmount.
13. In the Formula Workshop dialog box, enter the following formula:

{Orders_Detail.Quantity} * {Orders_Detail.Unit Price}

14. Click the Save and Close button to save the formula.
15. We need to put the SalesAmount formula on the report, but there isn’t any room. Move the Unit Price and Quanity fields to the left.
16. Drag and drop the SalesAmount formula onto the far right side of the report.

Now we need to add the summary fields for both the Quantity and SalesAmount fields.

17. Right-click on the Quantity field and select Insert > Summary. On the Insert Summary dialog box, set the summary location to be Grand Total and click the OK button.
18. Repeat the previous step to insert summaries for Group #1 and Group #2.
19. Right-click on the SalesAmount formula field and select Insert > Summary. On the Insert Summary dialog box, set the summary location to be Grand Total and click the OK button.
20. Repeat the previous step to insert summaries for Group #1 and Group #2.

At this point you’ve created the report with two grouping fields and there are summaries for the Quantity and SalesAmount fields. Your report should similar to the following figure.

21. It’s not the most beautiful report, but it will do for this tutorial. Save the report as Sales By Country and Product Type.rpt.

This report is now ready to be used as the starting point for the next two tutorials. So make sure to remember where you saved it so that you can re-load it when necessary.

Question: I have a report that usually prints two groups. But, I want to give the user the option to add a new grouping field when they run the report. Can I dynamically add a group to the report?

Answer: No, you can’t add new groups to a report while it is being printed. You can only hide existing groups. For example, if you have a report that needs to show between one to three groups, you need to create the report using the maximum number of groups (three in this example) and then hide the ones that aren’t needed.