Users want to see the details of orders. They would like to see the order number and then a small table showing the details (product name, promotion, quantity, and unit sell price) within each order.
Create a simple list report with Sales order / Order number, Products / Product, Sales fact / Quantity, and Sales fact / Unit sale price as columns.
Creating sections in a report is helpful to show a data item as the heading of a section. When you run the report, separate sections appear for each value. There is a way to reconstruct the report, and this is how to do it:
Click on the Order number column. Hit the Section button on the toolbar as shown in the following screenshot:
You will see that Report Studio automatically creates a header for Order number and moves it out of the list.
Notice that the Order number field is now grouped as shown in the following screenshot:
Run the report to test it.
The information we are trying to show in this report can also be achieved by normal grouping on order number. That will bring all the related records together. We can also set an appropriate group/level span and sorting for better appearance.
However, in this recipe, I want to introduce another feature of Report Studio called section.
When you create a section on a column, Report Studio automatically does the following:
It creates a new list object and moves the current report object (in our case, the existing list) inside that. This is report nesting. Both the inner and outer objects use the same query.
It creates grouping on the column selected for section, which is Order number in this case. It also creates a group header for that item and removes it from the inner list.
It formats the outer list appropriately. For example, hiding the column title.
Some of the advantages of creating sections are as follows:
As mentioned earlier, Report Studio does a lot of the work for you and gives you a report that looks more presentable. It makes the information more readable by clearly differentiating different entities; in our case, different orders. You will see mini-lists or tables, one for each Order number, as shown in the following screenshot:
As the outer and inner queries are the same, there is no maintenance overhead.
The Creating a nested report – defining the master-detail relationship recipe in Chapter 2, Advanced Report Authoring