Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reportsmith Version 4.2 derived field 1

Status
Not open for further replies.

wmannod

MIS
Jun 23, 2005
15

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.
 
The syntax is database brand specific. I will show Oracle examples because most people use that brand database.

Please see:
Syntax One:
SEE: /* <== 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:


Specializing in ReportSmith Training and Consulting
 
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
 
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)

Specializing in ReportSmith Training and Consulting
 
With the code above I get the error

Not enough operands

Thanks

 
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)

Specializing in ReportSmith Training and Consulting
 
Hi I still get this same error.

Not enough operands

Thanks
 
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)

Specializing in ReportSmith Training and Consulting
 
Hello.........Yes that part is working. It bring in the temp rate of 25 I formated it to 25.00.
 
OK now go with this:

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






Specializing in ReportSmith Training and Consulting
 
I get a temp rate of 25.00 where there is a temp rate and 1.00 where this is not a temp rate.
 
OK now go with

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

Specializing in ReportSmith Training and Consulting
 
This is giving me the temp rate where this is one, and the hourly rate where there is no temp rate. Working great.
 
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)

Specializing in ReportSmith Training and Consulting
 

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 )
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top