Creating a Crosstab Report in Visual Studio 2005 Using Crystal Reports
 
Published: 19 Oct 2006
Abstract
This tutorial describes the various steps in generating a cross-tab report using the Crystal Report elements that are integrated with the Visual Studio 2005 default installation.
by Jayaram Krishnaswamy
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 118614/ 137

Introduction

Reporting is one of the major tasks in any enterprise and reporting software occupies a prominent position in enterprise software suite.  There are multitudes of reporting software vendors; among them Crystal Reports has a large following with a variety of products designed for size and costs ranging from small business to enterprise.  It is relatively easy to retrieve data from a database and display it using any of the bound controls. This is especially so in ASPNET 2.0 where the amount of code you need to write has been drastically reduced.  However, to present the data in a formal, board-room friendly format and distribute it in hardcopy or for look-up is challenging.

Microsoft has been bundling Crystal reports ever since the VB days and it ships with every version of VS2005 as it did with VS2003.  It is well integrated with VS Studio 2005 and uses the VS enhancements in useful and intelligent ways.  It makes use of themes, smart tags, css styling, reporting source and data source control features, automatic data binding and many other features of ASPNET 2.0.  For highly functional and aesthetically pleasing, web and window based report generation, Crystal Report is ideally suited.  It is very versatile and can use data from a variety of databases and other non-traditional sources.  The version that comes with the default install of VS 2005 needs to be upgraded to Crystal Reports 11 to use even more enhancements.

Overview

This tutorial shows, step-by-step, how you may generate a cross-tab report using the built-in support for Crystal Reports in VS 2005.  The database used for the report is the Northwind database, a sample database in Office 11 (MS Access 2003).  The window based report uses the crystal reports controls available in VS 2005 IDE Toolbox.  The readers will benefit reading the other articles by the author on Crystal Reports and related materials, Crystal Reports with OLAP data, Publishing Crystal Report as Web Service, and On Accessing Data From An OLAP Server Using MS Excel (1,2,3).

Cross-tab reports

In order to access useful business intelligence from enterprise data for comparison or trend analysis, data in the form of a list is less useful.  Cross-tab reports provide the support to extract data for decision making.  Cross-tab reports are based on cross-tab objects; objects which are represented by a two dimensional grid that has data values based on a logic that you specify. Figure 1 shows an example of a cross-tab report from an expense report of an employee.  This report shows at a glance how much the employee spent on what date in addition to the total the employee spent for the trip.  This is more useful than just a day-to-day listing of expenses.

Figure 1

 

Query used in the present tutorial

While the original data comes from tabulated values, the reports are usually based on queries run against the database.  This tutorial uses a query run against the Northwind database.  The Northwind database has data related to products of various categories sold and shipped for several years by a number of different shippers.  Listing 1 shows the query run against the sample database for obtaining sales made in 1997 shown in Figure 2.  In order to obtain this data a number of tables are joined.

Listing 1

SELECT Categories. CategoryName,
 Products.ProductName, Sum (CCur ([Order
 Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductSales,
 "Qtr " & DatePart("q",[ShippedDate]) AS ShippedQuarter
FROM (Categories INNER JOIN Products ON
 Categories. CategoryID=Products.CategoryID) INNER JOIN (Orders INNER JOIN
 [Order Details] ON Orders.OrderID=[Order Details].OrderID) ON
 Products.ProductID=[Order Details].ProductID
WHERE (((Orders.ShippedDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY Categories.CategoryName,
 Products.ProductName, "Qtr " &
 DatePart("q",[ShippedDate]);

Figure 2

<img border=0 width=528 height=470 src="/ArticleFiles/1041/image002.jpg">

While this data in the above format is correct and valid, it is not very useful if you want to get a summarized view, such as the one we saw in the expense report.  In this tutorial the data will be used to create a cross-tab report that will answer questions regarding quarterly sales, total sales of 'Boston Crab Meat', etc.

Creating a Crystal Report Project

Add a crystal report template to a project

Highlight the windows project and right click to pick Add, New Item…as shown in Figure 3.

Figure 3

<img border=0 width=297 height=413 src="/ArticleFiles/1041/image003.jpg">

This opens up the Add New Item window where you click on the Crystal Report template from a list of Visual Studio Installed templates as shown in Figure 4.  It adds a default Crystal Report, Crystal Report1, if this is the first report in the project.  This may be changed to something different.  This name of the report created for this tutorial is Garnet.rpt.

Figure 4

 

Report Wizard Steps

As soon as you click the button Add in Figure 4, a Garnet.rpt file is added to the project and a Crystal Report Gallery window appears in the design view of the Garnet.rpt as shown in Figure 5.

You may use this window to create a new crystal report document by three different means.  If you use the wizard, you can further choose an expert to create three different kinds of reports. However, when you choose the option, As a Blank Report, you will be starting from scratch.  In this tutorial the choice made is to use the Report Wizard and the Cross-Tab expert to create a cross-tab report.  You can see the outline of a cross-tab grid with rows, columns, and cells in the Crystal Reports Gallery window.

 

 

 

 

 

 

 

 

 

 

 

Figure 5

When you click on the button OK, you open up the next window, Cross-Tab Report Creation Wizard.  This is an important step in the report creation process.  You need to choose the data which may be saved in any of the several containers listed in the left half of Figure 6 in the Available Data Sources area.  Since this tutorial will create a new connection to a data source, the Create a New Connection node is expanded to look for the source of Northwind data.  The OLE DB (ADO) sub-node, where such data could be found, is empty.  Another obvious container for Access data (DAO) is also available.  Since Access 2003 uses ADO extensively, the OLE DB is used.

Figure 6

 

Double click the OLE DB (ADO) node to open up the next window, OLE DB (ADO).  This provides an extensive list of data providers which also lists the Microsoft Jet 4.0 OLE DB Provider.

Figure 7

 

Highlight the Microsoft Jet 4.0 OLE DB Provider and click on the Next button.  This opens the window shown in Figure 8.  In this window you must provide the connection information to the data source.  When it is opened the database type and UserID field are automatically added.  You can click on the ellipsis button () across the Database Name item to open the file directory on your machine.  You then navigate to access the file required as shown in Figure 9.  When you choose the file and click open, the Databse Name field gets filled up.

Figure 8

 

Figure 9

Click on the button Next in Figure 8 to open the next window shown in Figure 10.  Here you may carry out modifications if necessary.  Here no changes were made.

Figure 10

 
<img border=0 width=452 height=463 src="/ArticleFiles/1041/image010.jpg">

Now click on the Finish button which takes you to the next step of the wizard.  Here you have established the connection to the Northwind.mdb file which shows all the Tables and Views contained in it.  In addition to the Tables and Views you can also set up a command using the Add Command shortcut.  The queries in MS Access 2003 are represented as Views in this window.

Figure 11

 

 

Click on the node, Views.  This expands the node as shown in Figure 12.  Here you see the query, Product Sales for 1997.

Figure 12

 

Highlight the Product Sales for 1997 in the Views node and click on the > (transfer to right) button in the middle.  This transfers the selected view to the right.

Figure 13

 

Now click on the Next button to open the window, Cross-Tab Report Creation Wizard.  This is the Cross-Tab sub task that you must complete to generate the report.  The Available Fields window shows the various fields in the report which you may verify against Figure 2.  This window is used to create the rows, columns and the summary fields needed for the cross-tab report.  It will be helpful to recall the analogy of the expense report.  This window may also be used to verify what kind of data exists in each of these fields, since we need to differentiate what is summarized information and what is not summarized information.  In this case it is obvious that Product Sales is the summarized information.

Figure 14

 

We want to show the product name as rows and should therefore highlight ProductName in the available fields list and click on the arrow > for selecting Rows as shown in Figure 15.  This will populate all the rows of the cross-tab report with product names.  You may also drag an item from the Available Fields and drop it into any of the Rows, Columns, and Summarized Fields window.

Figure 15

 

Similarly, highlight the ShippedQuarter in the available fields and click on the > arrow in the Columns related area in the Cross-Tab.  This adds the ShippedQuarter to the Columns of the cross-tab report.

Figure 16

 

Now highlight the ProductSales and click the > arrow in the Cross-Tab in the summary fields area.  This adds the ProductSales values to this area as shown in Figure 17.  When you see this sigma symbol you get the first assurance that you are placing the correct type of information. Even if you make a mistake in any of these manipulations nothing is lost.  Everything can be reset and reapplied until you get the result you expect.  Most of the items added can be removed by the < button.  Also, most of the windows have a button named Back which will take you to a previous window.

Figure 17

 

For this tutorial you have made all the necessary choices and should now click on the Finish button.  This takes you to the screen where you can make record selections.  Although you have already selected records when you chose the query, you make selections of fields not included in the fields to be included in the report should you so desire at this stage.

Figure 18

 

For this case, where all the fields are already chosen and no extra fields are showing in the available fields' area, you can skip this screen by clicking on the Next button.  This figure was included more for keeping the description of the wizard's steps complete.  When you click on the Next button, the window shown in Figure 19 will open.  This task is also optional.  If you do require a chart to be included with the report, you may choose the kind of chart in this screen.  A bar chart was chosen for this tutorial.

Figure 19

 

You may want to change the parameters in the drop-down and show quantities of interest in the chart, the defaults were assumed for this tutorial.  When you click on the Next button, you will come to the window shown in Figure 20 where you need to choose a style for the cross-tab report from a list of pre-defined styles.

Figure 20

 

You can pick each of these styles and see how they are rendered in the image area in the right. Basic - Terra Cotta style was chosen for this report.  You have come to the last step in the wizard.  When you click on the Finish button, you will be shown the Field Explorer window shown in Figure 21 and the crystal report in the design view tab as shown in Figure 22.  Figure 21 is quite similar to the Field List (or Field Explorer) from earlier versions of Crystal Reports.  This explorer lists all kinds of fields used in a report that includes database fields and non-database fields, such as those that support printing.

Figure 21

The Garnet report has two tabs at the bottom, a Main Report in design and a MainReportPreview tab which shows a preview of the data within the VS IDE.  You can make design changes and toggle the tabs to preview the report and back to make changes to the design again.

Figure 22

The "banded" structural details of Figure 22 are similar to those from earlier versions of Crystal Reports.  Both the chart and the cross-tab grid are in Section 2, the Report Header.  There is also a field "Print Date from the specified fields" node of the Field Explorer in Section 5, the report footer.  The horizontal axis showing shipped quarter has year values, but this is just some representative features and has no relation to the underlying data.  Only when you hit the Main Report Preview tab would you see the actual numbers from the underlying data.

Figure 23

 

All objects on the different areas of the report and their properties can be accessed from windows, such as the one showing in Figure 23 for the Graph 1 object.  The object is surrounded by a thin blue outline and the properties are available in the Graph 1 object's property window.  For example, the title of the graph (or chart) was changed to "Quarterly product Sales for 1997" in this window.

Figure 24 shows the fully expanded view showing all the available fields for the developer.  It includes the fields from the databse used in the report.  The Field Explorer and the Design view of the report are available for the designer at the same time and the fields can be dragged and dropped on the Design view on the appropriate sections.  For example, the Print Date field was dropped into the PageHeader by default.

Figure 24

 

Generating the Report

In order to produce a report after creating a Garnet.rpt document, a Report Viewer control on a form is needed.  The preview of the Main Report shows how the report would display in its final form.  In order to display the report on a form in the windows application, you will need to add a form to the project and then drag and drop a CrystalReportViewer control on to the form as shown in Figure 25.  The Crystal ReportViewer occupies the whole form, as shown with several of the icons grayed out.  These will become active once the CrystalReportViewer is configured to show the report by establishing the relationship between these two objects in the IDE.

Configuring the CrystalReportViewer is facilitated by the .NET feature called the smart tasks, in this case the CrystalReportViewer Tasks.

CrystalReportViewerTasks shows the various tasks that need to be performed.  Obviously, the first choice would take you through the necessary steps of creating a New Crystal Report.  In this tutorial this has already been accomplished.  The second item, Choose a Crystal Report…, would help you in choosing an existing Crystal Report that can be viewed on this viewer.

Figure 25

 

When you access the CrystalReportViewer's properties window by right clicking it, you will be able to make changes to various items.  This includes the choice of choosing a CrystalReport from among the various reports that may exist (as shown in Figure 26) where the Diamond.Garnet report will be chosen.  You may also browse for other reports that may be residing in the machine as well.

Figure 26

 

When you make the above choice, the properties pages get the binding to the report created earlier.  In addition to adding the CrystalReport to the CrystalReportViewer you may make many other choices as well.  The key elements of the CrystalReportViewerTasks also appear at the bottom of the CrystalReportViewer properties.

Figure 27

 

By associating the Diamond.Garnet [ProjectName.ReportName] report source with the CrystalReportViewer1, the binding is automatically established as shown in the Code behind the CrossTab.Designer.vb a part of which is reproduced in Listing 2.

Listing 2

Private Sub InitializeComponent ()
        Me.CrystalReportViewer1 = New Crystal Decisions. _
        Windows.Forms.CrystalReportViewer
        Me.Garnet1 = New Diamond.Garnet
        Me.SuspendLayout ()
        'CrystalReportViewer1
        Me.CrystalReportViewer1.ActiveViewIndex = 0
        Me.CrystalReportViewer1.BorderStyle = System.Windows.Forms. _
        BorderStyle.FixedSingle
        Me.CrystalReportViewer1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.CrystalReportViewer1.Location = New System.Drawing.Point (0, 0)
        Me.CrystalReportViewer1.Name = "CrystalReportViewer1"
        Me.CrystalReportViewer1.ReportSource = Me.Garnet1
        Me.CrystalReportViewer1.Size = New System.Drawing.Size (419, 497)
        Me.CrystalReportViewer1.TabIndex = 0
        'Crosstab
        Me.AutoScaleDimensions = New System.Drawing.SizeF (6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size (419, 497)
        Me.Controls.Add (Me.CrystalReportViewer1)
        Me.Name = "Crosstab"
        Me.Text = "Crosstab"
        Me.ResumeLayout (False)
    End Sub

The report is now ready for prime time.  Just build the project and make sure that the Crosstab is the Startup form and run.  You should be able to see the report now hosted on the Crosstab form as shown in Figure 28.  You may also notice that the information has exceeded the limits of a page spread to a second page.  You may use the navigation buttons at the top to go to the next page and once in the next page to come back.  The various icons in the report menu are now active for printing, zooming, searching, etc.

Figure 28

 

 

All elements on the design area are configurable.  Figure 29 shows how the Row#1 Name object can be formatted by clicking the object which brings up a context sensitive menu from which you can accomplish your task.  This will bring up the Format Editor window as shown in Figure 30.  For example, by choosing the Graph1 object the title can be changed.

Figure 29

When you choose the Format Object in the drop-down, you will be invoking the Format Editor shown in Figure 30.  The editor comes with the defaults and, as shown in Figure 30, the row elements are centered.  This was changed to "Left" as shown in Figure 30.  Now when the report is open, the rows will be aligned to the left.

Figure 30

 

Similarly the font was changed from the default of "Arial" to "Garamond," as shown in Figure 31.

Figure 31

 

 

It is possible to modify the formatting of all objects from their default values using the design interface.  When you are sufficiently satisfied, you rebuild the projects and run to display the form.  This is the completed cross-tab report of the data from the Northwind database.

Figure 31

 

 

Summary

The built in support for Crystal Report which is included in the default installation of Visual Studio 2005 uses the various new features of the Visual Studio 2005 IDE.  The report created is aesthetically pleasing, highly functional, besides being board-room friendly.  The generated report supports tasks such as printing, exporting to various formats, as well as searching for text in the report.  As the tutorial demonstrates, reports can be generated without any coding effort on the part of the developer and the reports generated can be reused by deploying in other ways, such as through web servers and web services.



User Comments

Title: Show chart of row in cross-tab   
Name: letamuns
Date: 2/20/2012 12:01:53 AM
Comment:
Hi! Thanks for your article. In case: I want to have a link of every row, when I click it, the crystalreportviewer show a chart for that row.
Title: how to print value   
Name: nithya
Date: 10/8/2011 2:58:28 AM
Comment:
how to print the values in between section4 and section5 in crystal report in asp.net
Title: Cross Tab Report   
Name: Bharti
Date: 8/2/2011 10:51:33 AM
Comment:
Hi!

Thanks for a helpful article. I have created a cross tab report and need to modify the order of records. I am sorting a field by specified order and have added another one to the sort field. At this point the records were all sorted correctly and easily. My report is not recognising this new data which I have added. I removed the sort and when I run the report the same sort is still in the report. What am I doing wrong?

Thank you.
Title: thanks   
Name: manisha
Date: 6/6/2011 5:45:03 AM
Comment:
excellent thanksssss
Title: CR   
Name: Engineer
Date: 5/31/2011 1:45:38 AM
Comment:
Thnx..helpful article
Title: Very Good   
Name: Very Good
Date: 5/21/2011 5:33:45 AM
Comment:
Thanks,
This article is very helpful.
Title: Cross tab   
Name: Reda Karim
Date: 5/8/2011 10:35:36 AM
Comment:
Thanks very much it was very helpful for me
Title: Cross tab   
Name: Gaja
Date: 8/11/2010 2:49:20 AM
Comment:
How to remove summarized field in cross tab option
Title: Crystal report cross tab   
Name: Suhail
Date: 5/31/2010 4:30:32 AM
Comment:
Hello Sir,I have the same problem in cross tab that is there is no sum in Summary field of cross tab can u pls help me how to include that
Title: Cross Tab   
Name: das
Date: 4/22/2010 7:06:56 AM
Comment:
Hi,
Is it possible to enable the "Can grow"(Fig 30) propery of a field?
If no,What is the solution?
Title: How to make a cross-tab reprt without summarizing any field?   
Name: Vicky
Date: 2/26/2010 1:31:37 AM
Comment:
Sir i've developed a Time Management System in which emplyees attendance is stored.
I've to make a cross tab report for this project, in which i've to mention Incoming Time, Outgoing etc.
But i dont have to summarize any field.
Please help me out in this regard.

Advance Thanx
Vicky
Title: CrossTab   
Name: ABJ
Date: 10/3/2009 2:13:58 AM
Comment:
Really Great.. 100 points to you.. :-)
Title: Cross Tab Crystal Report   
Name: Jayaram Krishnaswamy
Date: 7/15/2009 12:31:55 PM
Comment:
Hi Deepak,

If you would let me know how you are doing this with some more details I may be able to help you. I replied to your email instead of commenting here.

Cheers,

Jay
Title: crosstab crystal report   
Name: deepak
Date: 6/3/2009 1:23:35 AM
Comment:
sir i am not getting the sum function in the drop down list just below the summary filed in the cross tab .....

can u please help.....
i will be thankful..
regards
deepak

Product Spotlight
Product Spotlight 



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


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 10/2/2014 4:24:21 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search