×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Reportsmith Version 4.2 derived field

Reportsmith Version 4.2 derived field

Reportsmith Version 4.2 derived field

(OP)

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.  

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

(OP)
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  

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

(OP)
With the code above I get the error

Not enough operands

Thanks

 

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

(OP)
Hi I still get this same error.

Not enough operands

Thanks

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

(OP)
Sorry I'm still getting the same error.  

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

(OP)
Hi, This give me a value of 1.00

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

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

RE: Reportsmith Version 4.2 derived field

(OP)

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 )  

RE: Reportsmith Version 4.2 derived field

Your welcome.....

Feel free to give me a star....

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close