Solved

SQL REPORT EXPRESSION

Posted on 2009-04-09
10
201 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 26

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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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 26

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 26

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 26

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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
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…

738 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