Solved

SQL REPORT EXPRESSION

Posted on 2009-04-09
10
205 Views
Last Modified: 2012-05-06
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
Comment
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
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 8

Expert Comment

by:Hadush
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

by:gabepcsolutions
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

instead of this

$24 + $24 * 7% = $25.68
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24112488
try this expression
=IIF((Fields!Data1.Value > 0),(Fields!Data2.Value + (Fields!Data2.Value * Fields!Data3.Value)),"N/A")
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

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




0
 
LVL 27

Expert Comment

by:Chris Luttrell
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 27

Expert Comment

by:Chris Luttrell
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

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

Accepted Solution

by:
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

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

Author Comment

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
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. …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question