April 30, 2009 at 2:00 am
Hi,
I am using a table on my report and have a field called 'other1' in my detail section of my table.
It is in the detail section of my table because responses from different people are listed on the page using this 'other1' field.
Now the problem I'm having is when I try to calculate a total at the bottom of my table.
The total field is currently in the footer of my table and the expression currently looks like this:-
=iif(count(Fields!ID.Value)-1 <> 0, Sum(ReportItems!other1.Value) / Count(Fields!ID.Value -1),3)
ID is a field in my SQL Server table which is called 'submissions'.
So if ID -1 is not equal to 0 then sum of other1 field value entries divided by a count of the ID's from my submissions table -1 else 3
But it is coming up with the following error:-
The value expression for the textbox 'total' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.
But I can't move my 'other1' expression into the header or footer of my table as it will not then list all of the entries in a list on the page, which is what I want to see on the report.
So how else can I get around this problem?
If I take the Sum out of my expression it gets rid of the error message but the total just displays 0.
Any help most appreciated.
April 30, 2009 at 10:24 am
Hi Zeetec
Just try below expression:
=iif(count(Fields!ID.Value)-1 0, Sum(Fields!Other1.Value, "DataSetName") / (Count(Fields!ID.Value)-1),3)
May 1, 2009 at 1:37 am
Ok thanks for that I'll give it a try.
But I eventually managed to get that working but I had to go the long way around.
I just basically entered the values for the report items in my formula and then did a sum on these.
Linked to the same report, in my other1 field I have a zero value aswell.
so I want to set a condition to say something like:-
IIf value of this textbox is zero then hide this textbox from the report.
How would I be able to do this?
Currently it shows all zero's.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply