Calculate Values Based Upon Cells in Different Cross-Tabs

[PremHealth1]

Hi,

I'm using CR 2016 Support Pack 8 Patch 3.

I have two cross-tabs being output. Call them CTA and CTB.
Both have Departments listed down the left and Sitter Types listed across the top.
Calculation cells in CTA are "bad sitter minutes".
Calculation cells in CTB are "good sitter minutes".
Total rows on the bottom and the right of both cross-tabs.

I need to be able to access the values in the intersection cells/possible the total cells of CTA and CTB and use them in calculation formulas outside of the cross-tabs to calculate percentage values.

Here is some sample CTA and CTB data and the new percentage calculation values I want using data from CTA and CTB...

CTA SitType1 SitType2 SitType3 Total
Dept1 100 200 300 600
Dept2 400 500 600 1500
Dept3 700 800 900 2400
Total 1200 1500 1800

CTB SitType1 SitType2 SitType3 Total
Dept1 900 800 700 2400
Dept2 600 500 400 1500
Dept3 300 200 100 600
Total 1800 1500 1200

New calculated perentage data I want using far right total values from CTA and CTB.
Pct Bad Min Dept1: 20% Pct Good min Dept1: 80%
Pct Bad Min Dept1: 50% Pct Good min Dept1: 50%
Pct Bad Min Dept1: 80% Pct Good min Dept1: 20%

NOTE: The CTA and CTB are output next to each other (CTA CTB) on the report so that department rows are lined up. I want my percentage calcs for each row to line up appropriately so you have (CTA, CTB, % Calcs).

Question 1:
How do I get the values from each cross-tab to use in the percentage formulas? I'm currently investigating doing something like this (don't even know if I can get it to work for the values in the total columns). But if it works it will necessitate the creation of a lot of formulas but I'm OK with that.
NumberVar n;
If GridRowColumnValue( "Dept" ) = "Dept1" and GridRowColumnValue( "SitType" ) = "SitType1"
then n:= currentfieldvalue;
false

Question 2: If the above works it concerns me as I'm dealing with cross-tab data. If the cross-tabs grow and pick up one more department and I don't know about it I...
1) Won't have formulas for it.
2) The alignment of all my calculated percentage values will be off. In the above example if Dept0 was added all the department rows in CTA and CTB get moved down one row. Percentage calcs to the right which are formulas will be misaligned.
Is there a better way?

Don't overestimate my Crystal experience - lots of googling...Thanks

Update1...
1)
Failed to mention that both cross-tabs are in report footer.
2)
Above it states: Calculation cells in CTA are "bad sitter minutes". And Calculation cells in CTB are "good sitter minutes". In the CTA cross-tab-expert wizard the "Summarized Fields" entry is a summary of a variable that I created that determines whether "minutes" from a database are bad or not. In the CTB cross-tab-expert wizard the "Summarized Fields" entry is a summary of a variable that I created that determines whether "minutes" from a database are good or not.
3)
As stated above I want the output to look like: CTA then CTB then % Calcs. Just realized though that I can add multiple values in the "Summarized Fields" of each cross-tab. So in CTA calculation cells I can have the bad minutes and good minutes. In CTB I can output the bad minutes and good minutes. I also get both values as totals in the total column.
In the bad minute cross-tab CTA I can suppress the good minute values in the intersections and in the total column.
In the good minute cross-tab CTB I can suppress the bad minute values s in the intersections and in the total column.

I believe I just need...
A variable to sum bad and good minutes on each row in each cross-tab (NewRowSum)
A formula in a new column to the right of the existing total column that allows me in CTA to divide bad minutes by the NewRowSum value * 100 to get a percentage. Then do the same in CTB.

This would limit the number of formulas and remove any concern about the cross-tabs growing.

My questions now are...
Question 3: How do I get the NewRoSum value?
Question 4: Where do I create the formula for the new column?

Thanks

 

[lbass]

You left out the most critical information--the content of your formulas for the good and bad minutes. Assuming these are conditional formulas, the total is available to you in each crosstab by adding a formula without the condition, and you can then calculate within a crosstab. You could easily also just use one instead of three crosstabs, with three summaries. Or you could do a third crosstab that includes the needed values as summaries and then suppress them and resize the fields to minimize them. The way you do the calculation in the crosstab depends upon your ease with working with the newest crosstab functionality. I don't have the most recent version of CR, so would use conditional formatting fields for each summary to set up variables to do the calculations.

-LB