Cross Tab Craziness in Crystal Reports
page 2 of 5
by Jeff McWherter
Feedback
Average Rating: 
Views (Total / Last 10 Days): 66649/ 97

Getting the data to the cross tab

1.       Click on insert and select the cross tab item.

2.       Place the cross tab in the report header for this example.

3.       Right click on the cross tab and select Cross-Tab Expert.

4.       Expand the “Customers” table under the Available Fields, select the “ContactName” field, and add it to the rows collection.

5.       Expand the “Products” table under the Available fields, select the “ProductName” field, and add it to the rows collection.

6.       Expand the “Orders” table under the Available fields, select the “OrderDate field, and add it to the Columns collection.

7.       Expand the “Orders_Details” table under the Available fields, select the “UnitPrice” field, and add it to the summarized fields.

Figure 1: The Cross-Tab Expert

 

Let’s go into more detail about what we just did. First, we added 2 fields to the rows “collection”. The first field that was added was “Customers.ContactName”. For each contact name in the returned data set, a row will be printed in the cross tab. The second field added was the “Products.ProductName” field. By adding a second row to the collection, a “sub row” will be inserted for each product that the customer ordered.  The next field that was added was the “Orders.OrderDate”, to the columns collection. This field will print a column for each unique value. We will go more into options for this group later in the article. The last field that was added was the “Orders_Details.UnitPrice" to the Summarized Fields collection. This collection is where your data is summarized.

After you have completed those steps you can preview the cross tab, but this is not exactly what we may have expected to see.

Figure 2: Customer Product Total Cross Tab Default

 

The first thing we need to work on is the grouping. The requirements we specified for this report were to display the yearly totals for each product that a customer ordered.

1.       For grouping the orders, right click on the Cross Tab and select “Cross Tab Expert”.

2.       Click on the “Orders.OrderDate” field in the Columns Collection.

3.       Click on the “Group Options…” button.

4.       Select “for each year” from the "column will be printed" box.

Figure 3: Columns Group Options

While we are in the section expert, we need to change how the “Order_Detais.UnitPrice” is being summarized. The default is “Count” and we need a “Sum”

1.       Ensure you are in the “Cross Tab Expert”.

2.       Click on the “Order_Details.UnitPrice” field under the Summarized fields collection.

3.       Click the “Change Summary…” button.

4.       Select “Sum” in the "calculate this summary" box.

Figure 4: Change Summarized Fields menu

 

At this point you should have a report that looks something like this:

Figure 5:  Customer Product Total Cross Tab Fixed Group Options

 

 

 


View Entire Article

User Comments

Title: Good job   
Name: Sanjarbek Hudayberdiev
Date: 2010-12-13 1:12:32 AM
Comment:
Good introductory article.Thanks.
Title: Report with Total and Average Column   
Name: JERRY
Date: 2010-10-06 11:56:30 AM
Comment:
i was build report crosstab with column ( sum ) and ( avg ),but something wrong..
name col1 col2 col3 sum avg
A 10 10 10 40 10
B 10 10 20 10 (avg should 20/3 = 6.6)
C 10 20 30 60 20


In name B when col2 hasnt value,average looked wrong.
Title: Cross Tab Total in Time Format   
Name: Venkatesh
Date: 2010-06-18 2:33:52 AM
Comment:
Hi,
how to get time summary in total field
Title: Trainee   
Name: Mouhsine
Date: 2010-06-11 8:40:07 AM
Comment:
Hello all,
I have some difficulties with CR 2008.
My current problem is to add a row in my crosstable with evolution rate between TurnOver of year N and year N+1.
I now that is possible by Calculated Member in Cross Tab but I don't know how.
Can you help me ?
Title: Summary Differences In Cross Tabs   
Name: Phasor
Date: 2009-09-25 9:20:40 AM
Comment:
I need to create a cross tab report where the summary is the difference between the maximum and minimum values. Is there a built in summary field for this and if not how do I go about entering a custom formula in the summarizied field.

Phasor
Title: Cross-tab in subreport   
Name: Rhyan
Date: 2009-09-25 3:03:22 AM
Comment:
Hi,

I have a main report that used a cross-tab in one of the sub reports in the main report. The cross-tab expanded horizontally so I checked the Repeat On Horizontal Pages property of my text objects so that they will be displayed also on the horizontal pages. I run the report in my web application and was displayed properly including the text objects in the horizontal pages. However, when I exported the report into pdf, the horizontal pages was not included.

Is there a way to include the horizontal pages in a pdf if the cross-tab is in a subreport?

Note: Tried the cross tab in the main report and when exported to pdf, it included the horizontal pages.

Hope to get a quick response.

Thanks
Title: how to read the summary field in cross tab   
Name: Maignanamurthy
Date: 2009-09-22 9:32:58 AM
Comment:
i have created one cross tab report in vb.net
this report contains one summary field value also
it works fine
PROBLEM IS
i want to re-caluclate the summary field.
for that purpose i want to get the summary field value in Cross Tab Report.
tell me
how to read or get the summary filed value in cross tab report.
Title: Get the value of summary Field in cross tab   
Name: Maignanamurthy
Date: 2009-09-22 9:23:48 AM
Comment:
i want to re calculate the cross tab summary field value.
how to get the summary field value in crystal report.

ITS URGENT
Title: Repeat labels for rows   
Name: keith
Date: 2009-06-25 12:38:33 PM
Comment:
For Crystal 11...In your example above, is there any way to make Alejandra Camino to print on every row in that section?
Title: Dynamic Connection on CrossTab   
Name: Saga
Date: 2009-05-06 2:59:21 AM
Comment:
this article is good but can anyone help me on

How to Get Data from SQL and display to Crosstab through source code without the use of WIZARD (The Cross-Tab Expert).

I had done this connection using Unbound Text Fields.
is there a version on Crosstab? please Help.
Title: Label for summarized field   
Name: Mano
Date: 2009-02-13 3:39:28 PM
Comment:
Hi,

I have more than 3 summarized field and would like to label them each.Can anyone let me know how we do this.

Thanks
Mano
Title: to caluculate weighted avg   
Name: Anitha
Date: 2009-02-04 4:01:47 AM
Comment:
Hi,
I have one summary field to caluculate Weighted Avg.I have caluculated by using the inbuilt function Weighted Avg.But i have one query here ,if the denominator is '0' that time it is showing as blank.
but i need there also 0.
how can i solve this.
please help me on this
Title: Sticky Thickets   
Name: Kevin Perez
Date: 2009-01-15 3:07:38 PM
Comment:
I'm trying to create a crosstab that always has the same set of columns.

I don't have any record for one of the columns, but want that column to show in the crosstab, regardless.

Any ideas on how to achieve this?
Title: crosstab report   
Name: HasnolHisham
Date: 2008-12-11 3:13:49 AM
Comment:
Dear Anitha,

There are 2 option to set your cross tab display (whether horizontal or vertical):

1)Righ click the Cross Tab table and find Pivot Cross Tab
2) Right click Cross Tab table>Summarized Field Label>Summarize Horizontally or Summarize Vertically.
Title: Here is a tough one   
Name: HasnolHisham
Date: 2008-12-11 3:08:40 AM
Comment:
Dear Sanjay,

Basically there is an option for the @personnelcost to be displayed as average. Go to Summarized Fields (Cross Tab Expert) and change summary (by default=sum) to average.

I dont think you can devide the personnelcost by the total orders which is another field.It can be done if you're using Group Expert but it's not work in Cross Tab. It's impossible unless the formula have been defined in formula field.
Title: Here is a tough one   
Name: Sanjay
Date: 2008-11-26 12:20:34 PM
Comment:
I am using a cross tab in Crystal 11 and already have a field insterted that I am taking the average of (in this case Personnel cost). I would like to take that average returned and devide it by the total orders which is another field in the cross tab but i can't seem to get the average of the average / cost. Any thoughts?
Title: Outstanding   
Name: Rajesh
Date: 2008-11-04 8:57:25 AM
Comment:
This article is very good and it helps me lot. I am new to Ccrystal report. I need some info @ tabular report.
Can i link column to subreport like link report.
Title: Very Informative   
Name: Sangamithra
Date: 2008-11-03 12:33:38 PM
Comment:
Hi,I am new to cross tab reports and this article was really helpful and easy to follow.
Title: crosstab report   
Name: anitha
Date: 2008-10-30 7:26:23 AM
Comment:
how to change the horizontal to vertical and vertical to horizontal directions in crystal reports
Title: Thanks for the information..Additional Question   
Name: John Heck
Date: 2008-10-23 10:00:57 PM
Comment:
I really enjoyed your article and was wondering if you could tell me what I am trying to do is possible and if so how. I am trying to create a cross tab report that uses Sales Reps, Company, and Employee down the left side and Sales Reps across the top with the intersection being a commission rate. What I am trying to do is show all the sales reps that share a commission with other sales reps for each employee for each company.

Regards,
John Heck
Title: Mr   
Name: Muhammad Yaseen Khan
Date: 2008-08-24 4:06:44 PM
Comment:
Sir i am very happy to see your tutorial. beleive me its very nice . i just started crystal reporting but after your tutorial i am feeling that i can easily make some other Cross tab reports because you explain very nicely. i am thankfull to you. and hope you will continue share your expirence in the form of such tutorial.
Thank you

With regards

Yaseen Khan
Title: Excellent Article for Beginners   
Name: Md. Mushtaque
Date: 2008-08-17 2:01:19 AM
Comment:
Sir,
I am glad to see such article on Cross-Tab Report. It is really excellent for Beginners. If you could suggest something on Text data to be displayed and calculated as number and again displayed as text in a particular format.
Such as if there is a field of Hours having data as 2:30,2:45,2:15. How to display these data and show the total hours as 7:30 where as I got the value as 7.50 Hours but as per the hours format it is not exactly correct and acceptable.
If you could suggest something on it, it will be really appreciated and this could solve lots of problems in Management of Time and Money.

Thanks again for your Article. Please put some articles on advanced and lively features.

Product Spotlight
Product Spotlight 



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


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-04-16 9:33:26 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search