0

I am having problems trying to do the following. I am trying to incorporate an iif statement inside a caption. This is the current line that is evaluated on the label:

=round(@ Data.rp_TATStatAtBenchmark~ / @ Data.rp_TATStatCount~ * 100) + "%"

and I have changed it to the following to handel the error when @ Data.rp_TATStatCount = 0 and @ Data.rp_TATStatAtBenchmark = 0 as well (for some reason the report shows ??? when the second value is 0 instead of showing just "0%". Anyhow, this is what I'm trying to write:

=IIf((@ Data.rp_TATStatAtBenchmark~ = "0" OR @ Data.rp_TATStatCount~ = "0"),"0%", round(@ Data.rp_TATStatAtBenchmark~ / @ Data.rp_TATStatCount~ * 100) + "%")

but it is not working. Any help would be appreciated. Thank you!

Jose H.
  • 5
  • 2

1 Answers1

0

If the @Data.rp_TATStatCount~ value is 0, the reason you're seeing a result of "???" is because divide by zero is a mathematical expression which has no meaning ('undefined'). This would always return as undefined '???' instead of a number -- so the result would never be 0. Since token variables are simply replaced, the expression is still be checked for validity, even if you're using an iif() statement.

If you want to create an exception for divide by zero, then you will also need to provide a calculation to check for when rp_TATStatCount = 0, and either replace it with a non-zero integer, or use a different column name. The best way might be to create a second Calculated Column that checks for zero (your iif() statement) and then use this newly created column to perform your round() function.

here's a quick example inside a Static datalayer using your existing column names:

<DataLayer Type="Static" ID="dlStatic">
 <StaticDataRow rp_TATStatAtBenchmark="0" rp_TATStatCount="0" />
   <CalculatedColumn Formula="iif(@Data.rp_TATStatCount~=0, 1, @Data.rpTATStatCount~)" ID="StatCount2" />
   <CalculatedColumn Formula="iif(@Data.rp_TATStatAtBenchmark~ = &quot;0&quot; OR @Data.rp_TATStatCount~ = &quot;0&quot;, &quot;0%&quot;, round(@Data.rp_TATStatAtBenchmark~ / @Data.StatCount2~ * 100) + &quot;%&quot;)" ID="pctCol" />
</DataLayer>
david
  • 236
  • 2
  • 3
  • Thank you for your response. I tried to add the calculated column formulas to my datalayer and reference it later where I want to display them, but the result is that no values are shown this time instead of "???". Is it absolutely necessary to have the static data row? And if so, how can I incorporate that to my existing datalayer? BTW, thanks for helping me out with this and my previous problem, I appreciate it :) – Jose H. Jun 21 '12 at 16:28
  • Also, on the 1st Calculated column, shouldn't the third entry be: @Data.rp_TATStatCount~ ? (Even when changing this, no change, the entries remain blank) – Jose H. Jun 21 '12 at 17:10
  • I used the static data row simply to create a sample demonstration, so you won't need to keep that in your actual content. Also, yes, there is a mistake , the third entry in the Statcount2 calc column should match your column name: @Data.rp_TATStatCount~. Making that change, in my example should allow it to work properly- however you can likely just copy the 2 calculated columns into your Definition to get it to work. – david Jun 21 '12 at 17:42
  • I'm sorry, I believe I was making a mistake. Thanks so much for your help again :) – Jose H. Jun 21 '12 at 19:29