Creating Formulas on Cross Tab Data

[SundancerKid]

Hi everyone,

I am trying to use formulas on Cross Tab Data using SQL Payroll view. The View has Location Name, Last Name, First Name, Pay Code: ( HOURS DOUBLE OVER ADJHR SALARY ADJSAL ).

I Created a Cross Tab using Location, Last Name, First Name as my Row. Pay Code as my Column and Totals as my Value.

I Created 2 Groups Location and Pay Code.

I put the Cross Tab in the Pay Code Footer(GF2).

I want to create formulas off of the cross tab data and print all of my data in a summary fasion in the Location Footer (GF1).

I manualy created Totals so that I could control what was printed in (GF1).

I am using the whileprintingrecords function in the (GH1) I am initalizing my totals to 0. I am accumulating in (GF2) and I am printing in (GF1).

My problem in the accumlating formula I am using this formula:
(
if (({GlobalCT100.PAYROLCD} = "HOUR") and ({GlobalCT100.PAYROLCD} = "SALARY") and ({GlobalCT100.PAYROLCD} = "ADJHR") and ({GlobalCT100.PAYROLCD} = "ADJSAL"))then
//{@accum} to be placed in the Group #2 footer:
whileprintingrecords;
numbervar subtRegPay:= subtRegPay + {GlobalCT100.UPRTRXAM};
numbervar grtotRegPay:= grtotRegPay + {GlobalCT100.UPRTRXAM}
)

for @RegPay

AND

(
if (({GlobalCT100.PAYROLCD} = "OVER") or ({GlobalCT100.PAYROLCD} = "ADJOVER") or ({GlobalCT100.PAYROLCD} = "DOUBLE")) then
//{@accum} to be placed in the Group #2 footer:
whileprintingrecords;
numbervar OTReg:= OTReg + {GlobalCT100.UPRTRXAM};
numbervar GOTReg:= GOTReg + {GlobalCT100.UPRTRXAM};
)

for @OTPay

I seem to be getting the same data in both.

Reg Pay OT Pay
ADMIN Management $12,563.00 $12,563.00
ADMIN Services $8,905.50 $8,905.50
TOOL Room $3,625.98 $3,625.98

I have verified the Data, Reg Pay is right. OT Pay is wrong.

Please help.

Thanks

Rick

 

[lbass]

Is there a reason you are not adding another crosstab in the report footer with location as row, paycode as column, and total as value?

-LB

 

[SundancerKid]

The Cross Tab that I Created is Supressed. I only want to see the Formulas that I calculate from the Cross Tab information.

Is there a better way?

I have already tried creating a Cross Tab in SQL. I can see the data ok in my data files. But when I go to create a formula from the pivot data. I get no value. The information seems supressed.

Thanks,

Rick