help with IIF SSRS 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

help with IIF SSRS 2008

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2012-07-07 : 16:20:37

I have a simple calculation in SSRS that calculates service level for tickets closed and everything works great. It uses an IIF statement at the beginning to make sure it does not divide by zero and this works. However, I need add a 2nd condition to this report so that I do not SUM any tickets in green and SUM the Total where the job description is like "MGR%" and job Description like "SUPV%"
Here is the name of the field that needs the condition:
Fields!POSITION_DESCR.Value. I need to sum(Fields!GREEN.Value) where Fields!POSITION_DESCR.Value not like "MGR%" or Fields!POSITION_DESCR.Value not like "SUPV%" The same is true for the denonmiator.

=IIF((Fields!GREEN.Value + Fields!YELLOW.Value + Fields!RED.Value) = 0, 0, sum(Fields!GREEN.Value) / sum((Fields!GREEN.Value + Fields!YELLOW.Value + Fields!RED.Value)))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-07 : 17:09:35

nest IIF for that


=IIF((SUM(IIF(Fields!POSITION_DESCR.Value not like "MGR%" Or Fields!POSITION_DESCR.Value not like "SUPV%", Fields!GREEN.Value,0) + Fields!YELLOW.Value + Fields!RED.Value)) = 0, 0, sum(Fields!GREEN.Value) / SUM(IIF(Fields!POSITION_DESCR.Value not like "MGR%" Or Fields!POSITION_DESCR.Value not like "SUPV%", Fields!GREEN.Value,0) + Fields!YELLOW.Value + Fields!RED.Value))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

joe8079
Posting Yak Master

127 Posts

Posted - 2012-07-07 : 19:30:46

thanks, I'll give that a shot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-07 : 20:33:02

welcome
let me know if you face any more issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources