Conditional SUM?

I have a table that lists both "Buy" and "Sell" orders. I want to display a
SUM of all Buys and a SUM of all Sells in the footer. Is there a way to
conditional SUM the rows based on a column value in reporting?
Thanks,
JeffOn Apr 30, 11:22 am, Jeff Tu <Jef...@.discussions.microsoft.com> wrote:
> I have a table that lists both "Buy" and "Sell" orders. I want to display a
> SUM of all Buys and a SUM of all Sells in the footer. Is there a way to
> conditional SUM the rows based on a column value in reporting?
> Thanks,
> Jeff
Generally, accessing table control data in a footer is very
restrictive. You can either try to access the datasets directly or
access the dataset(s) via hidden parameters. You might be able to
access the dataset from the footer w/: =iif(Max(Fields!
SomeFieldName.Value, "SomeDataSetName") = "Buy", Sum(Fields!Buy.Value,
"SomeDataSetName"), Sum(Fields!Sell.Value, "SomeDataSetName"))
-or-
=iif(Parameters!SomeParameterName.Value = "Buy", Sum(Parameters!
Buy.Value), Sum(Parameters!Sell.Value))
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you for your reply. Unfortunately, the example you provided would not
work for me. To apply your first example to my case, it would read like this:
=iif(Max(Fields!SomeFieldName.Value, "SomeDataSetName") = "Buy",
Sum(Fields!Quantity.Value, "SomeDataSetName"), Sum(Fields!Quantity.Value,
"SomeDataSetName"))
This obviously would NOT work since I only have one Quantity column, nor
would it work if I put a value of zero for the false condition.
As for your second example, I do not see how summing up parameters would
help when what I need is summing up of row values. Perhaps you can elaborate
on this?
"EMartinez" wrote:
> On Apr 30, 11:22 am, Jeff Tu <Jef...@.discussions.microsoft.com> wrote:
> > I have a table that lists both "Buy" and "Sell" orders. I want to display a
> > SUM of all Buys and a SUM of all Sells in the footer. Is there a way to
> > conditional SUM the rows based on a column value in reporting?
> >
> > Thanks,
> > Jeff
>
> Generally, accessing table control data in a footer is very
> restrictive. You can either try to access the datasets directly or
> access the dataset(s) via hidden parameters. You might be able to
> access the dataset from the footer w/: =iif(Max(Fields!
> SomeFieldName.Value, "SomeDataSetName") = "Buy", Sum(Fields!Buy.Value,
> "SomeDataSetName"), Sum(Fields!Sell.Value, "SomeDataSetName"))
> -or-
> =iif(Parameters!SomeParameterName.Value = "Buy", Sum(Parameters!
> Buy.Value), Sum(Parameters!Sell.Value))
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

conditional aggregate?

Is there any way to add some sort of condition, or where clause to an
aggregate function? I have a report with three nested lists - the detail is
in the inner list, list3. In the second (middle) list, I want to count the
number of rows for column x where column y is not equal (<>) "This".
I have tried adding a textbox in the 3rd list to tally rows where the
condition satisfies a count or a 'tic', and then attempted to add another
expression in list 2 that would sum the new textbox values in list 3, but am
ripping my hair out with error messages - what is the one about 'aggregates
can only be used on report items in the header and footer'? As far as I
understand, you can't really use Fields in the header or footer anyway - so
how one would use an aggregate function in that manner is beyond me...
Anyway, I know Crystal offers a variety of ways to accomplish this type of
thing - is there anyway to do a sum or a count for field X in a detail group
where field Y in the same detail group meets a certain criteria?
Thanks,Myles:
You can do a count in a group based on the condition or expression.
You can do soemthing like this:
=RunningValue(IIf(y.value<>"This",X,Nothing),Count,"grpName")
Or you can have this expression for you count:
CountDisctinct(IIf(y.value<>"This",X,Nothing),"grpName")
Hope this answers your question. For more information, search for
RunningValue in reporting services on msdn.
"Myles" wrote:
> Is there any way to add some sort of condition, or where clause to an
> aggregate function? I have a report with three nested lists - the detail is
> in the inner list, list3. In the second (middle) list, I want to count the
> number of rows for column x where column y is not equal (<>) "This".
> I have tried adding a textbox in the 3rd list to tally rows where the
> condition satisfies a count or a 'tic', and then attempted to add another
> expression in list 2 that would sum the new textbox values in list 3, but am
> ripping my hair out with error messages - what is the one about 'aggregates
> can only be used on report items in the header and footer'? As far as I
> understand, you can't really use Fields in the header or footer anyway - so
> how one would use an aggregate function in that manner is beyond me...
> Anyway, I know Crystal offers a variety of ways to accomplish this type of
> thing - is there anyway to do a sum or a count for field X in a detail group
> where field Y in the same detail group meets a certain criteria?
>
> Thanks,|||thank you sam, I will give that a try!
"sam" wrote:
> Myles:
> You can do a count in a group based on the condition or expression.
> You can do soemthing like this:
> =RunningValue(IIf(y.value<>"This",X,Nothing),Count,"grpName")
> Or you can have this expression for you count:
> CountDisctinct(IIf(y.value<>"This",X,Nothing),"grpName")
> Hope this answers your question. For more information, search for
> RunningValue in reporting services on msdn.
> "Myles" wrote:
> > Is there any way to add some sort of condition, or where clause to an
> > aggregate function? I have a report with three nested lists - the detail is
> > in the inner list, list3. In the second (middle) list, I want to count the
> > number of rows for column x where column y is not equal (<>) "This".
> >
> > I have tried adding a textbox in the 3rd list to tally rows where the
> > condition satisfies a count or a 'tic', and then attempted to add another
> > expression in list 2 that would sum the new textbox values in list 3, but am
> > ripping my hair out with error messages - what is the one about 'aggregates
> > can only be used on report items in the header and footer'? As far as I
> > understand, you can't really use Fields in the header or footer anyway - so
> > how one would use an aggregate function in that manner is beyond me...
> >
> > Anyway, I know Crystal offers a variety of ways to accomplish this type of
> > thing - is there anyway to do a sum or a count for field X in a detail group
> > where field Y in the same detail group meets a certain criteria?
> >
> >
> > Thanks,