A look at SAP Crystal Reports for Visual Studio 2010
 
Published: 20 Jul 2010
Abstract
This article will review some of the new features of SAP Crystal Reports for Visual Studio 2010 and build a sample application using ASP.NET and SQL Server 2008.
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 26619/ 50

Introduction

If you installed Visual Studio 2010 you probably noticed something missing.  Yes, Crystal Reports is no longer installed as part of Visual Studio 2010.  But fear not, it didn't disappear.  It just needs to be downloaded separately.  The BETA for SAP Crystal Reports for Visual Studio 2010 can be downloaded here.  The production ready version is due out sometime in summer 2010.  Once you download and install it, it will appear in Visual Studio 2010 just like you are used to. 

One of the nice new features in the report viewer control is that reports can now be sorted dynamically by the end user.  There is also a new parameter panel that is displayed in the viewer control that allows the user to easily change parameters on a report.  Another nice feature is that the user can dynamically filter data once it has been retrieved from a database.  This article will review these new features with example code using the AdventureWorks sample database provided by Microsoft.  You can download the AdventureWorks database here.  Once you've installed the database you need to create a SQL Login that the ASP.NET application can use to connect to the database.  My sample code expects a SQL Login to be called "aspalliance" and the password should be set to "aspalliance".

Create a New Web Application in Visual Studio 2010

Before getting into the sample code you should first create a new ASP.NET web application using Visual Studio 2010.  Follow these steps to create the solution.

1.    Launch Visual Studio 2010.

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

3.    Click on Visual C# from the list of installed templates.

4.    Select ASP.NET Web Site from the list of templates.

5.    Set the Web Location drop down to File Location.

6.    Change the name of the web site to "C:\...\Documents\Visual Studio 2010\WebSites\SAPCrystalReports2010" where … points to your user path.

7.    Click the OK button.

The new web site should be created.  In Visual Studio 2010 when you create a new web site, by default, Visual Studio will add the Default.aspx and web.config files but also a host of other files that are there for best practice purposes such as a master page, an about page, and a Scripts and a Styles folder.  If you prefer not to have these files and folders created then choose the "ASP.NET Empty Web Site" template when creating a new site.

Using the New Sorting Control

A great new feature in SAP Crystal Reports for Visual Studio 2010 is the dynamic sorting control.  This allows user to change the sort order of the report while they are viewing it in the report viewer.  There are up and down arrows placed next to the column headers that the user can click on to sort ascending or descending.  This is a feature that has been a long time coming in my opinion.  Here are the steps to show the sorting control in your report.

1.    Right click on you project in the Solution Explorer and select Add New Item… from the pop-up menu.

2.    Select Crystal Reports from the list of templates and rename the file EmployeeList.rpt.  Click the OK button.

3.    The Crystal Report Gallery dialog box will appear.  Select "As a Blank Report" and click the OK button.

4.    A blank Crystal Report should appear in the designer.  Now you need to tell the report what database to connect to so you can design the report.  In the Field Explorer window you should see the Database Fields node.  Right click on this node and select Database Expert…

5.    The Database Expert dialog box should appear.  Under the Available Data Sources list you should see the "Create New Connection" node.  Click the plus sign next to this node.

6.    Click the plus sign next to OLE DB (ADO).  The OLE DB (ADO) dialog box should appear.  Select "Microsoft OLE DB Provider for SQL Server" from the list and click the Next button.

7.    Enter the Server name, User ID, Password, and select the AdventureWorks database from the list.  Click the Finish button.  If you entered all the correct information the dialog box should close.  If you get an error fix your settings and click the Finish button again.

8.    You should see the AdventureWorks database listed under the OLE DB (ADO) connections.  Click the plus sign next to the database name. 

9.    Click the plus sign next to the Person schema and then click the plus sign next to tables.  This should list all the tables in the Person schema.

10. Hold down the Ctrl button and left click on the Address, Contact, and StateProvince tables.  Click the > arrow button to move these into the list of Selected tables.

11. Expand the Human Resources schema and expand the tables under it.  Move the Employee and the EmployeeAddress tables to the list of selected tables.

12. Click the OK button.  The Links dialog should appear.  This lets you define the joins between the tables.  The links should be defined as shown in the following diagram.

13. Click the OK button again.

14. The Database Fields node in the Field Explorer should now have a plus sign next to it.  Click the plus sign to view the tables and then click the plus sign next to the Contact table to view the fields available for the report.

15. Drag the Last Name field to the details section of the report.

16. Drag the Address.City, StateProvince.StateProvinceCode, and Employee.HireDate to the details section.

17. Now you must define the sorting within the report.  From the main menu select Crystal ReportsàReportàRecord Sort Expert…. 

18. Move all 4 fields to the Sort Fields list and click the OK button.

19. Right click on the LastName textbox in the Page Header section.  Select Bind Sort Control… from the pop-up menu.

20. Choose the Contact.LastName field and click the OK button.  Do the same for the other three fields.

21. Save your report.

22. Now you need to add a page to view the report.  Right click on the project in the Solution Explorer and select Add New Item… from the pop-up menu.

23. Choose WebForm from the list of templates and change the name to Viewer.aspx.  Click the Add button.

24. Drag the CrystalReportViewer control from the toolbox to the web form.

25. Open the code behind page and add the following using statements.

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

26. Add the following code:

protected void Page_Load(object sender, EventArgs e)
{        
    ReportDocument report = new ReportDocument();
    report.Load(Server.MapPath("EmployeeList.rpt"));
 
    SetTableLocation(report.Database.Tables);
 
    CrystalReportViewer1.ReportSource = report;             
}
 
private void SetTableLocation(Tables tables)
{
    ConnectionInfo connectionInfo = new ConnectionInfo();
 
    connectionInfo.ServerName = "(local)";
    connectionInfo.DatabaseName = "AdventureWorks";
    connectionInfo.UserID = "aspalliance";
    connectionInfo.Password = "aspalliance";
 
    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
        TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
        tableLogOnInfo.ConnectionInfo = connectionInfo;
        table.ApplyLogOnInfo(tableLogOnInfo);
    }
}

27. This code will open the report and bind it to the viewer control.  Be sure you change the ServerName, UserID, and Password in the SetTableLocation method.

28. Set this page as the startup page and run your project.  You should see two arrows next to each column header.

Clicking on the up arrow will sort the report ascending by that column.  Click the down arrow sorts descending.

Filtering Saved Data

Another nice new feature of Crystal Reports is the ability to dynamically filter the report without writing any code.  This is done with parameter fields and the new Saved Data option for the Selection Formula.  Assuming you followed the steps above you should close your browser to stop debugging.  Follow these steps to learn how to add this new feature to the Employee List report.

We'll change this report so the user can filter the report by an employee's hire date.

1.    Right click on the Parameter Fields node in the Field Explorer and select New… from the pop-up menu.

2.    Enter Hire Start Date for the Name.

3.    Change the Type to Date.

4.    Click the OK button.

5.    Right click on the Parameter Fields node again and select New… from the pop-up menu.

6.    Enter Hire End Date for the Name.

7.    Change the Type to Date.

8.    Click the OK button.

9.    From the main menu select Crystal ReportsàReportàSelection FormulaàSaved Data….

10. Enter the following formula:

if {Employee.HireDate} >={?Hire Start Date} and {Employee.HireDate} <= {?Hire End Date} then
   true
else
   false

11. Click the Save and close button.

12. Run the report again.  This time when it runs the user will be prompted to enter the Hire Start and End dates.

13. Enter 1/1/2000 for the Hire Start Date.

14. Enter 12/31/2000 for the Hire End Date.

15. Click the OK button.  You should only see records for employees hired in the year 2000.

16. On the left hand side of the viewer you should see a question mark inside parenthesis. (?).  Click on this.  This will display the parameters panel.

17. Change the start date to 1/1/2001 and the end date to 12/31/2001. 

18. Click the Apply button.  You should now see the employees hired in 2001.

This is a nice new way to display the parameters in a report rather than have the user refresh or run the report again.

Dynamic Grouping

The last feature this article will demonstrate is how to use the parameter panel to change how a report is group at runtime.  By allowing the user to change groups at runtime they can see summations, counts, and averages based on the groups without having to create separate reports for each group. 

1.    Stop the debugger if it is running.  Right click on the Parameter fields in the Field Explorer and select New…

2.    Enter "Group By" for the name of the formula.

3.    In the Value Grid enter "City", "State\Province", and "Hire Date (Year)".

4.    In the Value Options grid, change the Allow Custom Values setting to false.

5.    Click the OK button.

6.    Right click on the Formula Fields in the Field Explorer and select New… from the pop-up menu.

7.    Enter "Grouping Field" for the name and click the OK button.

8.    Enter the following formula:

if {?Group By} = "City" then
    {Address.City}
else if {?Group By} = "State\Province" then
    {StateProvince.StateProvinceCode}
else if {?Group By} = "Hire Date (Year)" then
    totext(Year({Employee.HireDate}), "####")

9.    This will resolve what the user has entered into the correct database field or formula.

10. Click the Save and close button.

11. Now you need to add a group that groups by this formula.  Right click on the white space in the report and select InsertàGroup from the pop-up menu.

12. Set the condition field to the "Grouping Field" formula and click the OK button.

13. The group should now be added to the report and a group header and footer section should appear in the design of the report.

14. Now add a count to the group header.  When you preview the report and change how the grouping works this number should change.

15. Right click on the Last Name field in the Details section.

16. Select InsertàSummary from the pop-up menu.

17. Change the "Calculate This Summary" drop down list to "Count".

18. Change the "Summary Location" drop down list to "Group #1:GroupingField - A".

19. Click the OK button.  This will add the count to the group footer section.  Drag the field to the group header section.

20. Run the report again.  This time enter a start date of 1/1/2000 and an end date of 12/31/2001.  Set the grouping field to City.  Your report should look like the following image.

21. Display the Parameter Panel by clicking on the (?) on the left side.  Change the group to Hire Date (Year) and click the Apply button.

The report should now be displayed grouped by the year of the employee's hire date.

Summary

This article demonstrated some of the new features of SAP Crystal Reports for Visual Studio 2010.  The sorting controls and the parameter panel give the end user the ability to answer business questions about their data without having to rely on a developer to create a new report with different sorts or filters.  These are great new features which make it worth it to upgrade sooner rather than later.

Good luck on your project and happy coding.



User Comments

Title: Need another sample   
Name: Matt Slay
Date: 2010-07-21 5:29:44 AM
Comment:
Nice intro showing how it all from within CR, but I'd like to see a sample where I had data from a LINQ query (EF or Linq To Sql) or even DataTables, where the querying and filtering happens in code inside my DAL, and then how that data can be passed into the report.

Product Spotlight
Product Spotlight 



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


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 4:15:16 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search