Microsoft Dynamics NAV 2015 Professional Reporting
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

Grouping

When you add data to a Tablix, in many cases you will add groupings to create a different view on the data. When you add a group it is better to give it a proper name, so you know why it is there. You can create multiple and different types of groups in a Tablix and, in this section I will explain what a group is and the difference between parent-child and adjacent groups.

How can I implement grouping?

Groups can be created manually, but sometimes they are created automatically, for example when you use the wizard in report builder or when you drop a field on the grouping pane. Groups are also structured in hierarchies. The hierarchies are defined by the relationships and can be horizontal or vertical. When you create groups, the rows that they contain become dynamic.

In a Tablix, there are also static rows. Static rows don't belong to a group. Static rows are used to display totals or labels. They will only display once. A dynamic row is a part of a group, and is usually rendered more than once. Textboxes in a dynamic row usually contain aggregated data. The detail row in a Tablix is the innermost row and it displays the detail data, which is not aggregated.

Note

More in-depth information about static and dynamic rows is available here:

https://msdn.microsoft.com/en-us/library/ee240753.aspx

Adding a parent-child group to a Tablix

When you add a group to a Tablix you can do it in two ways, you can use a parent-child hierarchy or an adjacent one.

A parent-child hierarchy can be compared to a tree structure. You organize your data in a tree structure so that you can expand and collapse it. The idea behind it is that you want to create a summary of your data according to a particular field. For example, if you create a list of items and add a group to the location, you can then create a summary of the inventory by location.

  1. To get started, create a new report with the following dataset:
    Adding a parent-child group to a Tablix
  2. Then, in the layout add a Tablix to the body, that contains the following fields:
    Adding a parent-child group to a Tablix

Let's have a look and see how we can create this type of grouping.

  1. To create a group, click on the Details arrow in the Row Groups window:
    Adding a parent-child group to a Tablix
  2. In the window that opens, select the field to group on, like this:
    Adding a parent-child group to a Tablix
  3. The option Add group header/footer adds an extra header and/or footer row to the Tablix. As you can see, when a group is added in a Tablix, it is made visible by the grouping line in the row handles.

    Tip

    Row and column handles

    Row and column handles become visible at the top and left side of a Tablix when you click on them. They are gray and you can click (or right-click) on them. The row handles also contain indicators to show the type of row.

  4. This vertical line shows where the group starts and ends and it resembles a big bracket:
    Adding a parent-child group to a Tablix
  5. Now you have two extra rows, a group header, and a group footer row. In the group footer row, below the quantity field on the detail row, select the quantity from the dropdown:
    Adding a parent-child group to a Tablix
  6. Now, after you have selected the quantity field in the group footer row, make sure you aggregate the numerical values on the footer (or header) row, as in the following example:
    Adding a parent-child group to a Tablix
  7. The row group's window now shows the group and the result is as follows:
    Adding a parent-child group to a Tablix

    Note

    If it is not already the case, I recommend changing the name of the group to a meaningful name. This is important when you are going to apply multiple nested groupings and makes it easier to see which group is which.

  8. We have now added a group to our Tablix and, when we run the report, it will resemble this:
    Adding a parent-child group to a Tablix
  9. Next, I will remove the footer row and put the total in the header. Most managers find it more interesting to start a report with the totals and then the details. To do this, you can copy past the field from the footer row to the corresponding textbox in the header row:
    Adding a parent-child group to a Tablix
  10. Then, select the footer row, right-click, and select Delete Rows:
    Adding a parent-child group to a Tablix

How do I implement expand/collapse?

To make it even more interesting, I will now add an expand/collapse option to the Tablix, so you can show or hide the details. To do this, you use the Hidden property.

  1. Every textbox has a Hidden property which you can either set to True or False, or to an expression. Instead of setting the Hidden property of each individual textbox in the details to False, I will do it via the row visibility. Right-click the detail row handle and select Row Visibility...:
    How do I implement expand/collapse?
  2. In the row visibility window that opens, select the option, Show or Hide, based upon an expression. I will use this as an expression: 
    =Last(Fields!HideDetails.Value, "DataSet_Result")

    This means that the visibility of the detail row now depends on what the user selects for the HideDetails option on the request page.

    Note

    The reason for the Last function is that the HideDetails field is added as the last row in the dataset designer.

  3. Then, select the Location Code field as the toggle item, as in the following screenshot:
    How do I implement expand/collapse?

    A toggle item is the name of a textbox on which you can click to change the Hidden property. So, by doing this, we enable the expand/collapse option on our group, allowing the user to show or hide details dynamically when running the report.

  4. Next, set the InitialToggleState property of the textbox that contains the LocationCode to: 
    =NOT Last(Fields!HideDetails.Value, "DataSet_Result")

This will make sure that, when the user selects HideDetails (or not), the + or – icon is correctly displayed. The InitialToggleState property of a textbox determines the initial state of the toggle image. When you run the report, it now looks as follows:

How do I implement expand/collapse?

In the request page, the user can select the HideDetails option to select the default value of the Hidden property for the detail row. At runtime the user can click on Location Code fields to expand or collapse the row and show or hide the details dynamically.

Note

An example of the finished report is available in the object: Packt - CH02-4. An example of the starter object for this report is available in the object: Packt - CH02-8.

Adding an adjacent group to a Tablix

Now, let's have a look at how we can use an adjacent grouping in a report. You can start by importing the object: Packt - CH02-9.

For this new report, I will use the following dataset:

Adding an adjacent group to a Tablix

To create the layout follow these steps:

  1. In the layout, you start by adding a Tablix and then, in the detail row, you add the Sales LCY and Profit LCY as fields:
    Adding an adjacent group to a Tablix
  2. Next, you add a parent group on [SalespersonCode_Customer], just as we did in the previous report:
    Adding an adjacent group to a Tablix
  3. Then you add a new group but, instead of selecting a parent-child group, you select Adjacent After...:
    Adding an adjacent group to a Tablix
  4. For the group expression, use the following expression: 
    =Year(Fields!PostingDate_CustLedgerEntry.Value)
  5. The result of the preceding expression is shown in this screenshot:
    Adding an adjacent group to a Tablix
  6. Then, rename the group to Year:
    Adding an adjacent group to a Tablix
  7. Now add a child group below the Year group, name it Month, and use the following expression: 
    =Month(Fields!PostingDate_CustLedgerEntry.Value)
  8. The result of the preceding expression is shown in this screenshot:
    Adding an adjacent group to a Tablix
  9. In the group fields, select the Sum aggregation for the Sales LCY and Profit LCY fields. You will now have a report layout that resembles this:
    Adding an adjacent group to a Tablix
  10. To add a row at the end of the report showing a total, go into the Row Groups and add a total, as in this screenshot:
    Adding an adjacent group to a Tablix
  11. Now, as in the previous report, use the row visibility and toggle properties to create an expand/collapse option for all groupings:
    Adding an adjacent group to a Tablix
  12. Then run the report. It will look like the following screenshot:
    Adding an adjacent group to a Tablix

In this report, we now have the details, first grouped by salesperson, and then below it by year and month. This gives the reader a different view of the same data, all in one Tablix. That's the power of using adjacent groupings.

Note

Adjacent groupings also have other advantages. You can use them to offer alternatives in document reports, for example. By combining them with group filters you can use one Tablix to show information from different data items, instead of using multiple tables, as in standard reports.

An example of the report with adjacent groupings is available in the object: Packt - CH02-5

上一章目录下一章