| 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
|
trying to sum an iif statement in the footer