• (619) 483-4180
  • info@avantiico.com
Facebook-f Linkedin Instagram Youtube

Advanced SSRS Grouping Page Breaks Microsoft Dynamics AX 2012

Michael Oakes

Michael Oakes

The Microsoft Dynamics AX/D365 Support Team at Avantiico is focused on solving our client’s problems, from daily issues to large and more complex problems.

See All Posts

Advanced SSRS Grouping Page Breaks in Microsoft Dynamics AX 2012

When developing Microsoft Dynamics AX 2012 custom SSRS reports, developers may be required to display data within groups, for example, display budget control transactions by budget group.  In these scenarios, it may be required to display information within each group including a group header and subtotal. It may be required to display the group header for each new page, for example if the transactions for a group span multiple pages. It also may be required to allow users to select whether they want each new group to display on a new page.

This Post Will Cover the Following:

  • How to display an SSRS report with groupings and to display the grouping header and subtotal
  • How to print the group header for a new page, for examples if the number of transactions for a group span multiple pages
  • How to include an option to print a new page for each grouping

Advanced properties and code will be used to demonstrate the above. An included XPO project with a sample project for Microsoft Dynamics AX 2012 R3 will be included. A simple table control showing a pre-defined list of customer accounts with a subtotal and grouping by customer account.

The Following Describes How to Configure the Table to Include Groupings With the Sample SSRS Report Included:

> A sample report is included, as a starting point, a simple table control was added:

Sample SSRS Grouping in Dynamics 365 for Finance and Operarions
Figure 1

> On the report table, add a parent group:

Add parent group screenshot Dynamics 365 for Finance and Operation
Figure 2

> For this demonstration, we will include a header and footer to display the group headings and to print a sub total for the group:

Tablix Group screenshot Dynamics 365 for Finance and Operation
Figure 3

> The report table control should look like this:

Account row screenshot Dynamics 365 for finance and operation
Figure 4

> Copy the first column headings to the new row, and delete the first row:

Delete row Screenshot Dynamics 365 for finance and operation
Figure 5

> Click on advanced mode. This will allow us to print the groupings for each new page:

Account number in Dynamics 365 for finance and operation
Figure 6

> Set the groupings to print on each new page. If the number of transactions prints across multiple pages, the group heading will always print.

Tablix member in Dynamics 365
Figure 7

> The left column can be deleted as it is not needed. The report should look like this now:

Sample SSRS Grouping in Dynamics 365
Figure 8

> Formatting is added to the report, to add a grouping footer total for the amount column:

Sample SSRS Grouping in Dynamics 365
Figure 9

> Running the report, we can see the groupings and totals:

Sample SSRS Grouping in Dynamics 365
Figure 10

> The sample data in the report is increased so the data in the first group prints across multiple pages:

Sample SSRS Grouping in Dynamics 365
Figure 11

> To force new grouping to print on a new page. Click on the AccountNumGroup and right click and select Group Properties. Set the Page Break option:

Group properties in Dynamics 365
Figure 12

> Each new group will print on a new page:

Sample SSRS Grouping in Dynamics 365
Figure 13

> To control whether the groups print on a new page, a parameter was added to the report contract. Since Microsoft Dynamics AX 2012 SSRS reports do not allow expressions to be used to determine whether a page break occurs in a group, we must create 2 tables and hide the tables based on the new page parameter.

> To hide the Tablix, right click on the Tablix properties and set the hidden properties:

Sample SSRS Grouping in Dynamics 365
Figure 14

> Enter the following expression. This will be used to hide the table control if the ‘new page’ checkbox is not checked:

=iif(Parameters!ReportDS_NewPage.Value = 0, true, false)

> Duplicate the table control, disable the page breaks for the AccountNum group, and enter the following as the expression for the Tablix visibility:

=iif(Parameters!ReportDS_NewPage.Value <> 0, true, false)

> The end result should look like this. The new page checkbox when running the report will control which Tablix to print:

Sample SSRS Grouping in Dynamics 365
Figure 15

Try it yourself. Run the report included in this blog with and without the new page checkbox:

popup screenshot in dynamics 365
Figure 16
Facebook
Twitter
LinkedIn

Explore More Content:

Featured

Microsoft partner logo

Products

Resources

Our Company

Join Our Newsletter

Stay up to date on everything Microsoft. Look forward to thorough articles on all of the latest and greatest happening within Dynamics 365.

Subscribe Now
Linkedin Youtube Twitter Facebook Instagram

600 B Street, Suite 300, San Diego, CA 92101    |    (619) 483-4180    |    info@avantiico.com

Request a free Dynamics 365 meeting

Discover how Avantiico helps you improve business processes, provide customers with a seamless experience and transform the way you do business.