iif statement - 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

General SQL Server Forums

New to SQL Server Programming

iif statement

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-12-09 : 12:22:45

I am having issues with my one fomula that I have. Since this is happening int he future, I am adding an iif statement to it would just say N/A until there is datat in the tables. My other formulas worked but for some reason this one isn't working. By looking at this, can anyone help me? I am working with visual Studio.

The formula that I have is...
=IIF(Fields!AdjQrt2_2009.Value=0,"N/A",sum(Fields!AdjQrt2_2009.Value/Fields!TotalChrgsQrt2_2009.Value)- sum(Fields!AdjQrt1_2009.Value/Fields!TotalChrgsQrt1_2009.Value)/(iif(Fields!TotalChrgsQrt1_2009.Value=0,1, Fields!TotalChrgsQrt1_2009.Value)*100 & "%"))

My original formula is this.

=sum(Fields!AdjQrt2_2009.Value/Fields!TotalChrgsQrt2_2009.Value)- sum(Fields!AdjQrt1_2009.Value/Fields!DhsIncomeQtr1_2009.Value)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 12:29:35

[code]=IIF(Fields!AdjQrt2_2009.Value=0,"N/A",
(sum(Fields!AdjQrt2_2009.Value/Fields!TotalChrgsQrt2_2009.Value)- sum(Fields!AdjQrt1_2009.Value/Fields!TotalChrgsQrt1_2009.Value))/
iif(Fields!TotalChrgsQrt1_2009.Value=0,1, Fields!TotalChrgsQrt1_2009.Value))[/code]

no need of %. you can set it in format tab of cell properties.

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-12-09 : 13:06:46

For some reason it is still not working. The error message I am getting is #error, if that helps.

quote:

Originally posted by visakh16

=IIF(Fields!AdjQrt2_2009.Value=0,"N/A",
(sum(Fields!AdjQrt2_2009.Value/Fields!TotalChrgsQrt2_2009.Value)- sum(Fields!AdjQrt1_2009.Value/Fields!TotalChrgsQrt1_2009.Value))/
iif(Fields!TotalChrgsQrt1_2009.Value=0,1, Fields!TotalChrgsQrt1_2009.Value))

no need of %. you can set it in format tab of cell properties.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 13:13:26

are fields used inside having numeric data?

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-12-09 : 13:16:30

Yes, it is all numeric data.

Here is one that worked for me, but diffferent fields and formula.
=IIF(Fields!DhsIncomeQtr2_2009.Value=0,"N/A",sum(Fields!DhsIncomeQtr2_2009.Value - Fields!DhsIncomeQtr1_2009.Value)/iif(Fields!DhsIncomeQtr1_2009.Value=0,1, Fields!DhsIncomeQtr1_2009.Value)*100 & "%")

quote:
Originally posted by visakh16

are fields used inside having numeric data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 13:16:40

also try this

=IIF(Fields!AdjQrt2_2009.Value=0,"N/A",
(sum(Fields!AdjQrt2_2009.Value/iif(Fields!TotalChrgsQrt2_2009.Value=0,1,Fields!TotalChrgsQrt2_2009.Value))- sum(Fields!AdjQrt1_2009.Value/iif(Fields!TotalChrgsQrt1_2009.Value=0,1,Fields!TotalChrgsQrt1_2009.Value)))/
iif(Fields!TotalChrgsQrt1_2009.Value=0,1, Fields!TotalChrgsQrt1_2009.Value))

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-12-09 : 13:18:15

That one worked visakh16! Thank you so much!

quote:
Originally posted by visakh16

also try this

=IIF(Fields!AdjQrt2_2009.Value=0,"N/A",
(sum(Fields!AdjQrt2_2009.Value/iif(Fields!TotalChrgsQrt2_2009.Value=0,1,Fields!TotalChrgsQrt2_2009.Value))- sum(Fields!AdjQrt1_2009.Value/iif(Fields!TotalChrgsQrt1_2009.Value=0,1,Fields!TotalChrgsQrt1_2009.Value)))/
iif(Fields!TotalChrgsQrt1_2009.Value=0,1, Fields!TotalChrgsQrt1_2009.Value))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 13:21:09

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources