A number of currency amount is required here.

mcse2007
mcse2007 used Ask the Experts™
on
Hi,

I'm trying to calculate 10% of my site delivery charge which is tax component but CR is having difficulty via the formula producing the result.

Formula
if {Sheet1_.ZFR2 - Site Delivery} <=0 then "0"
else ({@sdelivery-unit-cost} *.10)

when I check the formula, it highlight this   (@sdelivery-unit-cost}  as part of the error in the formula

@sdelivery-unit-cost is....
if ({Sheet1_.ZFR2 - Site Delivery}) <=0 then ""
else {@sdelivery-sum}


@sdelivery-sum is....
CStr(sum ({Sheet1_.ZFR2 - Site Delivery}, {Sheet1_.Bill#Doc#}),2,'')

Any idea how to fix this?

appreciate your help.
error.bmp
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr. Business Intelligence Consultant/Architect
Commented:
You're getting an error because you're mixing string and number data types:

@sdelivery-sum is....
CStr(sum ({Sheet1_.ZFR2 - Site Delivery}, {Sheet1_.Bill#Doc#}),2,'')//You're explicitly converting a numeric value to a string

Open in new window


@sdelivery-unit-cost is....
if ({Sheet1_.ZFR2 - Site Delivery}) <=0 then ""//whatever you encapsulate in quotes is a literal string
else {@sdelivery-sum}//this formula is a string

Open in new window


if {Sheet1_.ZFR2 - Site Delivery} <=0 then "0"//whatever you encapsulate in quotes is a literal string
else ({@sdelivery-unit-cost} *.10)//you're attempting to perform math using a string field - Sdelivery-sum is a string, not a number - that's why you're getting an error

Open in new window


If you want to do math, you need to use all numbers or do the math with numbers first and convert the results to a string.

~Kurt

Author

Commented:
what would be the fix formula then?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
How about

if {Sheet1_.ZFR2 - Site Delivery} <=0 then
    "0"
else
     CStr(sum ({Sheet1_.ZFR2 - Site Delivery}, {Sheet1_.Bill#Doc#}) *.10,2,"")

mlmcc

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
There is no one fix formula.  You'd have to change all the dependencies.  You'd first change sdelivery-sum by not converting it to a string.

sum ({Sheet1_.ZFR2 - Site Delivery}, {Sheet1_.Bill#Doc#})

Open in new window


You'd then have to change sdelivery-unit-cost to be something like:

if ({Sheet1_.ZFR2 - Site Delivery}) <=0 then 0
else {@sdelivery-sum}//this formula is a number

Open in new window


You'd then change the final formula to:


if {Sheet1_.ZFR2 - Site Delivery} <=0 then 0
else ({@sdelivery-unit-cost} *.10)

Open in new window


Before you change anything, however, you need to ascertain why you're using strings in the first place.  Is there a specific business rule you're trying to meet? Is this change going to break something else in the report?

~Kurt

Author

Commented:
Beautiful. That worked.

Thanks mlmcc...when I'm visiting UK next time, i'll buy you a drink. :-)
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Since rhinok pointed out the error and then gave a solution to fix all the formulas he desires a majority of the credit for the solution

mlmcc

Author

Commented:
rhinok deserves some points here, that's for sure...sorry mate.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial