posted 5/12/2010 by kylewalker - Views: [21019]
Recently I was tasked with creating a report that needed to have a product hierarchy occupy only one field. The hierarchy I will use as an example is comprised of three fields: Manufacturer, Category, and Model. And the output of my stored procedure is as shown below.
Now, when you create row groups for Manufacturer, Category, and Model and then preview your report, it will look something like this.
The problem with this is that the client wants those first three columns to only take up one column... No longer a problem. Here's all you have to do. Right-click on the field that's farthest to the right that belongs in the hierarchy (in this case, "Model") and the select "Insert Row" and then "Outside Group - Above".
Then, when you have the new row created above the "Model" cell, insert the name of the next-to-last group in the hierarchy ("Category") into the cell directly above.
Repeat these steps for as many columns as you want to combine. Once you have them all stacked into one column, delete the surplus columns.
It is a good idea to add some padding so that you can identify the different levels of the hierarchy (you will set this in the Properties, under "Padding")
So now, when you preview your report, it will look something like this:
Of course there are a lot more cosmetic changes that you can make and even subtotals by each group.
Hopefully this helps!
Wish I would have found this earlier. I ended spending 30 minutes trying to figure out how to do this last week.
Thank you for your post, works very well BUT if the value of the category or model is null then you will see a blank space between rows. I tried to use the =IIF(Fields!txtData2.Value IS NOTHING, True, False) but that does not work either.
Is there anyway I can fix it?
I personally would deal with that at the stored procedure level. If the value is null, either set it as 'Unknown', otherwise just return the value.
If this layout is not an intial design requirement, this is an excellent way to get the layout as you want. If you know you want this ahead of time, add your groups with headers and then stack the group names and use padding as you suggest.
I have had a lot of issues deleting columns resulting in permanently killing the RepeatOnNewPage = True setting for the column headers. You can set it but it no longer works. What is your experience with the process above affecting repeating column headers? When I stack the group names in this way, I do not delete the columns that were added by the group, I just physically collapse them to nothing so I don't mess up the repeating column headers.
Great blog, thanks.