Conditional group footer sum

HI,
I am working with a developer who has an interesting problem. They have
data field in a sql table that is of a numeric value a second field that
tells whether or not the previous value is a debit or credit for a general
ledger.
They want to show on a report a sum of the numeric field in a group footer.
We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
for a hidden field on the report in the detail row of a table and then using
the Reportitems Syntax to display that fields value in the group footer, but
we get an out of scope error.
Looking for possible suggestions, code sample, or alternatives. Changing
the data in the SQL table is not a possibility.
Thanks!!I recently tried doing something similar to this but I was getting a data
type error. I resolved it by making the following change:
Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got errors
New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
Fields!Amount.Value*0)
I don't know why mulitplying by 0 gives the correct data type, but simply
putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...) I
could find that worked.
"Mark" wrote:
> HI,
> I am working with a developer who has an interesting problem. They have
> data field in a sql table that is of a numeric value a second field that
> tells whether or not the previous value is a debit or credit for a general
> ledger.
> They want to show on a report a sum of the numeric field in a group footer.
> We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
> for a hidden field on the report in the detail row of a table and then using
> the Reportitems Syntax to display that fields value in the group footer, but
> we get an out of scope error.
> Looking for possible suggestions, code sample, or alternatives. Changing
> the data in the SQL table is not a possibility.
> Thanks!!|||The reason why multiplying with 0 works is that it preserves the original
datatype of the numeric field (which could be anything like UInt16, Decimal,
etc.).
This should work (the constant value 0.0 is a System.Double at runtime):
=sum(iif(Fields!Type.Value = 'Dr', CDbl(Fields!Amount.Value), 0.0)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:9EAB31A6-E7CA-4912-82BC-A678EAE9CA0C@.microsoft.com...
> I recently tried doing something similar to this but I was getting a data
> type error. I resolved it by making the following change:
> Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got
errors
> New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
> Fields!Amount.Value*0)
> I don't know why mulitplying by 0 gives the correct data type, but simply
> putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...)
I
> could find that worked.
> "Mark" wrote:
> > HI,
> >
> > I am working with a developer who has an interesting problem. They have
> > data field in a sql table that is of a numeric value a second field that
> > tells whether or not the previous value is a debit or credit for a
general
> > ledger.
> >
> > They want to show on a report a sum of the numeric field in a group
footer.
> > We have tried writing a conditional
formula(=Iif(FieldB="dr",Sum(FieldA),o))
> > for a hidden field on the report in the detail row of a table and then
using
> > the Reportitems Syntax to display that fields value in the group
footer, but
> > we get an out of scope error.
> >
> > Looking for possible suggestions, code sample, or alternatives.
Changing
> > the data in the SQL table is not a possibility.
> >
> > Thanks!!