?
Solved

SQL REPORT EXPRESSION

Posted on 2009-04-09
10
Medium Priority
?
208 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 2000 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

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

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. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

801 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