Solved

SQL REPORT EXPRESSION

Posted on 2009-04-09
10
199 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
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is this datetime? 1 25
SQL - Curser to do an insert based on a select 2 24
Duplicated data in GROUP_CONCAT 2 14
SSRS 2008 Jump To Report 4 19
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

680 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