Grouping Formula Effects Totals in ALL Crosstabs

[rose4567]

Report in crystal 10 using 2 cross-tabs. To display data the way client has requested, I created a formula to group the data. It works great, except that if I use it in one of my cross-tabs it effects the totals in the other cross-tab (even though the formula is not used in the other cross-tab).

When I use the formula in the Row selection in Cross-tab #1, it skews my running grand totals in Cross-tab #2 which I DO NOT want that selection criteria applied to and have NOT used that formula in any row selection.

Why does a formula used in one cross-tab effect my totals in all cross-tabs? The formula is not performing any calculations. It it merely record selection criteria. For reference, below is the formula I am using to group. Thank you!!

RECORD SELECTION CRITERIA TO APPLY TO ALL FORMULAS AND CALCULATIONS:
____________________________________________
{po_account_segment.HEADER_ITEM_IND} = "I" and
{PO_HEADER.CURRENT_HDR_STATUS} in ["3PCO", "3PCR", "3PPR", "3PS"] and
{po_header.PO_DATE} in {?Pm-@startpodate} to {?Pm-@endpodate}

GROUPING FORUMLA USED IN ONE CROSS-TAB ROW SELECTION:
_________________________________________________
If {PO_HEADER.PO_TYPE_CODE} IN ["CB", "CP", "EO", "GB", "GP", "PB", "PP", "SB", "SP"] AND
THEN
"EXEMPTIONS"

Else
IF {PO_HEADER.PO_TYPE_CODE} <> ["CB", "CP", "EO", "GB", "GP", "PB", "PP", "SB", "SP"] and
{VENDOR_CERTIFICATION.CATEGORY_ID} = "20 JSEB" and
{po_header.po_date} in {vendor_certification.cert_date} to {vendor_certification.expire_date}
THEN
"EQUAL BUSINESS OPPORTUNITIES"

 

[lbass]

Please explain how each crosstab is set up (row, column, summary) and where each is located (what report section).

-LB

 

[rose4567]

I will try my best to explain this as clearly as possible, but if anything is unclear, please let me know and I'll try to clarify better.

The report summarizes spending grouped by purchasing categories. Sprending is cross-tabed by accounting codes and spending categories.

The report is broken into two sections. The first set of two cross-tabs groups by department. The second set of cross-tabs generates city-wide totals and is located in the report footer.

Cross-tab 1: Group Header (groups by dept)
__________________
It looks like this:
supply cap improv pro svc total
Exemptions
sole po 1.00 2.00 1.00 4.00
contract po 2.00 1.00 1.00 4.00
Exemption Totals: 3.00 3.00 2.00 8.00
EBO
asian 3.00 0 0 3.00
hispanic 0 2.00 1.00 3.00
EBO Totals: 3.00 2.00 1.00 6.00
__________________

ROW 1: Accounting Codes. The first row is simply intended as a grouping mechanism to so I can break the accounting codes down further in the next section. It groups using different field criteria into two groups - EXEMPTIONS or EBO using formula:
If {PO_HEADER.PO_TYPE_CODE} IN ["CB", "CP", "EO", "GB", "GP", "PB", "PP", "SB", "SP"]
THEN
"EXEMPTIONS"

Else
IF {PO_HEADER.PO_TYPE_CODE} <> ["CB", "CP", "EO", "GB", "GP", "PB", "PP", "SB", "SP"] and
{VENDOR_CERTIFICATION.CATEGORY_ID} = "20 JSEB" and
{po_header.po_date} in {vendor_certification.cert_date} to {vendor_certification.expire_date}
THEN
"EQUAL BUSINESS OPPORTUNITIES"

ROW 2 is set to group under Exemptions and EBO using simple alias formula:
IF {@exempt_non-exempt} = "EXEMPTIONS" THEN
(If {PO_HEADER.PO_TYPE_CODE} = "CB" THEN
"State Contract Blanket Order"
ELSE
If {PO_HEADER.PO_TYPE_CODE} = "CP" THEN
"State Contract Purchase Order")


ELSE
If {@exempt_non-exempt} = "EQUAL BUSINESS OPPORTUNITIES" THEN
(If {vendor_certification.category_type} = "1.JSEB" THEN
" JSEB"
ELSE
If {vendor_certification.category_type} = "2.JSEB/AA" THEN
"African American")

COLUMNS: Purchasing Categories - supply, Improvements, Professional services, Contract services, or Uncategorized.


SUMMARIZED FIELD: {po_account_segment.DOLLAR_AMT}
Grand Total Columns are suppressed in this crosstab because totaling criteria follows formula selections and it will not compute. But Exemptions and EBO total here.


Cross tab 2: Group Footer (groups by dept)
Here I am simply calculating total purchase orders, cross-tabed by Purchasing Category.
_____________________
It looks like this:
supply cap improv pro svc total
Total POs 30.00 45.00 10.00 85.00
Exemptions 3.00 3.00 2.00 8.00
POs Less Exemp 27.00 42.00 8.00 77.00
______________________
No Rows are selected because I'm performing a different set of calculations outside the grouping criteria set in Exempt-Non-Exempt formula.

Rows are broken into Purchasing Categories - supply, Improvements, Professional services, Contract services, or Uncategorized.

Summarized Fields are based on Forumla fields:
(1) Total POs: {po_account_segment.DOLLAR_AMT}
(2) Exemp (Formula):
if ({PO_HEADER.PO_TYPE_CODE} IN ["CB", "CP", "EO", "GB", "GP", "PB", "PP", "SB", "SP"] and
{po_account_segment.HEADER_ITEM_IND} = "I" and
{PO_HEADER.CURRENT_HDR_STATUS} in ["3PCO", "3PCR", "3PPR", "3PS"]) then
{po_account_segment.dollar_amt}
(3) POs Less Exemptions (Formula):
{po_account_segment.DOLLAR_AMT} - {@exemp}

Then I have two other cross-tabs.
Cross tab 3 is in Report Footer A and is exactly the same as Cross-tab 1. The only difference is that this cross-tab is not in a group so it is summarizing Exemptions and EBO totals for the whole city.
Cross-tab 4 is in Report Footer B and is exactly the same as Cross-tab 2. The only difference is that this cross-tab is not in a group and is summarizing Grand Total amounts for the whole city.

Wish I could post a screen shot of the report to make it easier to explain and see. But basically, it appears that when I add the grouping forumla EXEMPT-NON-EXEMPT to Cross-tabs 1 and 3, it effects totals in ALL of the Cross-tabs, skewing totals in cross-tabs 1,2,3 and 4. I thought since I broke my grand totals out into different cross-tabs I could use different criteria in two of the cross-tabs without the other cross-tabs being effected, but that does not appear to be the case.

Thank you so much for your help!!!!

 

[lbass]

In what way does it skew the totals? Inflate them? Or? I think this might have to do with how your tables are linked. If you are using an inner join on one of the tables in your row formula (grouping formula) and the table is ONLY referenced in that formula, then the link becomes activated only then, and could change results. Try researching this and see if changing the join for the vendor certification table to a left join eliminates the problem.

-LB

 

[rose4567]

I'm sorry I didn't add this to my other post. I was thinking about that on my way home, that I had forgotten to break out all the tables for you.

Yes, the formula inflates the totals.

I am using 4 tables. Here's how they are linked:

PO_Header to PO_Account
INNER JOIN on po number and release number

PO_Header to Vendor_Certificate
LEFT OUTER JOIN on vendor id so all PO data is captured and only vendor cert where needed. (note: i originally had this as an inner join and my totals were way low. so i changed to left outer join and now now my totals are correct, but when i add that EXEMPT_Non-EXEMPT formula which references vendor_cert table it inflates my totals.)

PO_Header to Department
INNER JOIN (for grouping so i can display department description.)

I will keep looking at the table joins. Thanks for the tip!

 

[lbass]

I don't think this is really a crosstab issue, but instead a linking one. I would try laying out the fields in the detail section of the report, and notice if you are getting repeated values due to the joins. You might have to use running totals instead of regular summary fields in the crosstab. This is possible in XI--I'm unsure about CR 10.

-LB

 

[rose4567]

Thanks LB for your help!!!