Possible to access no. rows in groups outside the table?

Hi there,

I'm currently grouping data on some criteria, the way the data works basically means that there are between 2-3 groups guaranteed (no more). In the Group Header I have CountRows(table1_group) thus giving me the total number of rows in each group at the top of each grouping. However, I need to do a calculation in a textbox above the table using these row counts. The method of grouping is not too difficult when using the VB syntax in the grouping expression but more difficult to get the same effect from the SQL side and hence I don't want to have separate datasets calling different queries to get the information that way. Is there anyway to get access to these row counts on the groups? Reporting Services can't know how many groups there will be before processing so I'm not sure that this is possible? Ideally I suppose if there was a Group CountRows collection of some kind that could be accessed in an expression in the textbox or custom code then this might be possible. I could also add an invisible column and set the values to something specific depending on the group if there was a way to count the number of values in the table (unique values repeated in columns in a group, but unique to that group).

Any help is much appreciated,
Thanks.

Sorry, but it would also be feasible to place the textbox within the table by moving the headings and other table data downward leaving a gap at the top. However, the textbox although in the table is presumably still outside the scope of the group. Just thought I'd mention it incase it sparked an idea by anyone.

Thanks again.

|||

Have an invisible list above your table and add group the list by the same fields as your table and use Count or any other aggregate functions in textboxes inside that list.

Shyam

|||

Thanks for your response, but I'm not sure how to implement that. If I have say 3 groupings in my table. The RowCount at the top of the group headers gives the following at the top of each grouping in the table:

Group1 Total: 4

Group2 Total: 7

Group3 Total: 22

Then a single textbox at the top of the page would say

Group1 + Group2 = 11

Group1 + Group2 = 50% of Group3.

How would I accomplish this using the invisible list?

Thanks again.

|||

Have a hidden table at the top of your report and have the same 3 groupings and then delete all the rows except the group header rows. Write 3 functions in report code which will increment a public variable (3 ublic variables declared at the top of the report) whenever it is called. Say, the function names are CountGroup1, CountGroup2, CountGroup3.

In each of your 3 group headers, call each of the corresponding functions in the code to count the group records. Have the expression in the group headers like this:

=Code.CountGroup1(CountDistinct(Fields!Field1.Value, "table1_Group1"))

=Code.CountGroup2(CountDistinct(Fields!Field1.Value, "table1_Group2"))

=Code.CountGroup3(CountDistinct(Fields!Field1.Value, "table1_Group3"))

And you can access the counts by referring to the public variables at the top of your main table using an expression something like this Code.VariableName

Shyam