Possible to do this report?

Hi,
Is it possible to get reporting services to create a report with a repeating
grouping structure with a total at the bottom of each group? I would want to
start with a report header that is only output once. Then for each repeating
group, I need a header, the body, and a footer with the total. Here is an
example of the structure (hope it makes sense!!!)...
>>Report Header Begin<<
Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
Title=Hello
>>Group Header End<<
>> Group Body Begin<<
Code Customer Quantity
XYZ Smith 10
ABC Jones 10
123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
Total = 30
>>Group Footer End<<
>>Group Header Begin<<
Title=OK
>>Group Header End<<
>> Group Body Begin<<
Code Customer Quantity
XYZ Smith 5
ABC Jones 5
123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
Total = 15
>>Group Footer End<<
--
McGeeky
http://mcgeeky.blogspot.comThis is totally supported and easy to do. You need to add groups to your
table. Each group can have header and footers. You can then add an
expression that totals the current field. Search Books Online for the word
grouping
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"McGeeky" <anon@.anon.com> wrote in message
news:eL6gP$jfFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to get reporting services to create a report with a
> repeating grouping structure with a total at the bottom of each group? I
> would want to start with a report header that is only output once. Then
> for each repeating group, I need a header, the body, and a footer with the
> total. Here is an example of the structure (hope it makes sense!!!)...
>>Report Header Begin<<
> Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
> Title=Hello
>>Group Header End<<
>> Group Body Begin<<
> Code Customer Quantity
> XYZ Smith 10
> ABC Jones 10
> 123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
> Total = 30
>>Group Footer End<<
>>Group Header Begin<<
> Title=OK
>>Group Header End<<
>> Group Body Begin<<
> Code Customer Quantity
> XYZ Smith 5
> ABC Jones 5
> 123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
> Total = 15
>>Group Footer End<<
> --
> McGeeky
> http://mcgeeky.blogspot.com
>|||Thanks Bruce. I have managed to get a group header and group body working
but cannot make a group footer, only a table footer (which I don't need).
How do I make a group footer?
Thanks!
--
McGeeky
http://mcgeeky.blogspot.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23yu%23nEkfFHA.1248@.TK2MSFTNGP12.phx.gbl...
> This is totally supported and easy to do. You need to add groups to your
> table. Each group can have header and footers. You can then add an
> expression that totals the current field. Search Books Online for the word
> grouping
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "McGeeky" <anon@.anon.com> wrote in message
> news:eL6gP$jfFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Is it possible to get reporting services to create a report with a
>> repeating grouping structure with a total at the bottom of each group? I
>> would want to start with a report header that is only output once. Then
>> for each repeating group, I need a header, the body, and a footer with
>> the total. Here is an example of the structure (hope it makes
>> sense!!!)...
>>Report Header Begin<<
>> Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
>> Title=Hello
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 10
>> ABC Jones 10
>> 123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 30
>>Group Footer End<<
>>Group Header Begin<<
>> Title=OK
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 5
>> ABC Jones 5
>> 123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 15
>>Group Footer End<<
>> --
>> McGeeky
>> http://mcgeeky.blogspot.com
>|||Right click the group and there should be a dialog box, where you can select
display footer...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"McGeeky" <anon@.anon.com> wrote in message
news:uuaheqkfFHA.1044@.tk2msftngp13.phx.gbl...
> Thanks Bruce. I have managed to get a group header and group body working
> but cannot make a group footer, only a table footer (which I don't need).
> How do I make a group footer?
> Thanks!
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23yu%23nEkfFHA.1248@.TK2MSFTNGP12.phx.gbl...
>> This is totally supported and easy to do. You need to add groups to your
>> table. Each group can have header and footers. You can then add an
>> expression that totals the current field. Search Books Online for the
>> word grouping
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "McGeeky" <anon@.anon.com> wrote in message
>> news:eL6gP$jfFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Is it possible to get reporting services to create a report with a
>> repeating grouping structure with a total at the bottom of each group? I
>> would want to start with a report header that is only output once. Then
>> for each repeating group, I need a header, the body, and a footer with
>> the total. Here is an example of the structure (hope it makes
>> sense!!!)...
>>Report Header Begin<<
>> Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
>> Title=Hello
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 10
>> ABC Jones 10
>> 123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 30
>>Group Footer End<<
>>Group Header Begin<<
>> Title=OK
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 5
>> ABC Jones 5
>> 123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 15
>>Group Footer End<<
>> --
>> McGeeky
>> http://mcgeeky.blogspot.com
>>
>|||Doh! So simple, but so easily missed.
--
McGeeky
http://mcgeeky.blogspot.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OzsLHvkfFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Right click the group and there should be a dialog box, where you can
> select display footer...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "McGeeky" <anon@.anon.com> wrote in message
> news:uuaheqkfFHA.1044@.tk2msftngp13.phx.gbl...
>> Thanks Bruce. I have managed to get a group header and group body working
>> but cannot make a group footer, only a table footer (which I don't need).
>> How do I make a group footer?
>> Thanks!
>> --
>> McGeeky
>> http://mcgeeky.blogspot.com
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23yu%23nEkfFHA.1248@.TK2MSFTNGP12.phx.gbl...
>> This is totally supported and easy to do. You need to add groups to your
>> table. Each group can have header and footers. You can then add an
>> expression that totals the current field. Search Books Online for the
>> word grouping
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "McGeeky" <anon@.anon.com> wrote in message
>> news:eL6gP$jfFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Is it possible to get reporting services to create a report with a
>> repeating grouping structure with a total at the bottom of each group?
>> I would want to start with a report header that is only output once.
>> Then for each repeating group, I need a header, the body, and a footer
>> with the total. Here is an example of the structure (hope it makes
>> sense!!!)...
>>Report Header Begin<<
>> Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
>> Title=Hello
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 10
>> ABC Jones 10
>> 123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 30
>>Group Footer End<<
>>Group Header Begin<<
>> Title=OK
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 5
>> ABC Jones 5
>> 123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 15
>>Group Footer End<<
>> --
>> McGeeky
>> http://mcgeeky.blogspot.com
>>
>>
>

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