Help with iif statement in a report

Hello,
If more info is needed just ask...I didn't want to clutter this up w/ too much detail on what the fields are and what I am trying to do.

I'm using Reporting Services. For the y axis on a chart report I have the following:

=round(Fields!CountOfLoginTime.Value/ Sum(Fields!no_of_weeks.Value, "average")/5,0)

This works and returns the expected result.
However for one instance ( a parameter )  I would like to divide by 6 instead of 5.
So I was thinkng I needed an iif statement

I have one parameter in the report... Parameters!location.Value

So how can i write the following iif statement ? or any other method that can be used

if Parameters!location.Value = 'ab123' then
round(Fields!CountOfLoginTime.Value/ Sum(Fields!no_of_weeks.Value, "average")/6,0)
else
round(Fields!CountOfLoginTime.Value/ Sum(Fields!no_of_weeks.Value, "average")/5,0)

Thank you for your time
LVL 16
mdiglioAsked:
Who is Participating?
 
andrewbleakleyCommented:
I would return the 5 or 6 as part of the underlying query either as a field from the table or as a case statement within the query

select *,
location_no_of_weeks =
CASE location
    WHEN 'location_a' THEN 5
    WHEN 'location_b' THEN 6
    ELSE 5
END
FROM my_table

this way you need not use an IIF statement which will require nesting if you bring another variation in.
your function will always be:
round(Fields!CountOfLoginTime.Value/ Sum(Fields!no_of_weeks.Value, "average")/Fields!location_no_of_weeks,0)
0
 
mdiglioAuthor Commented:
Oops...i'm sorry
Can there be an OR in there ?

if Parameters!location.Value = 'ab123'  or 'ab321' then
round(Fields!CountOfLoginTime.Value/ Sum(Fields!no_of_weeks.Value, "average")/6,0)
else
round(Fields!CountOfLoginTime.Value/ Sum(Fields!no_of_weeks.Value, "average")/5,0)
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi mdiglio!

You will simply do it like this :

if (Parameters!location.Value = 'ab123'  or Parameters!location.Value = 'ab321') then
  round(Fields!CountOfLoginTime.Value/ Sum(Fields!no_of_weeks.Value, "average")/6,0)
else
  round(Fields!CountOfLoginTime.Value/ Sum(Fields!no_of_weeks.Value, "average")/5,0)

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
mdiglioAuthor Commented:
Hi,
Thanks for the response.
That doesn't seem to work as an expression
0
 
mdiglioAuthor Commented:
Hey...I like it!
Thanks for the help

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.