0

Instead of showing $0 I'm showing "-", which is string value. But then I need to calculate "Overall % of WP", which is 'WrittenPremium'/"Total Written Premium" and if there is no $ amount it will take string "-", and because of that I got an error. How can i fix it? How can I say in my expression something like, if you see "-", then dont divide, just 0.

enter image description here

I am trying something like that:

=IIF(ReportItems!TotalWrittenPremium.Value<>0, ReportItems!Written_Premium.Value/(ReportItems!TotalWrittenPremium.Value ),0)

But still giving me an error.

Serdia
  • 4,242
  • 22
  • 86
  • 159

2 Answers2

1

First of all, it's generally not a good practice to refer to report items like ReportItems!Written_Premium.Value. Here's what I would suggest:

  1. Add a calculated field to your dataset that removes the dashes. Let's call it "Calculated_Premium" for this example. The expression would be:

    =CDec(IIf(Fields!Written_Premium.Value = '-', 0, Fields!Written_Premium.Value))

  2. Refer to this new column in your table like this:

    Written Premium

    =Fields!Calculated_Premium.Value

    Total Written Premium

    =Sum(Fields!Calculated_Premium.Value)

    Percent of Total

    =Switch(Sum(Fields!Calculated_Premium.Value) = 0, 0 , True, Fields!Calculated_Premium.Value / Sum(Fields!Calculated_Premium.Value))

StevenWhite
  • 5,907
  • 3
  • 21
  • 46
0

This might be the issue with IIF trying to resolve both parts of the equation leading to a DIV0 error.

=IIF(ReportItems!TotalWrittenPremium.Value <> 0, 
        ReportItems!Written_Premium.Value /
        IIF(ReportItems!TotalWrittenPremium.Value = 0, 1, ReportItems!TotalWrittenPremium.Value)
    , 0)

If you run it in Visual Studio, you might get a better error message in the Error List.

Does the iif function compute both paths in SSRS or is it short-circuited?

divide by zero/null workaround in SSRS 2008 report

Community
  • 1
  • 1
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39