Writing Case Statements in Expressions in SSRS

• cljolly

  Ten Centuries

  Points: 1179

  More actions

  August 23, 2013 at 1:06 pm

  #395359

  Is it possible to do a case statement in an expression field in ssrs?

  Lets say I am tracking 6 kennels and some kennels have cats and some have dogs, and some have both. I want to track the average vet cost per cat or dog, but not both. If a kennel cats and dogs, I only want the answer to be dogs.

  Originally in the report query I had a case statement that said if kennel name contained dog then Y else N. So far so good.

  Then I created a field with the expression

  =iif(Fields!Kennelname.Value="Y",Fields!Vecost.Value/Fields!dogcount.Value,Fields!Vetcost.Value/Fields!catcount.Value)

  This is the result I am looking for

  Kennel Name Dog Count Cat Count Vet Cost Average per Animal

  White Dogs 5 4 100 20

  Black Cats 0 12 60 5

  Green Dogs 30 0 300 10

  What I go was this:

  Kennel Name Dog Count Cat Count Vet Cost Average per Animal

  White Dogs 5 4 100 20

  Black Cats 0 12 60 #error

  Green Dogs 30 0 300 #error

  It seemed like if there was both cats and dogs, it would work but would give me an error if there was only cats or dogs.

  Is it a timing issue in the case statement in the query?

  Thanks

• Daniel Bowlin

  SSC-Dedicated

  Points: 34566

  More actions

  August 23, 2013 at 1:46 pm

  #1644558

  cljolly (8/23/2013)

  ---

  Is it possible to do a case statement in an expression field in ssrs?

  Lets say I am tracking 6 kennels and some kennels have cats and some have dogs, and some have both. I want to track the average vet cost per cat or dog, but not both. If a kennel cats and dogs, I only want the answer to be dogs.

  Originally in the report query I had a case statement that said if kennel name contained dog then Y else N. So far so good.

  Then I created a field with the expression

  =iif(Fields!Kennelname.Value="Y",Fields!Vecost.Value/Fields!dogcount.Value,Fields!Vetcost.Value/Fields!catcount.Value)

  This is the result I am looking for

  Kennel Name Dog Count Cat Count Vet Cost Average per Animal

  White Dogs 5 4 100 20

  Black Cats 0 12 60 5

  Green Dogs 30 0 300 10

  What I go was this:

  Kennel Name Dog Count Cat Count Vet Cost Average per Animal

  White Dogs 5 4 100 20

  Black Cats 0 12 60 #error

  Green Dogs 30 0 300 #error

  It seemed like if there was both cats and dogs, it would work but would give me an error if there was only cats or dogs.

  Is it a timing issue in the case statement in the query?

  Thanks

  I am guessing that in both cases your problem is a result of trying to divide by zero. I believe all elements of the expression are evaluated for validity before one is actually executed and a value returned. So try managing the divide by zero in your formulae. I wrote a blog on handling divide by zero with a pretty simple embedded code that may be helpful. Note my comment in the comments at the bottom of the blog for where to put the code in a report.

  http://www.bidn.com/blogs/Daniel/ssas/1245/divide-by-zero-tweak

  The formula with the embedded code would be =iif(Fields!Kennelname.Value="Y",code.DivideBy(Fields!Vecost.Value,Fields!dogcount.Value),code.DivideBy(Fields!Vetcost.Value,Fields!catcount.Value))

  If this is more than you want you will have to use some additional logic in each element to evaluate a divide by zero.