Hello,I been reading a lot of posts about how Reporting Services doesnt handle grouping subtotals and aggregates of aggregates very well. However, with some of the very helpful posts (from which Ive borrowed the code) Ive gotten a little further but I am now stuck. I am trying to get the subtotals of the code I am using. It works, sort of The problem is the subtotal is showing up in the next grouping, not the current one. For example, Here's the structure:Group 1 Header (State) Group 2 Header (City) Group 2 FooterGroup 1 FooterResultsNew York Albany 10 Manhattan 10 Buffalo 10New York 0Florida Jacksonville 5 Miami 5 Tampa 5Florida 30California LA 1 San Fran 2 Hollywood 3California 15Here's the code I am using:DimsumGroupasIntegerFunctionAddValue(ByValnewValueAsDecimal)asIntegersumGroup=sumGroup+newValuereturnsumGroupEndFunctionFunctionGetSum()asobjectGetSum=sumGroupsumGroup=0'ResetthegroupreturnGetSumEndFunctionI put the AddValue() function in the Group2 Header and the GetSum() function in the Group1 footer. My guess is that the groups are not processed together, but I dont know how to get around it. Ive tried other things like referencing the Textbox in Group2, but I found that you cant get a reference to a textbox that is in a different grouping. Is there any way to have the subtotals show up in the proper Group1 footer?Thank you for the help.-Gumbatman

Hi Gumbatman, As I know, the processing sequence of reporting services engine is: table header, table footer, group header, group footer, then details. This is the reason why the subtotal is showing up in the next group. Also, this is why we cannot handle aggregates of aggregates. I do understand your concerns. Actually, this is really a good idea about doing aggregates of aggregates. A possible workaround is creating a new group header. 1. Delete the footer of group1, and then right-click the handle of group1header, insert a row above. Here's the structure: Group 1 new Header Group 1 Header (State) Group 2 Header (City) Group 2 Footer 2. Set the cell of new header with expression "=code.GetSum()". Right-click the handle of this new row, click the properties. In Properties window, set the visibility to be =iif(RowNumber(nothing)=N, true,false). N is the row count of the first group. In your example, N is 3. (You can also you another customer code to calculate the row count of the first group, then replace the expression with "=iif(RowNumber(nothing)=code.FirstRow(), true,false)) 3. Right-click the table footer, insert a row above. Use this new table footer as the last groups footer. In this way, it is looks like a real group. And you could also set toggled for the visibility. Please let me know the results. Regards, Raymond

Raymond, This is amazing! I cant thank you enough for providing me with this solution. Of course, there are a couple of wrinkles that I have a few questions about: I cant seem to get the RowNumber() to work correctly. It is giving me the row counts for the details, which are hidden. So I am getting counts like 865 or 1054 which is correct, if you are counting all the detail rows. Can you give me a little more information about doing the row counts? I did try to CountDistinct and while I got the row count I needed, I couldn't seem to align the results (get a True) to be able to Show or Hide the row. The other thing is that I have the State name in the totals. So even though Group1 appears to be part of the summary of Group2, it shows the next States name. For example: New York Albany 10 Manhattan 10 Buffalo 10Total for (blank) 30Florida Jacksonville 5 Miami 5 Tampa 5Total for New York 15California LA 1 San Fran 2 Hollywood 3Total for Florida 6 Again, thank you so much for your response, I had really given up.

Hi Gumbatman, There are some defects in this solution just as you said. However we can improve that. For the first question: the row count problem. The function RowNumber() will return the number of goup1s details and the result will increase by itself. That is the reason why I choose this function. We need to hide the first group header, the row number of the first group header is 3 in your example. So you can right-click the handle of this new row, click the properties. In Properties window, set the visibility to be =iif(RowNumber(nothing)=3, true,false) or =iif(RowNumber(nothing)<5, true,false). Whatever, you need to know the row counts for the details in the first group, and then hide the first header. Actually, there are many ways to hide the first group header. Like replace the hidden expression for first group header with "=iif(code.GetSum()=0, true,false)". The defect is maybe there is another group which total is 0. By this, this group header couldalso be hidden. Or using hidden expression for first group header with "=iif(Previous(Fields!State.Value) is nothing, true,false)". Choose a proper field value with Previouse(), the first group will return nothing. In this way, we can hide the first group header perfectly. For the second question: the footer name. I think the sample you give me has a little error. It should be like this if you put State name in the totals: New York Albany 10 Manhattan 10 Buffalo 10Total for (Florida) 30Florida Jacksonville 5 Miami 5 Tampa 5Total for California 15California LA 1 San Fran 2 Hollywood 3Total for 6 To solve this issue, we could use Previouse() for the State name. Here's the structure: handles (Table header ) 1 ="Total For"+Previous(Fields!state.Value) =code.GetSum() 1 =Fields!state.Value( toggled by this cell) 2 =Fields!City.Value =your own expression == details details (Table footer) Also, you could set the group2 toggled by the cell I marked in the diagram. Or create a division row between the two group headers. It could be more perfect. At last, the issue about the last groups total value. We can solve this issue by creating a fakerecord in your dataset. For example, insert one record into the dataset. This record just have a state name _fake: union select '_fake' as state, '_fake' as city. Sort the data by descending, so the fake data will show at the last group because of the character '_'. After that, set the hidden expression for each row (not edit group) with =iif(=Fields!state.Value = "_fake", true,false), except the first group header. By this, you could get the last groups total value in this fake group. You could also write the hard-code in table header as I mentioned in above post, if you feel bother to add a fake date. Now we achieve the way of aggregates of aggregates. If you have any good idea, please share with us. Regards, Raymond

Raymond,Thank you so much for all the time you've put into answering my questions. I can't express how amazingly helpful you've been! Additionally, you've given me information that I haven't been able to find anywhere, including several books and many, many website and forum searches.You are right, I did give you the example incorrectly. I'm glad you picked that up. Sorry about that.In short, I got it to work! At first I didn't understand when you talked about hiding the first group header. I kept thinking it was Group Header 1, not just the very first Group Header 1 that appears. So that is working now. I did use the formula you suggested, "=iif(Previous(Fields!State.Value) is nothing, true,false)" because that worked best for me. In my example, the Row Number was 3, but I still am unclear how you are able to determine that. So I wasn't able to use the =iif(RowNumber(nothing)=3, true,false) formula. The reason I am confused on that formula is because you said that the RowNumber() returns Group 1's details, but the three rows (Albany, Manhattan and Buffalo) are Group 2 headers, not the detail. Plus, what if the row number changes? Either way, the other formula worked perfectly. I appreciate you giving me alternatives.I still have to work out the Fake row and put the total there. I am hoping that I won't have to bother you with this again.Thank you so very much for all this great information. It has been enormously helpful!-Gumbatman

Raymond,Well everything was working as I hoped it would. Thanks so much for all your advice.Now the issue is that I have an additional table at the end of thereportthat shows the totals of everything and I need to get that "extra sub-total" (the one created by using the Function) to show on the same line as the other totals.For example,New YorkHeadcountOther Counts Albany 10 5 Manhattan 102 Buffalo 103Total forNew York 3010Florida Jacksonville 54 Miami 54 Tampa 54Total for Florida1512California LA 13 San Fran 23 Hollywood 33Total for California 69The new summary table should appear as:New York3010Florida1512California69The Other Counts summarize the way as expected because they are just straight Sums.The problem is the Headcount was calculated with the GetSum code and appeared at the Header while the other calcs where in the footer. Now I am at a loss as how to get them all on the same line.One thing I tried looking into was putting them all into a Hashtable where I could look up the values, by state. But no luck there.Once again, your help is greatly appreciated.Thanks.-Gumbatman

Hi Gumbatman, Since you already used custom code to achieve the total, Im afraid youd better to calculate the total in the same way, no matter the normal total or the aggregates of aggregates total. Note: you need to write another group of custom code for the new total, you cannot use the same global variable for the different total. It should works if you create new custom code function. Regards, Raymond

Raymond, I was going in that direction but wasn't sure. Now I will for sure do that. Thank you so much for all your help. I could never have gotten this report completed without it. -Gumbatman

Hi Raymond, I have a closer problem as it that you helped to resolve, but my structure is a bit diferent and I would like help to solve it this is my table structure: ---------|------------------------------------| | YEAR2 | ---------|------------------------------------| YEAR | =last(field!reservas.value) | <<<<---- brings me the last month of the year with data of YEAR2 ---------|------------------------------------| and this is the result that I wanna get --->year2 (dim) | 2009 2010.. V 1983 1 12 <<<<------ =last(field!reservas.value) year (dim) 1984.. 5 25 <<<<-------=last(field!reservas.value) --> total 6 37 the problem that I have is how to settle the header and footer groups to get the results, I hope you can help, thanks in advance!! Valen