Need expression for conditional count on a group total count

Question

Hi,

 

I have and ssrs 2008 report which should display the counts of duplicate records based on some match criteria. Where the match criteria could be two types 1)xNumber,Xtype 2)ABCid,xyzId
On the report I need to display following counts
2 Records ≥ 3 Records Total Records Received 2011 Total Records  2012 Total Records
2 Records meaning the number of matching or duplicate files based on the corresponding match criteria

I am able to get the values for following fields by grouping the table by the two match crieria above
but have issues with finding expression for 2Records snd >=3 records tables, as SSRS does not support aggregate on aggregate
But precisely this is what exactly I need.

Following two expressions which represent my requirement do not  work
=Count(IIF(CountRows("XNoXTypeGroup") = 2, CountRows("XNoXTypeGroup"),Nothing))

=Count(IIF(CountRows("XNoXTypeGroup") >= 3, 1,Nothing))

The following expression gives me a count of all the XNoXTypeGroups in the current parent group
=Count("XNoXTypeGroup", "ParentGroup")
but what I need is how many XNoXTypeGroups groups have rowcount as 2 and how many have more than 2 for each parent group.

I would appreciate any help on this.

 Thanks,

Hari

 

Answer 1

=Sum(IIF(CountRows("XNoXTypeGroup") >= 3, 1,0))

This will give you the count of groups having rows >=3

=Sum(IIF(CountRows("XNoXTypeGroup") = 2, 1,0))

This will give you the count of groups having rows =2

---

Cheers,
Jason
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Answer 2

Hi Jason,

This would not work as it errors out saying "Aggregate functions can not be nested inside other aggregate functions".

However I got this working following your blog post at http://beyondrelational.com/modules/2/blogs/65/posts/11579/aggregate-of-an-aggregate-function-in-ssrs.aspx

Thanks a for the post. But now I have a different issue.

I want to hide the group rows which had the expressions that called the code to add count. When I hide them the expression is probably not getting executed and hence I see the total subgroup count as 0.

Do you have a solution for this as well?

Thanks,

Hari

Answer 3

Hmm, if you hide them, then they will not get executed. But thankfully you can overcome it with the way explained below :)

http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

Let me know if u have any issues

---

Cheers,
Jason
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)