SSRS: The expansion and the effect of grouping aggregation in reports and the techniques of dealing with irregular hierarchies

Expansion and collection of grouped aggregations in reports and techniques for dealing with irregular hierarchies (do not expand without child elements, delete + symbols)

The expansion and the effect of packet aggregation is very common in the SSRS, and sometimes there are some more special situations to deal with. For example, when grouping merge, some hierarchies are irregular, and some groups have two layers, what should be done in this situation?

Note that the following requirements, if there is no other France below, do not show the + number, if the United states-utah/minnesota states do not have the children of the city, they do not show the + number.

Such requirements are occasionally encountered in the SSRS, which can be understood as how to handle display and concealment problems in asymmetric hierarchies.

The techniques shown below first review how to group vertically, using the table control instead of using Tablix. Finally, it shows how to not display the + number when there is no child hierarchy, so as not to mislead users into making unnecessary clicks.

The demo database used is AdventureWorks2012, in order to show the problem caused by irregular layers, the SQL in the dataset is written intentionally like this-

SELECT countryregionname
            countryregionname = ' France ' THEN ' ELSE stateprovincename end as
                  stateprovi  Ncename, when
            countryregionname = ' France ' THEN ' "when city = ' Nevada ' THEN '" when city
                   = ' Duluth ' THEN "ELSE City" as City
                 ,
            SalesQuota,
            SalesYTD,
            saleslastyear from 
Sales.vsalesperson
WHERE countryregionname in (' United States ', ' United Kingdom ', ' France ')

The following starts by creating a report and adding DataSource, DataSet, and table controls and populating the data