Wrox Programmer Forums
|
BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7
This is the forum to discuss the Wrox book Professional SQL ServerReporting Services by Paul Turley, Todd Bryant, James Counihan, George McKee, Dave DuVarney; ISBN: 9780764568787
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 24th, 2005, 08:35 PM
Authorized User
 
Join Date: Aug 2004
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to barmanvarn
Default iff statement not working

I have the following statement:

=iif( Fields!Ticket_Budget.Value = 0,0, Fields!Ticket_Sales.Value/Fields!Ticket_Budget.Value)

For some reason, it keeps trying to divide by zero when the budget fields IS equal to zero.

Any suggestions?
 
Old October 3rd, 2005, 12:12 PM
Authorized User
 
Join Date: Dec 2004
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bsullins
Default

This is one of the major PITA's in Reporting Services...

Reporting Services evaluates every part of the if statement so you have to do a nested if that evaluates to 1 if the value of ur field is 0.

Like so...

=iif(Fields!Ticket_Budget.Value=0,0,iif(Fields!Tic ket_Budget.Value=0,1,Fields!Ticket_Sales.Value/Fields!Ticket_Budget.Value))

Try that...

--
Ben Sullins
Sql Developer - Our Vacation Store
 
Old January 23rd, 2009, 05:58 AM
Registered User
 
Join Date: Jan 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Smile hi


example1:
=Switch( Fields!QtyTobeFilled.Value<= Fields!QuantityOnHand.Value,"Green",
Fields!QtyTobeFilled.Value>Fields!QuantityOnHand.V alue,"Blue")

example2:
iif(Fields!QtyTobeFilled.Value<= Fields!QuantityOnHand.Value,"Green",iif(Fields!Qty TobeFilled.Value>Fields!QuantityOnHand.Value,"Red" ))
 
Old January 23rd, 2009, 04:25 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Quote:
Originally Posted by bsullins View Post
Reporting Services evaluates every part of the if statement so you have to do a nested if that evaluates to 1 if the value of ur field is 0.

=iif(Fields!Ticket_Budget.Value=0,0,iif(Fields!Tic ket_Budget.Value=0,1,Fields!Ticket_Sales.Value/Fields!Ticket_Budget.Value))
Ummm...how does that fix the problem??? You *STILL* end up doing a divide by zero if *ALL* parts of *ALL* IIF's are evaluated.

I think you just made a typo. I think you meant to write:
Code:
=iif(Fields!Ticket_Budget.Value=0, 0,
     Fields!Ticket_Sales.Value / iif(Fields!Ticket_Budget.Value=0,1,Fields!Ticket_Budget.Value)
)
That way, if the value *IS* zero, you change it to 1 so no "divisiion by zero". But then the prior IIF ends up ignoring the division quotient, anyway, so the divide by 1 has no impact.

You *COULD* also do
Code:
= ( Fields!Ticket_Sales.Value / iif(Fields!Ticket_Budget.Value=0,1E100,Fields!Ticket_Budget.Value) )
So that the quotient becomes a really tiny number and, when displayed, will show as zero. Don't do this if you will use the result in any further calculation, but it should work for display-only purposes.





Similar Threads
Thread Thread Starter Forum Replies Last Post
IFF statement in SQL scott.allison@slp-eng.com SQL Server 2000 1 January 15th, 2007 01:13 PM
Would this be a IFF then Statement Corey Access 7 November 2nd, 2006 03:18 PM
Simple but not working IF THEN statement nlicata Classic ASP Databases 6 June 6th, 2003 10:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.