The subreport will display the SalesOrderDetails records for
a given SalesOrderID. Again, this is for demonstration purposes only but you
can apply the same concept to your reports when you have data in two different
databases. You can also apply the same concept if you have two separate
queries that need to execute to produce data on a report.
1.
Right click on the white space in the Details section and select InsertàSubreport… from the pop-up menu.
2.
A square box will follow your mouse pointer until you left click in a
section. Left click in the Details section so the report is placed in the
Details section. The Insert Subreport dialog will appear.
3.
Select the "Create a subreport with the Report Wizard option and
enter "SalesOrderDetail" for the New report name. Click the Report
Wizard… button.
4.
This will display the Standard Report Wizard dialog box. The first step
in the wizard is to choose a data source for the subreport. Double click on
the Make New Connection to create a new connection to the AdventureWorks
database.
5.
Select SQL Native Client from the Provider list and click the Next
button.
6.
Enter the server name, a user name and password, and select the
AdventureWorks database from the database drop down list.
7.
Click the Next button and then the Finish button.
8.
Expand the AdventureWorks database to display the list of schemas.
Expand the Sales schema and the tables underneath it.
9.
Select the SalesOrderDetail table and click the > button to move the
table to the Selected Tables list.
10. Expand
the Production schema and the tables underneath it.
11. Select
the Product table and click the > button to move the table to the Selected
Tables list. The SalesOrderDetail has a foreign key to the Product table. The
report will display the product name and number for each record in the
SalesOrderDetail table rather than the foreign key.
12. Click
the Next button.
13. Crystal
Reports tries to determine the relationships between tables automatically and
usually does a good job. However, in this case it created two links where we only
want one between the Product.ProductID and SalesOrderDetail.ProductID. The link
between the rowguid fields between the two tables should be deleted. To do
this left click on the line between the two fields and press the Delete key.
14. Click
the Next button.
15. The
next screen asks you which fields you want on the report. Click on the
following fields and move them to the Fields To Display list by clicking on the
> button. Product.ProductNumber, Product.ProductName, SalesOrderDetail.OrderQty,
SalesOrderDetail.UnitPrice, and SalesOrderDetail.LineTotal.
16. Click
the Next button.
17. The
next screen asks you to group the records. There is no grouping in the
subreport since this is handled in the main report. Simply click the Next
button.
18. The
next screen asks you for the selection criteria. There are no selection
criteria for this report. This will be handled in the main report. The
SalesOrderID field will be passed to this report and only records for the
specified ID will be displayed. Click the Next button.
19. The
report style will be standard so leave Standard selected and click the Finish
button.
20. This
will bring you back to the Insert Subreport dialog. Click on the Link tab.
21. This
screen allows you to define the field that will link the two reports together.
Select the SalesOrderHeader.SaleOrderId from the Available Fields list and
click the > button.
22. A
parameter field will be created in the subreport that will contain the
SalesOrderID from the main report. Click the OK button.
23. The
SalesOrderDetail subreport will be displayed as a box in the details section.
Click on the box to show the window handles and drag the borders so they are
the width of the page.
24. By
default the subreport is shown with a box around it. For this report we want
to remove the box. To do this, right click on the subreport and select Format
Object from the pop-up menu.
25. The
Format Editor dialog box will appear. Click the Border tab and set the Left,
Right, Top, and Bottom border to None and click the OK button.
26. Suppress
Section1(Report Header), Section2(Page Header), GroupFooterSection1,
Section1(Report Footer), and Section5(Page Footer). You can hide the section
by right clicking the gray bar and selecting Suppress from the pop-up menu.
27. Save
the report.
28. Now
you can preview the report by clicking on the Main Report Preview button.
Step 5: Format the Subreport
As you can see from the preview, the print date is being
repeated in the sub report and there is extra space between each group. This
is because when the sub report was created Crystal included the print date in
the report and also shows extra sections in the subreport. You can modify the
subreport to not show the print date and hide the extra sections by following
these steps.
1.
Click the Main Report button.
2.
Right click on the subreport and select Edit Subreport from the pop-up
menu.
3.
The SalesOrderDetail subreport will now be displayed and an extra button
will appear next to the Main Report Preview button that allows you to toggle
back and forth between each view.
4.
Click on the Print Date field in the sub report and press the delete key
to remove it.
5.
Lasso all the column header fields and drag them to the top of the
PageHeader section.
6.
Make the PageHeader height smaller by hovering your mouse over the top
of the details gray bar until the pointer turns to a vertical bar. Click and
drag the bar to just below the column header text boxes.
7.
Right click on the ReportFooter2 gray bar and select Suppress from the
pop-up menu.
8.
Right click on the LineTotal field in the Details section and select
InsertàSummary… from the pop-up
menu.
Leave the default settings and
click the OK button. This will add a total field to
ReportFooterSection1(Report Footer a) and unhide the section.
9.
Click on the Main Report Preview button to display the report.
The report is now displayed correctly without the print date
and the extra spacing for the sections.
