Have you ever gone to a web site that allows you to view a pie chart and then are able to click on the data and drill into the details that make up the total? Well, Crystal Reports has a control called the CrystalReportPartsViewer that allows you to easily create this functionality in your own sites.  This article will create a web page to display the sales data from the AdventureWorks sample database by Country, then by State, and then by City. I'll first show you how to display the data as a table and allow the user to click on the data to drill into the details.  I'll then change the report so the data is displayed as a pie chart and incorporate the same drilling functionality.

This article uses the AdventureWorks sample database that can be downloaded from http://msftdbprodsamples.codeplex.com/releases/view/37109 for SQL Server 2008.  Download and install the AdventureWorks2008_SR4.exe file.  The sample code is written using Visual Studio 2008 but it will work with Visual Studio 2005 as well.  If you do not have SQL Server 2008 you can use SQL Server 2005 but you'll need to download the 2005 AdventureWorks samples.

The goal of this article is to create web pages that look like the following images.

Your user can click on a section of the pie chart to drill into the details of each section.  This makes for a slick interface and a professional looking application.

You can download the sample code for this application here.

1.    Launch Visual Studio 2008.

2.    Select FileàNew Web Site from the menu.

3.    Choose ASP.NET from the list of templates.  You can select File or HTTP from the Location drop down list.  For this sample I'll use File.

4.    Set the Web Site Name to AdventureWorksPartsViewer.  The path should be "..\Visual Studio 2008\WebSites\AdventureWorksPartsViewer"

5.    Select Visual C# from the Language drop down list and then click OK.

Visual Studio will create a new web site and add a Default.aspx page and web.config file.  Now you need to add a Crystal Report file to your site.

1.    Right click on the web site in your Solution Explorer.

2.    Select Add New Item… from the pop-up menu.

3.    Click on Crystal Report from the templates.  If you don't see Crystal Report as an option then you need to run the Visual Studio 2008 installation and select the option to install Crystal Reports.  It comes free with Visual Studio 2008.

4.    Change the name of the report to Sales.rpt.  The Language drop down should already be set to Visual C# but if it isn't do so.

5.    Click the Add button.

Visual Studio will add the Crystal Report file to your web site and bring up the Crystal Reports Gallery dialog.  You may be prompted to register your product if this is the first time you're using Crystal Reports.  You can skip registering the product but it will keep prompting you every time you open a report so it's worth taking the extra minute or two to register.  Once you get to the Crystal Reports Gallery dialog you should select the "Using the Report Wizard" option and click OK.

The first step in the Report Wizard asks you to connect to a database.  Follow these steps to connect to the AdventureWorks database.

6.    Click the plus sign (+) next to the Create New Connection node.

7.    Click the plus sign next to the OLE DB (ADO) node.

8.    Another dialog box will appear.  This asks you which provider you want to use to connect to the database.  Scroll down the list and select SQL Native Client.  Click the Next button.

9.    The next screen allows you to enter the name of the database server, the SQL login, the password, and the database that you want to connect to. You should fill in your server name.  You can optionally use Integrated Security which means you'll connect to the database using your Windows Login. It doesn't matter if you use Integrated Security or a SQL Login as long as whichever you use has the correct permissions to read the tables in the database. I'll use Integrated Security for this example.

10. Click the Next button and then click the Finish button.

11. You should now see your server name appear under the OLE DB (ADO) node and the AdventureWorks database should appear under your server.

12. Click the plus sign next to the AdventureWorks database to view the list of schemas defined in the database.

13. Click the plus sign next to Sales and the click the plus sign next to Tables.

14. Scroll down and left click on the SalesOrderHeader table.  Click the > button to move this table to the list of selected tables.

15. Scroll up to the Person schema and add the Address, CountryRegion, and StateProvince tables to the list of selected tables.

16. Click the Next button.  The next screen allows you to define the links between the tables.  By default Crystal will try to figure out the links automatically.  Your screen should look like the following image.  If the links are not defined correctly than simply click and drag the fields to create a link between any two tables.  Once the links are defined you can click the Next button.

17. The next screen ask you to select the fields you want to display on the report.  These fields will be displayed in the Details section of the report.  Select the CountryRegion.Name, StateProvince.Name, Address.City, and SalesOrderHeader.TotalDue fields.  Click the > button to move the field in the Fields to Display list.

18. Click the Next button.  The next screen asks you which fields you want to group by.  For this report we want to show the data summarized at the Country level first, then by StateProvince, and then by the City.  This will create the drill through hierarchy we want to achieve in the web page.  Move the fields to the Group By list by clicking on the > button.

19. Click the Next button.  The next screen asks you which summary fields you would like to display on the report.  By default, Crystal will create a summary field for each group you defined in the previous screen.  Accept the default settings and click the Next button.

20. The next screen asks if you want to sort the groups.  Leave the default settings and click the Next button. 

21. The next screen asks if you want to add any charts to the report.  Leave this set to none for now.  Later in the article we'll add the graphs to the report.  Click the Next button.

22. The next screen allows you to specify selection criteria.  You use this if you want to filter the data on the report but we'll leave this blank.  Click the Next button.

23. The next screen allows you to select a layout for the report.  Leave the setting set to Standard and click the Finish button.  The report is now added to the project and should look like the following image.

The next step is to define the objects in the report that will appear as links to the user in a web page.  You start at the highest level of summation and define the lower level of details.  For this report we'll start at the Country level.  From within a country you can drill to a State or Province.  From within a State or Province you can drill into a City.  From within a city you can drill into the details records that make up the total.

1.    The first step is to define the starting point.  Right click on the Group #1 Name field in the GroupFooterSection1 section.  Click Copy from the pop-up menu.

2.    Right click any free space on the report and select ReportàReport Options… from the pop-up menu.

3.    The Report Options dialog should appear.  Click the Paste Link button in the Initial Report Part Settings section.

4.    The Object Name tells the viewer which object to display.  For this report we want to show the name and the total for that group so append ;SumofTotalDue1 to the Object name.  The Object Name should now be GroupNameName2;SumofTotalDue1.

5.    Click the OK button.

6.    Now that the initial view is setup you need to define what the drill down path is when the user clicks on the total that is displayed in the report.  Right click on the summation field that is in GroupFooterSection1.  Select Format Object from the pop-up menu.

7.    Click the Hyperlink tab.  Select the Report Part Drill Down option.

8.    Expand the plus sign next to GroupFooter2 and move GroupNameName4 to the Fields to Display list by clicking the > button.  Now move the SumOfTotalDue2 to the Fields to Display.

9.    Click the OK button.  When the user clicks on the field they will drill into the total field for that country.

10. Right click on the summation field in GroupFooter#2.  Select FormatObject from the pop-up menu.  Click on the Hyperlink tab and select Report Part Drilldown. 

11. Expand GroupFooter#3 and move the GroupNameCity2 and SumofTotalDue3 to the Fields to Display list.  Click the OK button.

12. Right click on the summation field in GroupFooter#3.  Select FormatObject from the pop-up menu.  Click on the Hyperlink tab and select Report Part Drilldown.

13. Expand the Details and move Name1, Name2, City1, and TotalDue1 to the Fields to Display list.  Click the OK button.

That's all you need to do in the report.  Now you need to create the web page that will display the parts.

1.    Open the Default.aspx page in Design mode.

2.    Navigate to the Reporting section in the Toolbox and find the CrystalReportPartsViewer control.  Drag this control onto the Default.aspx page.

3.    Double click on the web page to view the code behind. 

4.    Add the following using statements.

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

5.    Add the following code to the Page_Load event.

ReportDocument customersReport = new ReportDocument();
 
string reportPath = Server.MapPath("Sales.rpt");
 
customersReport.Load(reportPath);
 
SetTableLocation(customersReport.Database.Tables);
 
CrystalReportPartsViewer1.ReportSource = customersReport;

6.    Add the SetTableLocation method.

private void SetTableLocation(Tables tables)
{
    ConnectionInfo connectionInfo = new ConnectionInfo();
 
    connectionInfo.ServerName = @"YOURSERVER";
    connectionInfo.DatabaseName = "AdventureWorks";
    connectionInfo.UserID = "YOURSQLLOGIN";
    connectionInfo.Password = "YOURPASSWORD";
 
    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
        TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
        tableLogOnInfo.ConnectionInfo = connectionInfo;
        table.ApplyLogOnInfo(tableLogOnInfo);
    }
}

7.    Be sure to change the ServerName, UserID, and Password to your settings.

8.    Click the Run button to start the project.  Make sure the Default.aspx page is set as the startup page.  You should see the following page.

9.    Click on the $82,425,341.28 next to the United States.  You should see the following page.

10. This displays all the states within the United States.  The Next link will allow you to scroll through the list.  If you wanted to display more than 10 at a time you can change the BatchSize property of the PartsViewer control.

11. Click on the $308,827.13 next to Idaho.  This will display the cities within Idaho with an order.  You should see the following page.

12. Click on the $9,254.26 next to Sandpoint.  This will drill into the individual detail records that make up this total.

That was pretty easy.  Now let's change the report so that it displays a pie chart that a user clicks on to drill through instead of clicking on the numbers.

1.    Close your browser to stop debugging.

2.    Open the Sales.rpt report in Visual Studio.

3.    Right click on an empty location in the report and select InsertàChart from the pop-up menu.

4.    The Chart Expert should appear.  Choose Pie Chart for the Chart type.  Click on the Data tab.

5.    Change the placement to Footer.  Make sure the CountryRegion.Name is selected in the On Change of drop down list.  Make sure the Sum of SalesOrderHeader.TotalDue is selected in the Show drop down list.  Click the OK button.

6.    Right click on an empty location again in the report and select InsertàChart from the pop-up menu.

7.    Choose Pie Chart as the Chart Type and click on the Data tab.

8.    Change the Placement to For each CountryRegion.Name change.

9.    Choose the Footer option for the placement.  Make sure StateProvince.Name is selected in the On change of drop down list.  Make sure the Sum of SalesOrderHeader.TotalDue is selected in the Show drop down list.  Click the OK button.

10. Right click on an empty location again in the report and select InsertàChart from the pop-up menu.

11. Choose Pie Chart as the Chart Type and click on the Data tab.

12. Change the Placement to For each StateProvince.Name change.

13. Choose the Footer option for the placement.  Make sure Address.City is selected in the On change of drop down list.  Make sure the Sum of SalesOrderHeader.TotalDue is selected in the Show drop down list.  Click the OK button.

14. Now you need to define the entry point to the report and the links.

15. Right click on the graph in ReportFooterSection1. 

16. Select Copy from the pop-up menu.

17. Right click on an empty location in the report and select ReportàReport Options… from the pop-up menu.

18. Click the Paste Link button.  This should add Graph1 to the Object Name text box.  Click the OK button.

19. Right click on the Graph in ReportFooterSection1.  Select FormatObject from the pop-up menu.

20. Click the Hyperlink tab.  Select the Report Part Drilldown option.

21. Expand GroupFooter#1a and move the Graph2 object to the Fields to Display list.  Click the OK button.

22. Right click on the Graph in GroupFooterSection4.  Select FormatObject from the pop-up menu.

23. Click the Hyperlink tab.  Select the Report Part Drilldown option.

24. Expand GroupFooter#2a and move the Graph3 object to the Fields to Display list.  Click the OK button.

25. Right click on the graph in GroupFooter#2a.  Select FormatObject from the pop-up menu.

26. Click the Hyperlink tab.  Select the Report Part Drilldown option.

27. Expand Group Footer #3 and move the GroupNameCity2 and SumofTotalDue3 to the Fields to Display list.  Click the OK button.

Run the project again and you should see the following page.

Click on Canada and you will see a pie chart of the Provinces in Canada.

Click on Quebec.  This will show you a pie chart of the cities in Quebec that have placed orders.

Click on Brossard.  This will show you the total for Brossard.

Click on the $719,967.81.  This will display the details of the orders from Brossard.

This article showed you how to create drill down functionality in an ASP.NET application using Crystal Reports and the CrystalReportPartsViewer control. As you can see it is relatively trivial to set up.  Hopefully you can take this example and apply it to your own requirements. Crystal Reports is a great tool for integrating reports directly in your ASP.NET application and best of all it is free with Visual Studio. Good luck on your project and happy coding.