Writing If-Then using derived fields 1

[chabotnick]

I have a drived field which calculated a dollar amount. I would like to create a field that looks at that field and if the amount is over a set amount (say $800) it returns $800, but if it is less it returns the calculated amount. I've tried using SQL and I'm not really familer with using Report Basic Macros, so any help someone could provide would be appriciated.

Thanks.

 

[CharlesCook]

Try this in a derived field.

case
when your current code here >= 800 then 800
else your current code here
end

this is oracle 9 syntax if it doesnot work let me know because there is another way for oracle 8...

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

 

[chabotnick]

That, but that didn’t quite work. When I enter it as an SQL derived field, I get the error “Missing Right Parentheses.” If I enter it as a ReportBasic Macro I get numerous errors.

 

[CharlesCook]

This would be an SQL Derived Field. Please post what you are putting into the SQL derived field.

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

 

[dlblack]

With our version of Oracle and ReportSmith, I am able to use the "Least function" ie Least(value1,value2), returns the least of the two values.

David Black, CQA
Teepak, LLC

 

[CharlesCook]

Hi dlblack,

That was going to be plan b, but the more I think about it in this case that is better.

Least(your current code here,800)

Thanks...
Sometimes the box is large, but still a box.

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

 

[chabotnick]

That still isn't working for me. In the SQL derived field I put the following.

least (((REPORTS.V_EXPANSION_FLDS.FIELD22/52)*.667),800)

and I receive the message "Missing Right Parentheses"

 

[CharlesCook]

Make sure there is no space after the word least and before the First (

try this

least(100,800)

if ok then

least((100/52),800)

if ok then

least(((100/52)*.667),800)

if ok then

least(((REPORTS.V_EXPANSION_FLDS.FIELD22/52)*.667),800)

Let me know how it goes

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

 

[chabotnick]

That didn't work either. I used cut and paste to copy your coding exactly and I continue to get the "Missing Right Parentheses" error when I test the derived field.

Is there any other information that I could provide that would be helpful?

 

[CharlesCook]

Delete the Derived Field and hit Done NOT TEST. Test is not 100% reliable.

Does the report run ok?

If yes then try least(100,800)

and build more and more.

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

 

[chabotnick]

The report runs fine without the deleted derived field. I created a new field and put in only "least(100,800)" I then attempted to run the reports and receive an SQL Execution Error of "Missing FROM clause"

 

[CharlesCook]

Great that tells us something. What database are you using? Oracle? with ODBC?

Tell me more about your enviroment.

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

 

[chabotnick]

I am working from ADP PCPW 4.5 (SQL), using ReportSmith 4.2.

 

[CharlesCook]

Go to file then connections. Click on the connection name and do a screen shot. Can you email it to me at Charles@CharlesCook.com

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

 

[chabotnick]

Will do.