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

Hi,

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

Here is my example:

This is the output of my SQL

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!
```

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

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

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!)

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!

I would like a step by step explanation on the link you posted earlier with an example.

Thanks

(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

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?

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_.Reg

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 trialBefore 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@

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").

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#

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?

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@

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?

```
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!
```

```
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
-------------------------------------------------
```

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

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}

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

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.

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

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".

Crystal Reports

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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]