Derived Field error

This message has to do with database grouping and summary functions. Please post both derived fields.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

This is the how I arrive at my vacation balance (this derived field works):
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_CARRYOVER",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_ADJUST_YTD",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_EARNED_YTD",'0'))-
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_TAKEN_YTD",'0'))-
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_TAKEN_UNPROC",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_ADJUST_UNPROC",'0')))

This is the derived field where I am trying to take the above derived field and times it by the employee's hourly wage (where the error occurs):
(TO_CHAR((SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_CARRYOVER",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_ADJUST_YTD",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_EARNED_YTD",'0'))-
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_TAKEN_YTD",'0'))-
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_TAKEN_UNPROC",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_ADJUST_UNPROC",'0')))*
("J"."HOURLY_RT"))

Thanks!!!

We are running version 4.2.2,Oracle, and ADP.

I was able produce the same error using a similiar set of derived fields:
testsum (adds earning codes)
SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'51',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'55',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'56',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'S',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))

answer1 (testsum * hrlyrate)

TO_CHAR(( SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'51',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'55',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'56',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'S',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0')))* "REPORTS"."V_EMPLOYEE"."RATE1AMT")

I don't believe the TO_CHAR is necessary but it didn't make any difference.

When you test answer1 in the derived field box I get the error. Make sure that the rate field is highlighted in database grouping and the box is checked.

I found that eventhough RS complains, click 'ok' on the error and the derived field. The report should execute correctly, if the rest of the code is correct.

Hope this is of some help.





David Black, CSQA
Sr. Systems Analyst
Teepak, LLC