slxdeveloper.com Community Forums

The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!

Reporting & Notification Forums - Reporting
Forum to discuss reports for SalesLogix including Crystal, SQL Reports, etc. View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to Reporting | New Thread

View: Search:

Author

Thread: re: formula in crystal reports

Chokri
Posts: 62

re: formula in crystal reports Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 05 Feb 07 4:59 AM

Hi I am fairly new user so please bare with me!!

I have the following formula field in my crystal report. The RETENTION_RESULTS.DISCOUNT field is a VARCHAR hence the requirement to change this to a number field first.

IF ToNumber(Trim(Left({RETENTION_RESULTS.DISCOUNT}, 2))) = 0 THEN
0
ELSE
{BIP_SUBSCRIPTIONS.SUBS_AMOUNT} % ToNumber(Trim(Left({RETENTION_RESULTS.DISCOUNT}, 2)))

It almost does everything I need it to do except it does not return the correct value. I am trying to 'subract' the discount from {BIP_SUBSCRIPTIONS.SUBS_AMOUNT} field, I have changed the formula around and included the substraction sign but have not had any luck so far.

[Reply][Quote]

Steve Harris
Posts: 16

Re: re: formula in crystal reports Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Feb 07 7:13 AM

Not sure what you are exactly doing but one thought is to do the math at the SQL level instead of in crystal.

[Reply][Quote]

Chokri
Posts: 62

Re: re: formula in crystal reports Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Feb 07 8:12 AM

Quote:

Originally posted by Steve Harris

Not sure what you are exactly doing but one thought is to do the math at the SQL level instead of in crystal.

Thanks - where would I embedd this at the SQL level?

[Reply][Quote]

Steve Harris
Posts: 16

Re: re: formula in crystal reports Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Feb 07 8:28 AM

It depends on where you are writing the SQL statement. If you put it in a SQL Server view you would need to use a CASE statement. However, in Oracle you would use DECODE and in Access you could use IIF.

[Reply][Quote]

Chokri
Posts: 62

Re: re: formula in crystal reports Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Feb 07 9:39 AM

I see I am using SQL and will use the CASE statement, thanks for your help.

[Reply][Quote]

Deb R
Posts: 14

Re: re: formula in crystal reports Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Feb 07 10:43 AM

Quote:

Originally posted by Jyoti Dorris

Hi I am fairly new user so please bare with me!!

I have the following formula field in my crystal report. The RETENTION_RESULTS.DISCOUNT field is a VARCHAR hence the requirement to change this to a number field first.

IF ToNumber(Trim(Left({RETENTION_RESULTS.DISCOUNT}, 2))) = 0 THEN
0
ELSE
{BIP_SUBSCRIPTIONS.SUBS_AMOUNT} % ToNumber(Trim(Left({RETENTION_RESULTS.DISCOUNT}, 2)))

It almost does everything I need it to do except it does not return the correct value. I am trying to 'subract' the discount from {BIP_SUBSCRIPTIONS.SUBS_AMOUNT} field, I have changed the formula around and included the substraction sign but have not had any luck so far.

Why are you only using the left two characters of the discount field. If your discount field contains a value of 7200 your formula only pulls out 72. If this is what you want OK. If it is not that could be why you are not receiving the correct value.

[Reply][Quote]

Sean Wichman
Posts: 1

Re: re: formula in crystal reports Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 07 Feb 07 12:27 AM

Hi,

I assume that {RETENTION_RESULTS.DISCOUNT} is the percentage discount. In that case this formula should work:

IF ToNumber(Trim(Left({RETENTION_RESULTS.DISCOUNT}, 2))) = 0 THEN
0
ELSE
{BIP_SUBSCRIPTIONS.SUBS_AMOUNT} * (1 -(ToNumber(Trim(Left({RETENTION_RESULTS.DISCOUNT}, 2))) /100)

Hope this helps

[Reply][Quote]

Chokri
Posts: 62

Re: re: formula in crystal reports Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 07 Feb 07 3:52 AM

Excellent it worked! thanks.

[Reply][Quote]

Page 1 of 1

You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 11/25/2024 7:32:42 AM