Solved

# SQL REPORT EXPRESSION

Posted on 2009-04-09
201 Views
Having an issue making a simple calculation:
\$24 + \$24 * 7% = \$25.68

\$24 = Data2
7% = Data3

I am trying this ...
=IIF((Fields!Data1.Value > 0),((Fields!Data2.Value + Fields!Data2.Value) * Fields!Data3.Value),"N/A") doesn't work...

Any suggestions?

0
Question by:gabepcsolutions
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4

LVL 8

Expert Comment

ID: 24112110
You may need to change the datatypes to int or decimal example.
=IIF((CDec(Fields!Data1.Value > 0),((CDec(Fields!Data1.Value) + CDec(Fields!Data2.Value)) * CDec(Fields!Data3.Value)),"N/A")
If this is not working for you can you post what error type is giving you?
0

Author Comment

ID: 24112404
but wouldn't that through off the calculations...  it would take my 7% = Data3 and convert it to 7 = Data3 ??

\$24 + \$24 * 7 = \$336

\$24 + \$24 * 7% = \$25.68
0

LVL 26

Expert Comment

ID: 24112488
try this expression
=IIF((Fields!Data1.Value > 0),(Fields!Data2.Value + (Fields!Data2.Value * Fields!Data3.Value)),"N/A")
0

Author Comment

ID: 24112506
already did... nothing, I even tried this...

=IIF((Fields!Data1.Value > 0),(Fields!Data2.Value * Fields!Data3.Value)),"N/A")

0

LVL 26

Expert Comment

ID: 24112683
ah, I think your fields are strings and the % is part of the string.  I just tried this:
=IIF(CDec(Fields!Data1.Value > 0),(CDec(Fields!Data2.Value) + (CDec(Fields!Data2.Value) * CDec(Left(Fields!Data3.Value,Len(Fields!Data3.Value)-1))/100.0)),"N/A")
and got the following result.
SampleReportImage.png
0

LVL 26

Expert Comment

ID: 24112702
It also looks like it will work with out all the CDec wrappers, it was just not liking the trailing % on the 7.
0

Author Comment

ID: 24112713
woa... what is all that doing?
CDec(Left(Fields!Data3.Value,Len(Fields!Data3.Value)-1))/100.0))
0

LVL 26

Accepted Solution

Chris Luttrell earned 500 total points
ID: 24112727
As I said above, the CDec is not necessary.
It was not liking the % in the field so I had to strip it off the end with the Left function (I assume the % is last character) and have to give it the length of the string to keep with the Len function (subtracting the 1 character for the %):
Left(Fields!Data3.Value, Len(Fields!Data3.Value)-1)
then because this is a percent you want in your calculation I divide by 100 to turn 7 into .07 for the calculation.
0

Author Comment

ID: 24112739
cool... let me try it
0

Author Comment

ID: 24112775
wow you're very good... do you have an email address?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Powershell finalizing the end of an array. 4 32
sql select total by week ending 3 38
Not listening to where 1 39
Cannot get group page break to work in SSRS 2008R2. 14 32
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month9 days, 5 hours left to enroll