Conditional expression calculation not working

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

Development Tools

Reporting Services Development

Conditional expression calculation not working

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-28 : 11:32:56

I believe this was asked once, but didn't get a definite answer. Anyone know why when I use the following conditional expression, it just seems to ignore it?

=IIF(Fields!HRS_BID.Value = "0", "NONE", (SUM( Fields!HRS_USED.Value)/SUM( Fields!HRS_BID.Value)) * 100)

For example, I say if the Hours Bid is Zero, then don't do the calculation or else do it. Now the thing just went right ahead and did it anyways.

I tried doing a calculation field for it, and it seems to work, but I can't use a sum (the calculation i have above).

Any ideas on how to go about this?

jhermiz

3564 Posts

Posted - 2005-04-28 : 11:36:14

=IIF(Condition, True, False)

=IIF(YourField=0, "None", SUM(YourField)/SUM(OtherField) * 100)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-04-28 : 11:36:44

Ahh forgot to tell ya, as you have stated the sum dont work, so in place of that put your calculated fields.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-28 : 12:22:17

Jon,

It seems that RS just ignores my condition and went right ahead and does the calculation anyways even though the condition is true. I remember Tara or someone in this forum mention something about it, but I can't seem to recall.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-28 : 13:17:31

Chris,

I was able to get this to work. I just created a dummy report whose data set is:

SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2

So 0,1,2 are the 3 rows that I am working with. Then in the report, I've got two fields, one that just displays the ID column (which is what the field is named in this dummy report), then the second one displays this:

=IIF(Fields!ID.Value = 0, -1, SUM(Fields!ID.Value))

That works fine for me. In preview tab, I get:
0, -1
1, 3
2, 3

Which is what I'd expect.

I'm wondering if you are having problems with the data types as you're mixing integers with strings. The false part of your expression evaluates to an integer, but you are using a string for the true part as well as the condition.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-28 : 17:30:25

FOr some odd reason it works for other field, but for this one particular field it keeps sayig "Error attempted to divide by zero"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-28 : 17:35:16

Yes I recall something like this where it evaluates the false part even though it doesn't need to since the condition is true. I'd suggest doing this in embedded code in the report or if you can get it to work in a calculated field, do it there.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources