?
Solved

Help with iif statement in a report

Posted on 2005-04-28
5
Medium Priority
?
261 Views
Last Modified: 2008-03-04
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
0
Comment
Question by:mdiglio
  • 3
5 Comments
 
LVL 16

Author Comment

by:mdiglio
ID: 13892124
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
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13892369
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
 
LVL 16

Author Comment

by:mdiglio
ID: 13894473
Hi,
Thanks for the response.
That doesn't seem to work as an expression
0
 
LVL 11

Accepted Solution

by:
andrewbleakley earned 2000 total points
ID: 13897769
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
 
LVL 16

Author Comment

by:mdiglio
ID: 13897862
Hey...I like it!
Thanks for the help

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question