Sometimes when you create a report with a group (let's say on customer name) and then you wish to have a sub-group (let's use product name) and also have totals for that sub-group.
You may have a case where some groups do not show all of the sub-groups...in the example, not all products are listed for each customer. However, you want to show all products for all customers and have a sub-total for all products sold to all customers...even if the sub-total is just a zero.
First off, the most likely reason why you don't have all sub-groups showing for all groups would be that the data doesn't exist in the database in the first place. If customer A has never purchased product 3, then your report isn't going to show a sub-total for product 3.
If it's really important to you that each group show all sub-groups and at least a zero for a sub-total, then there are workarounds. One solution of course is to redesign your report based on a Command object or a stored procedure that brings those records. This blog post though will show you a different method of showing all sub-groups plus a total by "rolling up" data in a main report and passing this rolled up data to a subreport that brings back all sub-groups (products). This is a bit of work to do but if you really need those sub-groups and zeroes then you may find it worthwhile to at least look at this technique.
First, start by opening up the sample report located here. Note that there are 2 different sets of data for each customer...the first data set is much smaller and is based on tables...this is the one that is problematic. The second data set does in fact have all products listed for all customers. The second data set is in a sub-report which brings back all products.
If you go to the Design tab of the report you'll see a @ProdNames formula (with a green background) as well as @SalesAmt formula. If you open these up in the formula editor, you'll see that these are String Running Totals. They gather up product and sales info for each customer. There's a reset for these string running totals on the customer group footer (Group Footer 1b).
There is also a sub-report located in Group Footer 1a...if you right click on the sub-report and choose Change Subreport Links, you'll see that the string running totals are passed to the subreport to ?Pm parameters. Note that they are one way links as they do not directly link the formulas to any fields on the sub-report.
Open up the sub-report, please, and then go to the Database > Database Expert...there is only 1 table in this subreport and the record set that the report returns is simply all products with no customer information at all. If you look at the Details section in the sub-report, there's another formula "All Product Sales Amts" that converts both of the ?Pm links (those were our links from the string running totals) into arrays. The formula then checks the current details record (product name) against the product array to ensure that the correct sales amount is brought back from the sales array.
To use this technique against your own data then you can follow these simplified steps. Also remember to look at the notes in the sample's Report Header section.
- Copy the 2 string running totals from the main report onto your existing report. Also ensure that you copy the reset formula and put the reset on your group footer.
- Just change the references to Xtreme fields and summaries in those two string running total formulas.
- Then you'll have to create a sub-report and bring in only the 1 table that contains the sub-groups...in the sample case again it is just the product table.
- Link the sub-report on the 2 string running total fields but do not have them link to anything on the sub-report...i.e. this should be a one way link
- If you now copy the "All Product Sales Amts" formula into your subreport, you'll need to change the references to the Xtreme fields to your own.
- You should now be able to use the All Product Sales Amt fields in your chart or cross-tab
Hopefully, if all went well for you, you can see all of the sub-groups for all of your groups.
