Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
skrdlej
Starting Member
3 Posts |
 Posted - 2012-08-10 : 09:35:12
|
| Hi,I am trying to get a percentage of my Environmental Costs / (Revenue and Energy surcharges).The problem comes when the formula encounters a null or 0 and results in a #Error in the column. I have tried all kinds of ways using the IIF including the below. What am I doing wrong? I prefer not to use other functions/codes in the report SQL itself if possible because of the complexity already existing.There are textboxes in place of the names I use below. Just changed them to show the logic. Revenue, EnergyFee, EnviroFeeHow does one check for null in all areas and for 0 for the division part?=IIF(ReportItems!Revenue.Value = 0, 0, IIF(ReportItems!EnergyFee.Value = 0, 0, ReportItems!EnviroFee.Value/(ReportItems!Revenue.Value + ReportItems!EnergyFee.Value) ))Thank you all for any help you have! This report was due last week!Ms. Joell |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-10 : 10:59:10
|
| shouldnt condition be (ReportItems!Revenue.Value + ReportItems!EnergyFee.Value)=0?what if Revenue is not 0 but sum is 0 ie energyfee having same negative value?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
skrdlej
Starting Member
3 Posts |
Posted - 2012-08-10 : 12:18:54
|
| Thank you for responding!!!I did try that. Still getting the #Error. This particular record does not have an Enviro Fee or a Revenue Fee to capture. The value is null but if I show the records in SSRS as separate line items, they show up as 0's. here is what I tried based upon your input and it still failed:=IIF((ReportItems!Textbox76.Value + ReportItems!Textbox73.Value) = 0, 0, ReportItems!Textbox78.Value/(ReportItems!Textbox76.Value + ReportItems!Textbox73.Value) ) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-10 : 15:43:54
|
| try val(ReportItems!Textbox76.value)...are they all having numeric data?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
skrdlej
Starting Member
3 Posts |
Posted - 2012-08-10 : 16:35:01
|
| You are the hero of the day!!! That worked!Thank you so very much!Ms. Joell |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-10 : 16:59:15
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
#Error using Percentage formula; Null and 0s SSRS