Building Reports using ASP.NET and Crystal Reports - Part 7 Using Subreports to Create Advanced Reports
page 5 of 7
by Vince Varallo
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 34331/ 68

Step 4: Create the Subreport

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. 

View Entire Article

User Comments

Title: Crystal Report   
Name: B.Shah
Date: 2010-09-21 6:43:53 AM
Nice Article..Thanks
Title: Donor Database Administrator   
Name: Abdillahi Jibril
Date: 2010-01-27 11:00:37 AM
I have medium to advanced level experience in Crystal Report, and your article helps me improve my skills to be better Crystal Report Developer.
Title: crystal reports   
Name: joohitha
Date: 2010-01-05 11:47:19 PM
nice article

Tks vincey
Title: crystal reports tutor   
Name: lokesh
Date: 2009-12-28 5:02:03 AM
Thanks vincey
very nice and very help full for crystal report beginners.
i will be at

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-05-25 10:42:58 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search