Crystal Reports 11 - Cross-Tab

Hi,

I want to do some calculations on a cross-tab report.

Here is my example:
This is the output of my SQL
Categor 	Total_sales		Region
--------        ------------ 		-------
            

Prod1		1000			Chicago	
Prod1		6000			Boston
Prod1		8000			DC
Prod2		3000			Chicago	
Prod2		4000			Boston
Prod2		5000			DC
Prod3		2000			Chicago	
Prod3		6000			Boston
Prod3		9000			DC

-------------------------------------------------------

My Cross-tab should look like



Categor 	Chicago		Boston		DC	Total
--------        ------------    -------		--	-----
            

Prod1		1000		6000		8000	15000	
Prod2		3000		4000		5000	12000
Calc1		3		0.67		0.625
Prod3		2000		6000		9000	17000	

I dont know how to do the Calc1 part in the cross-tab, calc1 is row2/row1. and I have various other calculations, but I just want to know how to implement this first in the cross-tab.

Thoughts?

Thanks!

Open in new window

D-pkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EtdashouCommented:
Did you try to use a sub-report to do your calculations and then pass it to your main report with shared variables?

What I would do is to put your first Calc1 in a formula in a sub-report like this :
// put that formula in you detail section of your sub-report
shared numbervar array Calc1;
redim preserve Calc1[3];  // since there are 3 cities
select {City}
case  "Chicago" : Calc1[1] := Calc1[1] + 1
case "Boston" : Calc1[2] := Calc1[2] + 1
case "DC" : Calc1[3] := Calc1[3] + 1

and in your main report you insert in a formaula (and then add to formula to your crosstab):

shared numbervar array Calc1;

select {City}
case  "Chicago" : Calc1[1]
case "Boston" : Calc1[2]
case "DC" : Calc1[3]
mlmccCommented:
as I recall cross tabs are built before shared variables are evaluated in the final pass through the report so using shared variables won't help

Are you using CR2008?
If so then there are limited calculations you can do in a cross tab.

The question says CR XI so I assume that is what you are using.  In that case you will have to do a manual cross tab
http://www.kenhamady.com/form12.shtml

mlmcc
oliffCommented:
It may be possible to do your calculations as SQL commands, which will be calculated before the cross tab is built.

These may then be usable in the cross tab.

Just a stab in the dark, I do not have much experience with them, I normally build the table myself manually, with some formatting :) (aka crosstabs are poop!)
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

D-pkAuthor Commented:
Thanks everyone.....
oliff: I dont want to do that in the SQL.

Can somebody help on the syntax on how to insert that Calc1 record in the cross-tab, with some explanation, I am very new to this? and also some good resources for understanding the formulas with examples.

Thanks!
mlmccCommented:
What version of Crystal?

Onlt CR2008 allows a calculated field in the cross tab.

mlmcc
D-pkAuthor Commented:
I am using crystal reports 11....
I would like a step by step  explanation on the link you posted earlier with an example.

Thanks
mlmccCommented:
What do you need after row 3?

DO you need row3 / row2 ?

mlmcc
mlmccCommented:
Or is it row2/row3?

mlmcc
D-pkAuthor Commented:
yes... row2 / row1 (Prod 2 / Prod 1)

Thanks!
mlmccCommented:
Do you need that after each row or only for row 1 and 2?

mlmcc
D-pkAuthor Commented:
Just for row 1 and row 2..

(However, the example data above was just a part of my problem, I have various other calculations. My actual output has 6 new rows to be created like this based on various calculation.
For example, average_col is prod 3 / 12 and Calc3 is Prod 6 / Prod 9 and so on.. I first want to know how to start with these kind of problems and try to build on the other calculations based on the solution you post)

Thanks
EtdashouCommented:
Hi D-pk

Since you have a lot of different calculations, maybe the best way around is to create your own cross-tab ( manually put the lines between the cells), especially if you want to format each field with different formats (lets say : green if > , yellow if =, red if <). The bad thing about this is that you have to create a formula for every cell. A good way to solve this problem is to create a sub-report that display only 1 line, and then put this sub-report in a grouping section of your main report so the sub-report will repeat each time the group change ( change of city for this example ).

My first solution works as well (see above), since I used it in Crystal XI and 2008.
Did you play with sub-reports in the past?
D-pkAuthor Commented:
Thanks Etdashou!

Could you walk me thru one example step by step?

Thanks!
EtdashouCommented:
Ok D-pk, I hope you have some time hehe.
I know that my method is not the best, there may be a lot of better way to write formulas or to declare variables, but it works. If experts can comment my formulas, please do so.
First of all, I've created an excel sheet with exactly the data you gave.
Here's what I dit.
1. Create a report with the excel sheet as the database.
2a. Then create a sub-report in the report header ( Insert, then subreport).
2b . You have 2 option, choose the "Create a subreport with the report wizard",  enter a name for your sub-report then click on the button Report Wizard.
2c. Choose your Database then click "finish"
3 Right click on your sub-report, and click on the "border" table. remove all borders.
4 Open your sub-report, and suppress all your sections.
5. Create a Formula with this inside : (change the variables name as you want)

shared numbervar array Prod1;
shared numbervar array Prod2;
local numbervar ArraySize;

ArraySize := distinctcount({Feuil1_.Region});
redim preserve Prod1[ArraySize];
redim preserve Prod2[ArraySize];

select {Feuil1_.Region}
case "Chicago" :
if {Feuil1_.Category} = "Prod1" then Prod1[1] := {Feuil1_.Total Sales}
else if {Feuil1_.Category} = "Prod2" then Prod2[1] := {Feuil1_.Total Sales}
case "Boston" :
if {Feuil1_.Category} = "Prod1" then Prod1[2] := {Feuil1_.Total Sales}
else if {Feuil1_.Category} = "Prod2" then Prod2[2] := {Feuil1_.Total Sales}
case "DC" :
if {Feuil1_.Category} = "Prod1" then Prod1[3] := {Feuil1_.Total Sales}
else if {Feuil1_.Category} = "Prod2" then Prod2[3] := {Feuil1_.Total Sales}

6. Insert the formula in your Detail section, then close your sub-report
7. You should already have formulas for your Prod1, Prod2 and Prod3 in your main report. But if it's not the case, you should create them to put them in your cross tab, like this :

if {Feuil1_.Category} = "Prod1"
then {Feuil1_.Total Sales}

8. You have to create another formula for your Calc1 :

shared numbervar array Prod1;
shared numbervar array Prod2;

select {Feuil1_.Region}
case "Chicago" : Prod2[1] / Prod1[1]
case "Boston" : Prod2[2] / Prod1[2]
case "DC" : Prod2[3] / Prod1[3]

9. Insert another "report header" section, and insert a crosstab in it.
10a. In the cross-tab expert window. Add the field region as Columns
10b, in the summarized fields, insert the sum of Prod1, Prod2 and Prod3
10c, in the summarized fields, insert Calc1, but change the Summary for Max instead of Sum
10d. In the customize style tab, uncheck the "row total on left", and check the "show labels" box in the summarized fields section.

Once this is done, run your report and you should have your cross-tab.
If you have any question, please ask :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
D-pkAuthor Commented:
Thanks a lot for ur steps Etdashou!
Before I proceed I have a few questions.

1. where does my SQL go (it has begin date and end date parameters), in the Main report or sub-report?
From ur example, it looks like we are using the database fields in both main report and sub-report.
should I include the same SQL in both the reports with same parameters
2. what is "Feuil1_" means? for ex: do u mean by {command.region}
3. do I have to do any linking with sub report?

Again I really appreciate ur time..
Thanks@

EtdashouCommented:
Hi D-Pk,

1. Since I've created an excel sheet to "recreate" your situation, my database is an excel one. If your database is a SQL command, then yes you should put it in both sub-report and main report
2. I think you got this one hehe
3. No linking since I don't have parameters, but if you have parameters in your main report you should link them to the sub-report. (to do that, create the same parameter in your sub-report, then go in your main report, right click on your subreport and "change subreport links").
D-pkAuthor Commented:
Thanks Etdashou...
This brings an another question.
By Having my SQL in both main report and sub report, executes my SQL twice?
If thats the case, is there a way around to just execute it only once?

Thanks#
EtdashouCommented:
Well, it really depends on your report. but usually in my reports I do all my calculations in my subreports in the report header (because it's one of the best way to add numbers from different unlinkable databases), and then I only display the results in my main report. But in your case, since you want a crosstab, you have to use your SQL in both.

A way around would be to create a simple database with only regions for your main report (like an excel sheet), but it's only if you think that 2 SQL querries would be too long to run. I don't recommand this. And I must say that this add a lot to the what I've told you before, since you have to create a formula with shared variables for each "Prod" as well.

Why don't you want to execute your SQL twice?
D-pkAuthor Commented:
Ok.. I dont want my SQL to be executed twice, because performance issues...
How do I call a formula in an another formula..
For ex: if I want to make some calculations based on Calc1,
Calc1 * Prod3 and with that result calculate an another one and so on...
Thanks@
EtdashouCommented:
Ok, before going further, you should tell us how many calculations you have to do. If the answer is 5, we can still use my method, but if the answer is 200, we will have to change our strategy.
I can also give you a workaround for you SQL running twice, but first I would have to know if you will schedule that report or you will just run it manually?
D-pkAuthor Commented:
Here is my scenario:
Ok.. My output would look something like this:

Categor   Total_sales    Region
-------- ------------     -------
            

Prod1    1000              Chicago  
Prod1    6000              Boston
Prod1    8000              DC

Prod2a    0            Chicago  
Prod2a    0             Boston
Prod2a    0             DC

Prod2b    0             Chicago  
Prod2b    0             Boston
Prod2b    0             DC

Prod2c    0             Chicago  
Prod2c    0               Boston
Prod2c    0               DC

Prod3a    2000             Chicago  
Prod3a    6000             Boston
Prod3a    9000             DC

Prod3b    0             Chicago  
Prod3b    0             Boston
Prod3b    0             DC

Prod4    0             Chicago  
Prod4    0             Boston
Prod4    0             DC

Prod5    0             Chicago  
Prod5    0             Boston
Prod5    0             DC

Prod6    7000             Chicago  
Prod6    5000             Boston
Prod6    8000             DC

Prod7    0             Chicago  
Prod7    0             Boston
Prod7    0             DC

Prod8    0             Chicago  
Prod8    0             Boston
Prod8    0             DC

Prod9    3000             Chicago  
Prod9    4000             Boston
Prod9    9000             DC

Prod10    0             Chicago  
Prod10    0             Boston
Prod10    0             DC

-------------------------------------------------------

My Cross-tab should look something like this:


Categor 	Chicago		Boston		DC	Total
--------  -----------    -------	--	-----
            

Prod1		    1000		6000		8000	15000	
Prod2a		  8   		4   		12   	-
Prod2b		  2   		2   		4   	-
Prod2c		  2   		1	    	1.5 	-
Prod3a		  2000		6000		9000	17000	
Prod3b		  0.166		0.0833	0.093	0.34305	
Prod4		    0.6664	0.166		0.279	 -	
Prod5		    666.4		996		  2232	3894.4	
Prod6		    1000		6000		8000	15000	
Prod7		    1.5 		6.02		3.58	11.1	
Prod8		    666.4		996 		2232	3894.4	
Prod9		    1000		6000		8000	15000	
Prod10	    1.5 		6.02		3.58	11.1	

-------------------------------------------------

The calculation I have done is accurate for my sample data.

The "-" indicates I dont want the total there.

The following are the Calculations I made for each category:

Prod2a: These values has to be hard-coded.
Prod2b: These values has to be hard-coded.
Prod2c: (Prod2a/Prod2b) / 2

Prod3b: (Prod3a/Prod1) / 12
Prod4:(Prod2c * Prod3b) * 2
Prod5: Prod4 * Prod1

Prod7: Prod6 / Prod5
Prod8: This one has a small logic

if Prod4's value is greater than 1 then return value as 1 
else return the value as in Prod4

and then whatever the result from the conditional if statement multiply it with Prod1

Prod10: Prod9/Prod8

Scheduling: yes its a semi-annual report.

I hope I didnt confuse you :)
This is almost my scenario.. as far as the regions are concerned I have 7 cities for simplicity sake I have given 3, hope thats not gonna affect the strategy.

Thanks a lot!

Open in new window

D-pkAuthor Commented:
My Cross tab again.. Its messed up in my prev post...
Categor 	Chicago		Boston		DC	Total
--------  -----------    -------	--	-----
            

Prod1	    1000		6000		8000	15000	
Prod2a	     8   		4   		12   	-
Prod2b	     2   		2   		4   	-
Prod2c	     2   		1	    	1.5 	-
Prod3a	  2000		       6000		9000	17000	
Prod3b    0.166	              0.0833	        0.093	0.34305	
Prod4    0.6664              0.166		0.279	 -	
Prod5    666.4		     996		2232	3894.4	
Prod6     1000		    6000		8000	15000	
Prod7     1.5 		   6.02		        3.58	11.1	
Prod8    666.4		    996 		2232	3894.4	
Prod9     1000		    6000		8000	15000	
Prod10	  1.5 		    6.02		3.58	11.1	

-------------------------------------------------

Open in new window

EtdashouCommented:
Ok it's not too complicated. But there's a problem with my method, your Total column won't work since the Calc1 is not a sum but a max, so it gives the maximum data from that line.
In addition, your second crosstab is not really working as well (6000+2000=9000?)
I think the solution of a manual crosstab is what you need. To create it, you just build arrays like I've showed you directly in your main report (no need for a subreport in this case), and create a different formula for each cell of your crosstab and insert the lines manually.
It seems a long road, but if you choose well what calculations you do at the start of your report, and if you don't want to have too much formulas in your report (need to scroll), create a subreport for your calculations, and a subreport for your crosstab.
I think you have everything to build your crosstab, I've added my report and excel sheet as well.
(you will see that i've added a couple of things to the forumulas as well)
TEST-EE.rpt
TEST-EE.xls
D-pkAuthor Commented:
Thanks!
Ok I have a few questions:

I slightly changed your solution,  I created a formula as below (Lets call it "Main_Calculation")
For now I ve just taken till Prod 5, because if I get it till Prod 5, I can play around with rest of them.
This one works well till Prod 2c, when it comes to Prod 4 it just gives me zeros (I guess thats because my output of my SQL has 0s)
What I am trying to do is, get the value returned in the Prod 2c 's if statement and use it in the Prod 4's if statement.
And Prod 5 is also the same, getting the values from Prod 4 ''s if statement. so on so forth..
Can I create a seperate formula for Prod 2c (that is just copying the if statement and paste it in a new  formula - Prod 2c)?
If this is possible, how to get those values record by record, For ex: something like {@Prod 2c [1], @Prod 2c [2],@Prod 2c [3]} and
then use that in the Main_Calculation formula.
I want to do this way because, I can just use this formula in my summarized field and get it line by line. Your formula gives
all the calculations for each of the Products.

--------------------

The above thing worked (Partially )only if I use the sub-report and had the other formula in the sub-report. I want to avoid the (if possible)
sub-report(Mainly because of the double execution of SQL). If I am just creating the  formula( in Main_report) which is in sub-report and delete the sub-report all together and Place the formula in
Detail section of the main report and suppress it, will it work?

I know I am asking too many questions, but I wanna learn as well... Thanks again for your answers.

shared numbervar array Prod1;
shared numbervar array Prod2a;
shared numbervar array Prod2b;
shared numbervar array Prod2c;
shared numbervar array Prod3a;
shared numbervar array Prod3b;
shared numbervar array Prod4;
shared numbervar array Prod5;

if {Command.category} = "Prod 3b" then
select {Command.region}
case "Chicago" : (Prod3a[1] / Prod1[1]) / 12
case "Boston" : (Prod3a[2] / Prod1[2]) / 12
case "DC" : (Prod3a[3] / Prod1[3]) / 12

else if {Command.category} = "Prod 2a" then
select {Command.region}
case "Chicago" : 6
case "Boston" : 4
case "DC" : 3

else if {Command.category} = "Prod 2b" then
select {Command.region}
case "Chicago" : 1
case "Boston" : 2
case "DC" : 3


else if {Command.category} = "Prod 2c" then
select {Command.region}
case "Chicago" : (6 / 1) / 2
case "Boston" : (4 / 2) / 2
case "DC" : (3 / 3) / 2

else if {Command.category} = "Prod 4" then
select {Command.region}
case "Chicago" : (Prod2c[1] * Prod3b[1]) * 2
case "Boston" : (Prod2c[2] * Prod3b[2]) * 2
case "DC" : (Prod2c[3] * Prod3b[3]) * 2


else if {Command.category} = "Prod 5" then
select {Command.region}
case "Chicago" : Prod4[1] * Prod1[1]
case "Boston" : Prod4[2] * Prod1[2]
case "DC" : Prod4[3] * Prod1[3]


else {Command.Tot_sales}

--------------------
EtdashouCommented:
Ok, you have a few questions hehe
First of all, where do you insert your numbers in each of your arrays? where is the formula that fill the "Prod2c" array?
Second, yes you could create another formula to calculate Prod2c, but the place where you put it is very important in this case.
To solve the problem of use the SQL querry twice, you can do everthing in your subreport and your main report could only be connected to an excel sheet where there is only the names of the region on each line with a title. only that. then you will be able to connect to that excel sheet and use this field to build your crosstab, (one formula for each line, where you shared the arrays from the subreport).
Don't forget that crystal is calculating your formulas one after the other, so place in your subreport the first to declare your arrays, then another to calculate, etc.
*If you suppress a formula, it won't be calculated, you really need the subreport in your case.
D-pkAuthor Commented:
yeah few questions :)
Prod 2c formula would be just this:

if {Command.category} = "Prod 2c" then
select {Command.region}
case "Chicago" : (6 / 1) / 2
case "Boston" : (4 / 2) / 2
case "DC" : (3 / 3) / 2

-------------------------------------
and replace this part from the Main_Calculation formula with:
.....
else if {Command.category} = "Prod 2c" then
select {Command.region}
case "Chicago" : {@Prod2c[1]}
case "Boston" : {@Prod2c[2]}
case "DC" : {@Prod2c[3]}

and for the Prod 4 part in Main_Calculation formula would be

else if {Command.category} = "Prod 4" then
select {Command.region}
case "Chicago" : ({@Prod2c[1]} * Prod3b[1]) * 2
case "Boston" : ({@Prod2c[2]}] * Prod3b[2]) * 2
case "DC" : ({@Prod2c[2]} * Prod3b[3]) * 2

But, this makes sense, if we can make Prod 2c formula execute first before Main_Calaculation formula.
And Prod 5,6,7 .. I need to do similar things to make sure those formulas execute first...

And on the excel data, do u mean something like this:

Category       Region            
--------  -------
           

Prod1          Chicago
Prod1          Boston
Prod1          DC
Prod2a        Chicago
Prod2a        Boston
Prod2a        DC
...............

Thoughts??

Thanks
EtdashouCommented:
For the excel sheet, i mean :

Region
Chicago
Boston
DC

that's it. no prod , nothing else. so in your crosstab you will be able to do a "on change of" region, and the Select/Case in the formulas will work.

For the "if we can make Prod 2c formula execute first before Main_Calaculation formula", you can use the "EvaluateAfter(x)" function. This force your formula to be evaluated after the formula "x".
D-pkAuthor Commented:
Sorry for the delayed response... Thanks a lot for your help...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.