Sum Totals on Conditional field

Mike

unread,

Dec 7, 2004, 10:46:44 AM12/7/04

to

I am having trouble get the Total on a column, i get the following error:

c:\inetpub\wwwroot\cireports\PurchaseDetails.rdl The value expression for
the textbox 'textbox15' uses an aggregate function on data of varying data
types. Aggregate functions other than First, Last, Previous, Count, and
CountDistinct can only aggregate data of a single data type.

This is what i have, the report needs to not include a certain value if it
meets a certain condition, also to note the stored procedure that feeds this
report is used in several other parts of the application that this report is
working with, so it is not possible to change the stored procedure to do the
calculations in the stored procedure.

In my Dataset I created a Calculated Field called 'Amt' with the following
expression
=IIF(Fields!Col1.Value = "Condition",0,Fields!Col2.Value)

Then in the Report Designer I created a Report with the following Detail
Columns
=Fields!Col1.Value,
=Fields!Amt.Value,=Fields!Col3.Value,=Fields!Amt.Value-Fields!Col3.Value

Then in the Report Footer I have the following:

For the =Fields!Amt.Value Column I have =Sum(Fields!Amt.Value)
For the =Fields!Col3.Value Column I have =Sum(Fields!Col3.Value)
For the =Fields!Amt.Value-Fields!Col3.Value Column I have
=Sum(Fields!Amt.Value-Fields!Col3.Value)

Everything works except the =Sum(Fields!Amt.Value) total column

Can anyone explain why 2 of the fields that use the Calculated Field as part
of a formula work but the one that is suppose to Sum up the column does not.

Thanks
Mike

Mike

unread,

Dec 7, 2004, 12:43:42 PM12/7/04

to

Okay, i fixed my problem, here is the solution for anyone else who may run
across this

I changed the footer column Sum(Fields!Amt.Value) to
=Sum(CDBL(Fields!Amt.Value))

"Mike" <mike.no.s...@no.spam.com> wrote in message
news:uPiZkQH3...@tk2msftngp13.phx.gbl...

Dave

unread,

Sep 13, 2007, 6:39:02 PM9/13/07

to

THANK YOU!! I had the exact same problem with a "money" column.

From http://www.developmentnow.com/g/115_2004_12_0_0_453764/Sum-Totals-on-Conditional-field.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Tom

unread,

Oct 31, 2007, 1:17:13 AM10/31/07

to

Mike - exactly same problem my end. Many Thanks for the simple solution.

Chad

unread,

Mar 10, 2008, 2:59:16 PM3/10/08

to

Can anyone tell me how I can do this when I am AVG a column. I have the following code =FORMATPERCENT(avg(iif(Fields!hrs_cum.Value = 0, 0, Fields!hrs_cum.Value / iif(Fields!hrs_curterm.Value = 0, 1, Fields!hrs_curterm.Value))))

Now once it finds a zero it outputs a #error. Hoe can I make it handle the zero.

I also get this warning...Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

Sara

unread,

Oct 23, 2008, 7:32:37 AM10/23/08

to

thank u , i had the same problem... solved by adding "cdbl"

From http://www.developmentnow.com/g/115_2004_12_0_0_453764/Sum-Totals-on-Conditional-field.ht

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/

NikitaZ

unread,

Sep 17, 2009, 3:06:16 PM9/17/09

to

Thank you so much,,it worked perfect for me....i was going mad trying all sorts of expressions.Thanks once again.

David Bauernschmidt

unread,

Nov 30, 2009, 3:47:34 PM11/30/09

to

Thank you very much....worked exactly as needed.

From http://www.developmentnow.com/g/115_2009_9_0_0_453764/Sum-Totals-on-Conditional-field.htm

manpvr

unread,

Dec 29, 2009, 10:21:16 AM12/29/09

to

Hey Chad, did you find a solution for your problem???