trying to sum an iif statement in the footer

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

trying to sum an iif statement in the footer

Author

Topic

cardgunner

326 Posts

Posted - 2008-08-14 : 15:51:58

In the footer of a table I'm trying to sum the values of the table where the a value is like RN.

I have used every variattion of the following other then the one that is right and works.


=sum(iif(Fields!t_offc.Value like "RN*", Fields!t_amnt.Value, 0))

I have tried it a dozen different ways but i always get #error in the report.

Help!

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 01:29:34

try like this

=sum(iif(Fields!t_offc.Value like "RN%", Fields!t_amnt.Value, 0))

cardgunner

326 Posts

Posted - 2008-08-15 : 08:20:45

I did but I get 0 for results. Which is different then what I have been getting which is #error.

I have tried =sum(iif(Fields!t_offc.Value="RN100", Fields!t_amnt.Value, 0))

and I get #error

Also the "*" is the wildcard in reporting services. In a unrelated report I tried % instead of * and it didn't work

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 12:28:38

quote:
Originally posted by cardgunner

I did but I get 0 for results. Which is different then what I have been getting which is #error.

I have tried =sum(iif(Fields!t_offc.Value="RN100", Fields!t_amnt.Value, 0))

and I get #error

Also the "*" is the wildcard in reporting services. In a unrelated report I tried % instead of * and it didn't work

CardGunner

wat about this


=sum(iif(Left(Fields!t_offc.Value,2) = "RN", val(Fields!t_amnt.Value), 0))

cardgunner

326 Posts

Posted - 2008-08-15 : 12:57:14

I get a result of 0

Could this be because I'm asking to sum as set of conditions in the footer?

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 13:19:19

quote:
Originally posted by cardgunner

I get a result of 0

Could this be because I'm asking to sum as set of conditions in the footer?

CardGunner

Nope...thats a not a problem.Are you sure you've enough data satisfying the given condition?

cardgunner

326 Posts

Posted - 2008-08-15 : 13:25:04

I have a total of 28 records. 20 of them are RN records. They have a total of 2680.75. the grand total for all 28 recprds is 3451.53

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 13:29:09

try this and see if there's any difference?
=sum(iif(Left(Fields!t_offc.Value,2) = "RN", val(Fields!t_amnt.Value), 0),"Yourtablename")

cardgunner

326 Posts

Posted - 2008-08-15 : 14:05:56

I tried
=sum(iif(Left(Fields!t_offc.Value,2) = "RN", val(Fields!t_amnt.Value), 0),"tmp")

The value expression for the textbox ‘textbox16’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

But I'm unclear on the YourTableName.

CardGunner

cardgunner

326 Posts

Posted - 2008-08-15 : 14:12:53

this is a representation of the SQL statement I'm using for the dataset

 select  	tmp.*, 
		tmp.t_qant*tmp.t_pric t_amnt
from 
(select srv.t_unit,
	srv.t_offc, 
	srv.t_qant,
	srv.t_pric,
	srv.t_ordr
from 	srv
union all 
select	rnt.t_unit,
	rnt.t_offc
	rnt.t_qant
	srv.t_pric
	rnt.t_ordr
from 	rnt
)	tmp join
	main on main.t_unit=tmp.t_unit

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 14:13:21

quote:
Originally posted by cardgunner

I tried
=sum(iif(Left(Fields!t_offc.Value,2) = "RN", val(Fields!t_amnt.Value), 0),"tmp")

The value expression for the textbox ‘textbox16’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

But I'm unclear on the YourTableName.

CardGunner

thats the name of the table in whose footer you've put this expression. go to properties window and see the name of table.

cardgunner

326 Posts

Posted - 2008-08-15 : 14:18:37

darn, I thought that may have done it but no. I still get #error

My table name is table1 for reference.

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 14:24:57

quote:
Originally posted by cardgunner

darn, I thought that may have done it but no. I still get #error

My table name is table1 for reference.

CardGunner

can i see the expression used?

cardgunner

326 Posts

Posted - 2008-08-15 : 14:27:16

By the way I just expanded all the fields contained in the tmp.* abd typed them all out and still #error.

I thought maybe becasue I didn't name the field speciflly....

worth a try.

CardGunner

cardgunner

326 Posts

Posted - 2008-08-15 : 14:28:08

=sum(iif(Left(Fields!t_offc.Value,2) = "RN", val(Fields!t_amnt.Value), 0),"table1")

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 14:31:29

And are you sure you've not given any filters on table as well as dataset causing the reqd data to be filtered of?

cardgunner

326 Posts

Posted - 2008-08-15 : 14:39:03

From bad experiences in the past I try to do all filters in my sql statement.

The details section has all 28 records and it shows it correctly.

I looked in the table properties wondering if maybe a filter got added but no.

I don't even have any groups. This was supposed to be a slam bam report. Three Parameters a couple a totals and done.

Not so...

CardGunner

cardgunner

326 Posts

Posted - 2008-08-15 : 14:44:29

I just rebuilt the report and still the same #error.

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 14:51:53

just try this and see if atleast this works

=sum(iif(Left(Fields!t_offc.Value,2) = "RN",1, 0))

cardgunner

326 Posts

Posted - 2008-08-15 : 14:57:24

20

Perfect.

CardGunner

cardgunner

326 Posts

Posted - 2008-08-15 : 15:00:38

just tried
=sum(iif(Left(Fields!t_offc.Value,2) = "RN", Fields!t_qnt.Value * Fields!t_pric.Value , 0))

same #error

In case you where going to ask.

CardGunner

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources