Building Reports Using ASP.NET and Crystal Reports - Part 2 - Quarterly Sales Report
page 7 of 15
by Vince Varallo
Feedback
Average Rating: 
Views (Total / Last 10 Days): 63653/ 175

Step 6: Create the cross tab

The cross tab functionality in Crystal Reports is a great way to summarize data. Cross tabs let you pivot data so that a field's value is displayed as columns in a table. Cross tabs are particularly useful when creating any date driven reports that need to see summarized data. This report displays each employee for the territory as the rows and the fiscal year's quarter as the column.  Before we create the cross tab we need to create a formula that will tell us the fiscal quarter for the SalesOrderHeader.OrderDate. We then will use this formula for the columns in the cross tab.

1.    In the Field Explorer window right click on the Formula Fields and select New… from the pop-up menu.

2.    The Formula Name dialog should appear. Enter FiscalQuarter for the name and click the Use Editor Button.

3.    Enter the following formula:

Listing 1

'Q' + totext(DatePart("q", {SalesOrderHeader.OrderDate}), 0) + '/' + 
totext(Year({SalesOrderHeader.OrderDate}), 0, '')

This formula creates a string based on the SalesOrderHeader.OrderDate. If the order date was 4-14-2009 then the formula would return 'Q2/2009.'

4.    Click the Save and close button. 

The cross tab also wants to use the employee's name as the row; however, we need to use the name formatted as Last Name, First Name. We must create another formula to accomplish this.

5.    In the Field Explorer window, right click on the Formula Fields and select New… from the pop-up menu.

6.    The Formula Name dialog should appear. Enter EmployeeName for the name and click the User Editor button.

7.    Enter the following formula:

Listing 2

{Contact.LastName} + ", " + {Contact.FirstName}

8.    Click the Save and close button.

9.    Now you can create the cross tab using these formulas.

10. Right click on the white space in Group Header #1 section. Select InsertàCross-Tab… from the pop-up menu.

11. Drop the cross tab in the Group Header #1 section on the left side of the report. The Cross-Tab Expert dialog should appear.

Figure 7

12. Click on the FiscalQuarter field from the list of Available Fields.

13. Click the > button next to the Columns list.

14. Click on the EmployeeName field from the list of Available Fields.

15. Click the > button next to the Rows list.

16. Now you need to specify which field to summarize. For this example we will summarize the SalesOrderHeader.SubTotal field. Click on this field in the list of Available Fields.

17. Click the > button next to the Summarized Fields list. By default, this uses the summary operation on this field which is what we want. You could change the summary operation to average, minimum, maximum, count, and a host of others. To see all available options click on the SalesOrderHeader.SubTotal field in the Summarized Fields list and click the Change Summary button.

Figure 8

18. Click the OK button. You should now see the cross tab in the Group Header #1 section.


View Entire Article

User Comments

Title: specialized in wholesale cheap nike nfl jerseys   
Name: wholesale cheap jerseys
Date: 2012-08-06 4:16:47 AM
Comment:
we are specialized in wholesale cheap nike nfl jerseys , nfl jerseys , mlb jerseys , nhl jerseys , nba jerseys , soccer jerseys ,hoodies, sports caps.

http://jerseymember.com/Nike-NFL-Jersey-c134.html

http://jerseymember.com/NFL-Jersey-c68.html

http://jerseymember.com/MLB-Jerseys-c94.html

http://jerseymember.com/NHL-Jerseys-c96.html

http://jerseymember.com/NBA-Jerseys-c77.html

http://jerseymember.com/NCAA-Jersey-c114.html

http://jerseymember.com/Soccer-Jerseys-c123.html

http://jerseymember.com/Customized-Jerseys-c116.html

http://jerseymember.com/Customized-Jerseys-c116.html

http://jerseymember.com/Hoodies-c136.html

http://jerseymember.com/Sports-Caps--c119.html

http://jerseymember.com/Others-c131.html
Title: nfl jerseys cheap   
Name: NIKE NFL jerseys
Date: 2012-07-02 10:11:39 AM
Comment:
http://www.jersey2shop.com
http://www.cheapjersey2store.com
http://www.jerseycaptain.com
http://www.yourjerseyhome.com
We are professional jerseys manufacturer from china,wholesal.cheap nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes
Cheap NFL,NBA,MLB,NHL
,heap jerseys,2012 nike nfl Jerseys,nba jersey and shorts,oklahoma city thunder jersey,official jeremy lin new york knicks jersey,NFL Jerseys Wholesale,blake griffin jersey blue,NFL jerseys For Sale online.All Our Jerseys Are Sewn On and Directly From Chinese Jerseys Factory
,Wholesale cheap jerseys,Cheap mlb jerseys,]Nike NFL Jerseys,Cheap China Wholesae,Wholesale jerseys From China,2012 nike nfl Jerseys,Jerseys From China,,2012 nike nfl Jerseys,Revolution 30 nba jerseys,jersey of nba chicago bulls direk rose ,nfl jerseys,green bay packers jerseys wholesale,Buffalo Bills nike nfl jerseys sale,good supplier soccer jerseys,cool base mlb jerseys,Revolution 30 nba jerseys,2012 stanley cup nhl jersey,
We are professional jerseys manufacturer from china,wholesal.cheap nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes. www.yourjerseyhome.com
Title: 2012 NFL jerseys   
Name: NIKE NFL jerseys
Date: 2012-07-02 10:10:03 AM
Comment:
http://www.jersey2shop.com
http://www.cheapjersey2store.com
http://www.jerseycaptain.com
http://www.yourjerseyhome.com
We are professional jerseys manufacturer from china,wholesal.cheap nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes
Cheap NFL,NBA,MLB,NHL
,heap jerseys,2012 nike nfl Jerseys,nba jersey and shorts,oklahoma city thunder jersey,official jeremy lin new york knicks jersey,NFL Jerseys Wholesale,blake griffin jersey blue,NFL jerseys For Sale online.All Our Jerseys Are Sewn On and Directly From Chinese Jerseys Factory
,Wholesale cheap jerseys,Cheap mlb jerseys,]Nike NFL Jerseys,Cheap China Wholesae,Wholesale jerseys From China,2012 nike nfl Jerseys,Jerseys From China,,2012 nike nfl Jerseys,Revolution 30 nba jerseys,jersey of nba chicago bulls direk rose ,nfl jerseys,green bay packers jerseys wholesale,Buffalo Bills nike nfl jerseys sale,good supplier soccer jerseys,cool base mlb jerseys,Revolution 30 nba jerseys,2012 stanley cup nhl jersey,
We are professional jerseys manufacturer from china,wholesal.cheap nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes. www.yourjerseyhome.com
Title: asp   
Name: sumti
Date: 2011-04-10 7:27:41 AM
Comment:
very nice code
Title: Nice Article   
Name: Rick
Date: 2009-12-01 11:18:40 AM
Comment:
The example was very easy to understand and follow, thanks for taking the time to put these together.
Title: Awesome   
Name: Rusty
Date: 2009-08-27 10:36:14 AM
Comment:
Awesome job. I am a brand new CR user and I found your article to be easy to understand, follow, and very helpful. Thanks and keep them coming.
Title: Superb   
Name: ramakrishna
Date: 2009-08-01 6:51:52 AM
Comment:
This article is very good to customise my website,
Title: great work   
Name: Ankit Agarwal
Date: 2009-07-28 12:48:39 AM
Comment:
u have done a great work
this article is really helpful
Title: Great Job   
Name: yin min
Date: 2009-07-26 5:52:54 AM
Comment:
Thanks for your step by step article.
Everyone who is new at crystal are looking for such a simple and very useful article.
Thanks again.
Title: Well Done   
Name: Muhammad Aijaz Rajput
Date: 2009-07-10 10:47:53 AM
Comment:
This is an excellent article. Thanks. I realy need the detailed step-by-step article approach that you take.
Title: Source coming soon   
Name: Vince
Date: 2009-06-22 9:20:19 AM
Comment:
The source should be posted soon.
Title: Excellent   
Name: Walter
Date: 2009-06-16 4:03:31 AM
Comment:
Hi Vince,

another excellent article. Thanks. I really like the detailed step-by-step approach that you take.

Will the source be available as a download?

Regards

Product Spotlight
Product Spotlight 



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


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-05-20 6:08:09 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search