Reportsmith Version 4.2 derived field 1

[wmannod]

REPORTS.V_EMPLOYEE.RATE1AMT*REPORTS.V_PAYDATA_ALL.REGHOURS

I have a derived field as above that works fine. I would like to add more to this so it will only run if

REPORTS.V_PAYDATA_ALL.TEMPRATE = null or ifnot > 0

I can't get the syntax correct. Can anyone help me.

 

[CharlesCook]

The syntax is database brand specific. I will show Oracle examples because most people use that brand database.

Please see:

http://charlescook.com/PPS/ADP/ReportSmith/PCPW/SQL-Derived-Fields.pps

Syntax One:
SEE:

http://www.techonthenet.com/oracle/functions/decode.php

/* <== Comments start like this and end like this ==> */
/* NVL(parm1,parm2) convert parm1 to parm2 value if null */
/* so now I don't need to check for NULL */
/* DECODE is an If parm1 = parm2 then parm3 else parm4 */
/* just count the parms */
DECODE(NVL(REPORTS.V_PAYDATA_ALL.TEMPRATE,0),
0,(REPORTS.V_EMPLOYEE.RATE1AMT * REPORTS.V_PAYDATA_ALL.REGHOURS),
REPORTS.V_PAYDATA_ALL.TEMPRATE)

Syntax Two:
CASE NVL(REPORTS.V_PAYDATA_ALL.TEMPRATE,0)
WHEN 0 THEN (REPORTS.V_EMPLOYEE.RATE1AMT * REPORTS.V_PAYDATA_ALL.REGHOURS)
ELSE REPORTS.V_PAYDATA_ALL.TEMPRATE
END
SEE:

http://www.techonthenet.com/oracle/functions/case.php

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[wmannod]

Hi Thanks the response. I am very new to this and I am using ADP for windows. I have come up with the code below but I don't have the parenthesis correct.

@DECODE IF (REPORTS.V_PAYDATA_ALL.TEMPRATE, 0 )
Then (REPORTS.V_PAYDATA_ALL.REGHOURS * REPORTS.V_PAYDATA_ALL.TEMPRATE)
if [REPORTS.V_PAYDATA_ALL.TEMPRATE, > 0 ]
Then (REPORTS.V_EMPLOYEE.RATE1AMT * REPORTS.V_PAYDATA_ALL.REGHOURS)

Thanks so much

 

[CharlesCook]

This should work...

@DECODE(NVL(REPORTS.V_PAYDATA_ALL.TEMPRATE,0),
0,(REPORTS.V_EMPLOYEE.RATE1AMT * REPORTS.V_PAYDATA_ALL.REGHOURS),
REPORTS.V_PAYDATA_ALL.TEMPRATE)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[wmannod]

With the code above I get the error

Not enough operands

Thanks

 

[CharlesCook]

try this:

@DECODE(@NVL(REPORTS.V_PAYDATA_ALL.TEMPRATE,0),0,(REPORTS.V_EMPLOYEE.RATE1AMT * REPORTS.V_PAYDATA_ALL.REGHOURS),REPORTS.V_PAYDATA_ALL.TEMPRATE)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[wmannod]

Hi I still get this same error.

Not enough operands

Thanks

 

[CharlesCook]

A good example of data brand syntax...

@DECODE(@NULLVALUE(REPORTS.V_PAYDATA_ALL.TEMPRATE,0),0,(REPORTS.V_EMPLOYEE.RATE1AMT * REPORTS.V_PAYDATA_ALL.REGHOURS),REPORTS.V_PAYDATA_ALL.TEMPRATE)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[wmannod]

Sorry I'm still getting the same error.

 

[CharlesCook]

so we will need to debug this. Start with this ONLY:

@NULLVALUE(REPORTS.V_PAYDATA_ALL.TEMPRATE,0)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[wmannod]

Hello.........Yes that part is working. It bring in the temp rate of 25 I formated it to 25.00.

 

[CharlesCook]

OK now try this:

@DECODE(@NULLVALUE(REPORTS.V_PAYDATA_ALL.TEMPRATE,0),
0,(1 * 1),
0)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[wmannod]

Hi, This give me a value of 1.00

 

[CharlesCook]

OK now go with this:

@DECODE(@NULLVALUE(REPORTS.V_PAYDATA_ALL.TEMPRATE,0),
0,(1 * 1),
REPORTS.V_PAYDATA_ALL.TEMPRATE)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[wmannod]

I get a temp rate of 25.00 where there is a temp rate and 1.00 where this is not a temp rate.

 

[CharlesCook]

OK now go with

@DECODE(@NULLVALUE(REPORTS.V_PAYDATA_ALL.TEMPRATE,0),
0,(REPORTS.V_EMPLOYEE.RATE1AMT * 1),
REPORTS.V_PAYDATA_ALL.TEMPRATE)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[wmannod]

This is giving me the temp rate where this is one, and the hourly rate where there is no temp rate. Working great.

 

[CharlesCook]

Then finally

@DECODE(@NULLVALUE(REPORTS.V_PAYDATA_ALL.TEMPRATE,0),
0,(REPORTS.V_EMPLOYEE.RATE1AMT * REPORTS.V_PAYDATA_ALL.REGHOURS),
REPORTS.V_PAYDATA_ALL.TEMPRATE)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[wmannod]

You Rock. I added the last little bit of code and I now have the results I was looking for. I never could have done this without your help.

@DECODE(@NULLVALUE(REPORTS.V_PAYDATA_ALL.TEMPRATE,0),
0,(REPORTS.V_EMPLOYEE.RATE1AMT * REPORTS.V_PAYDATA_ALL.REGHOURS),
REPORTS.V_PAYDATA_ALL.TEMPRATE *REPORTS.V_PAYDATA_ALL.REGHOURS )

 

[CharlesCook]

Your welcome.....

Feel free to give me a star....

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting