Syntax Error

Question

Syntax Error

2Tall

SSChampion

Points: 14644
More actions
June 16, 2010 at 3:01 pm

#236596

I am attempting to write a nested IIF. The second IIF generates a syntax error.
=IIF(SUM(Fields!DemandMinutes.Value + Fields!TimeBooked.Value) >= Fields!ActivityCentreCapacity.Value,0,IIF(SUM(Fields!DemandMinutes.Value - Fields!TimeBooked.Value) / (Fields!ActivityCentreCapacity.Value) *100)<0,0,SUM(Fields!DemandMinutes.Value - Fields!TimeBooked.Value) / (Fields!ActivityCentreCapacity.Value)*100) & "%"
Can someone point me in the right direction.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

2Tall (6/16/2010)
I am attempting to write a nested IIF. The second IIF generates a syntax error.
=IIF(SUM(Fields!DemandMinutes.Value + Fields!TimeBooked.Value) >= Fields!ActivityCentreCapacity.Value,0,IIF(SUM(Fields!DemandMinutes.Value - Fields!TimeBooked.Value) / (Fields!ActivityCentreCapacity.Value) *100)<0,0,SUM(Fields!DemandMinutes.Value - Fields!TimeBooked.Value) / (Fields!ActivityCentreCapacity.Value)*100) & "%"
Can someone point me in the right direction.
Thanks,
Phil.

I see three possibilities:

1. if Fields!ActivityCentreCapacity.Value = 0 then you'll get a divide by zero error.

2. You are concatenating numbers with the "%". Try removing the & "%" and see what happens. (not sure if you can just concatenate strings to numbers... it might work)

3. It looks like you're missing the final ")"... "*100)" should be "*100))".

Everything else looks okay to me.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

2Tall SSChampion Points: 14644 More actions · Answer 2

Hi Wayne. I have come up with:

=ROUND(IIF(SUM(Fields!DemandMinutes.Value) - SUM(Fields!TimeBooked.Value) >= Fields!ActivityCentreCapacity.Value,100,IIF((SUM(Fields!DemandMinutes.Value) - SUM(Fields!TimeBooked.Value) / (Fields!ActivityCentreCapacity.Value) *100) <0,0,SUM(Fields!DemandMinutes.Value - Fields!TimeBooked.Value) / (Fields!ActivityCentreCapacity.Value)*100)),2)

Logic:

The activity centre value is fixed for each activity centre (i.e. 495 minutes).

Demand in minutes can vary and can be any value, however the activity centre capacity = 100% when demand >= 495 minutes.

Time is booked off against each activity centre.

If the sum of all the demand in minutes less the sum of all the time booked is >= than the activity centre capacity (495) then capacity = 100%.

If the sum of all the demand in minutes less the sum of all the time booked / the activity centre capacity * 100 is less than 0, then capacity is zero

Else show me the current capacity as a % (If the sum of all the demand in minutes less the sum of all the time booked / the activity centre capacity * 100).

I hope that makes sense? I have attached a screen shot of the rendered report.

Is there a chance of a divide by zero error, or any obvious flaws in my logic?

Thanks,

Phil.

Update: Logic is incorrect as I am getting a negative value.

Example: Demand in Minutes = 416 minus Time Booked is 495 = -79

-79/ 480 *100 = -16.46

I want it to display 0.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 3

OK I have changed

IIF((SUM(Fields!DemandMinutes.Value) - SUM(Fields!TimeBooked.Value) / (Fields!ActivityCentreCapacity.Value) *100) <0

to

IIF((SUM(Fields!DemandMinutes.Value) - SUM(Fields!TimeBooked.Value)<0

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper