Report Design: Crosstab report

Print Page | Close Window

Crosstab report

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3464
Printed Date: 03 May 2024 at 8:36am

Topic: Crosstab report

Posted By: fusion
Subject: Crosstab report
Date Posted: 17 Jun 2008 at 1:45pm

Is there any way i can add a row in a crosstab right below the total for the columns. That would be the formula field.

I would use the colum total value in the formula.

Dept X Y z Tot

acc 1 2 0 3

mkt 2 2 3 7

HR 0 1 1 2

sales 1 2 4 7

4 7 8 19

FTE 4/19*.5 7/19*.5 8/19*.5 This is the row that I need

to add below the total of columns.

If this is possible, Can anyone explain How?

Thank you.

Replies:

Posted By: BrianBischof
Date Posted: 17 Jun 2008 at 3:00pm

Ugh. Not that I know of.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: fusion
Date Posted: 17 Jun 2008 at 3:09pm

Thanks Bryan.

So how can I get what I want?

Is there a way using SQL to do this? My values for the columns such as X, Y, Z changes. User does not want to go and change the SQL everytime a values has been added.

Thanks again.

Posted By: BrianBischof
Date Posted: 17 Jun 2008 at 3:50pm

There is a way to use SQL to have it create the cross-tab data for you and then you just report from that data. I've never done it before, but I've seen it in Google. Also, I know that MS Access has a way of creating cross-tab data using SQL. If you have Access there should be a way to get it to create the data and then look at its SQL code and create your own from there.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: fusion
Date Posted: 18 Jun 2008 at 11:39am

Thanks Bryan.
I do know how to create a crosstab using SQL but the problem with that is the column names should be hard coded in SQL. Everytime a value changes, the code has to be changed.
http://www.orafaq.com/wiki/SQL_FAQ

SELECT job,
           sum(decode(deptno,10,sal)) DEPT10,
           sum(decode(deptno,20,sal)) DEPT20,
           sum(decode(deptno,30,sal)) DEPT30,
           sum(decode(deptno,40,sal)) DEPT40,
           sum(sal)                   TOTAL
FROM emp           
GROUP BY job

In this scenario, when the dept gets added such as 
dept 50, dept 60, dept 70, dept 80 etc. each of them 
has to be added to the code manually. 
The change is not automatic

Thanks.

Posted By: BrianBischof
Date Posted: 18 Jun 2008 at 6:25pm

I did some research and it appears that it can't be done in SQL (strange that I thought this was possible). The other option, which is quite complex but you are probably up for it, is to use the code in my book to copy the totals from cross-tab into an array. Then create a formula for the max number of columns you could have (as long as it doesn't expand onto a second page) and put them into a new section and line each one up below the cross-tab where a column would be. If the column doesn't have a value (the array element is zero), then return an empty string. If the array has a value, perform the formula on it and convert it to string and print it on the report.

Does this make any sense? If not I can try to explain it better. It's definitely a 'unique' solution, but I think it will work as long as the cross-tab stays on one page.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: fusion
Date Posted: 19 Jun 2008 at 8:17am

Bryan,

I am using only crystal reports. How is it possible to copy the totals of the cross tab in an array.

The other thing is the Crosstab expands to next page even though I set the page as Legal.

Let me know if this can be done.

Thank you.

Posted By: BrianBischof
Date Posted: 19 Jun 2008 at 11:46am

If it expands to the next page then you can't do this. Sorry.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: fusion
Date Posted: 19 Jun 2008 at 3:35pm

Bryan,
How can I capture the column totals in a variable?

After I click on the column total field, right click and go to the format editor,
select the common tab and select the Display String formula field,
I was able to capture one value by using the following code.

Shared stringvar t := ToText(currentfieldvalue);

I then created a formula. I declared the variable in that formula.
shared stringvar t;

I inserted this value on the section below

I could only view one value.

Any ideas on how all the values can be displayed?

Thanks

Posted By: BrianBischof
Date Posted: 19 Jun 2008 at 4:43pm

Did you ever get a copy of my book (I forget...)? I have a whole section dedicated to this topic with sample code. Anyway, you create an array and you put each value into the next element of the array. Every time the formula is called, increment the index counter by one and use that to insert the value into the array. When the cross-tab is finished, the array will have every value you need and then you can use them in other locations on your report. Very cool!

You can find out more about my books at http://www.amazon.com/exec/obidos/ASIN/0974953601/bischofsystem-20 - Amazon.com or reading the http://members.crystalreportsbook.com - Crystal Reports eBooks online.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: fusion
Date Posted: 19 Jun 2008 at 11:13pm

Bryan,
I have not bought the book.
I got the idea.

Thanks for all your help.

Posted By: BrianBischof
Date Posted: 20 Jun 2008 at 2:29am

cool.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: fusion
Date Posted: 24 Jun 2008 at 8:17am

Thanks Bryan.

Print Page | Close Window