Creating a Crosstab Report in Visual Studio 2005 Using Crystal Reports
page 3 of 6
by Jayaram Krishnaswamy
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 97347/ 180

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.


View Entire Article

User Comments

Title: Show chart of row in cross-tab   
Name: letamuns
Date: 2012-02-20 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: 2011-10-08 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: 2011-08-02 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: 2011-06-06 5:45:03 AM
Comment:
excellent thanksssss
Title: CR   
Name: Engineer
Date: 2011-05-31 1:45:38 AM
Comment:
Thnx..helpful article
Title: Very Good   
Name: Very Good
Date: 2011-05-21 5:33:45 AM
Comment:
Thanks,
This article is very helpful.
Title: Cross tab   
Name: Reda Karim
Date: 2011-05-08 10:35:36 AM
Comment:
Thanks very much it was very helpful for me
Title: Cross tab   
Name: Gaja
Date: 2010-08-11 2:49:20 AM
Comment:
How to remove summarized field in cross tab option
Title: Crystal report cross tab   
Name: Suhail
Date: 2010-05-31 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: 2010-04-22 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: 2010-02-26 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: 2009-10-03 2:13:58 AM
Comment:
Really Great.. 100 points to you.. :-)
Title: Cross Tab Crystal Report   
Name: Jayaram Krishnaswamy
Date: 2009-07-15 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: 2009-06-03 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-2019 ASPAlliance.com  |  Page Processed at 2019-03-23 8:58:33 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search