SQL REPORT EXPRESSION

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?

gabepcsolutionsAsked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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
 
HadushCommented:
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
 
gabepcsolutionsAuthor Commented:
but wouldn't that through off the calculations...  it would take my 7% = Data3 and convert it to 7 = Data3 ??

$24 + $24 * 7 = $336

instead of this

$24 + $24 * 7% = $25.68
0
Cloud Class® Course: 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.

 
Chris LuttrellSenior Database ArchitectCommented:
try this expression
=IIF((Fields!Data1.Value > 0),(Fields!Data2.Value + (Fields!Data2.Value * Fields!Data3.Value)),"N/A")
0
 
gabepcsolutionsAuthor Commented:
already did... nothing, I even tried this...

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




0
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
It also looks like it will work with out all the CDec wrappers, it was just not liking the trailing % on the 7.
0
 
gabepcsolutionsAuthor Commented:
woa... what is all that doing?
CDec(Left(Fields!Data3.Value,Len(Fields!Data3.Value)-1))/100.0))
0
 
gabepcsolutionsAuthor Commented:
cool... let me try it
0
 
gabepcsolutionsAuthor Commented:
wow you're very good... do you have an email address?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.