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,