August 23, 2013 at 1:06 pm
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
August 23, 2013 at 1:46 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply