Reporting Based on SSRS

There no better way of getting to grips with the Dynamics AX framework than learning by example. This cookbook is packed with recipes for creating and managing reports along with full explanations for complete understanding.

(For more resources related to this topic, see here.)

Creating a matrix report

A matrix is an interesting representation format with a two-dimensional view of the data, allowing capabilities to consolidate by row and column. This recipe will discuss how to add and use a matrix data region in reports. Also discussed is the totaling capabilities in matrix reports.

How to do it…

This recipe is broken down into two sections. In the basic report design section we will build a simple RDP that will be used in this recipe as well as in other recipes found in this article and the actual recipe is in the second section.

Basic report design

  1. Before we start on this recipe we will build an RDP class that can be used as a source for all the recipes in this article. This RDP will be used as the source of a dataset for all the following reports.
  2. Create a query as seen here. The InventItemGroupItem table uses the exists join and is added for the purpose of limiting the sales lines data to certain item groups.
  3. The goal of the RDP is to run across the sales line in the system and then retrieve the item, item group, and the shipping date confirmed. The shipping date confirmed is also split into multiple parts such as years, months, and days.

    This RDP can be time consuming if you have a huge database of sales orders, so limit your data to certain item groups or a certain period as required in the RDP's processReport method.

  4. This RDP will fill a temporary table shown here:

  5. The RDP shown here will fill the temporary table by running through all the sales lines in the system.

    The general approach for analysis like this is to use OLAP so that it is faster and provides multiple dimensions of consolidations but using an OLAP to demonstrate these report controls might stop several from practicing. This is because the majority of AX developers are not BI experts. Keeping this in mind this RDP has been used to demonstrate the examples.

    [ //bind query - shows in the report dialog SRSReportQueryAttribute(queryStr(PktSalesLine)) ] class PktItemSalesHistoryDP extends SRSReportDataProviderBase { PktItemSalesHistoryTmp salesHistoryTmp; } [ SRSReportDataSetAttribute(tableStr(PktItemSalesHistoryTmp)) ] public PktItemSalesHistoryTmp getItemSalesHistoryTmp() { select salesHistoryTmp; return salesHistoryTmp; } private void insertTmpTable(SalesLine _salesLine) { Qty qty; date shipDate; InventItemGroupItem groupItem; qty = _salesLine.QtyOrdered; shipDate = _salesLine.ShippingDateConfirmed; groupItem = InventItemGroupItem::findByItemIdLegalEntity( _salesLine.ItemId, _salesLine.DataAreaId); salesHistoryTmp.clear(); salesHistoryTmp.ItemId = _salesLine.ItemId; salesHistoryTmp.ItemGroupId = groupItem.ItemGroupId; salesHistoryTmp.Price = _salesLine.salesPrice; salesHistoryTmp.Amount = _salesLine.SalesPrice * Qty; salesHistoryTmp.Qty = qty; salesHistoryTmp.Year = year(shipDate); salesHistoryTmp.MonthOfYearId = mthOfYr(shipDate); salesHistoryTmp.Days = dayOfMth(shipDate); salesHistoryTmp.insert(); } [ SysEntryPointAttribute(false) ] public void processReport() { Query query; QueryRun queryRun; SalesLine salesLine; InventItemGroupItem itemGroup; query = this.parmQuery(); queryRun = new queryRun(query); while ( { salesLine = queryRun.get(tableNum(salesLine)); this.insertTmpTable(salesLine); } }

Creating a matrix report

  1. Create a report in Visual Studio named PktMatrixReport and add the RDP provider as a dataset.
  2. Set the Dynamic filter property to false.
  3. Create a new precision design and name it matrixDesign, and then double-click to open up the editor.
  4. Right-click and insert a new matrix data region:

  5. Use the field selector to set the fields as shown in the following screenshot. Choosing Qty in the data section will automatically add the Sum function to it:

  6. To add the total quantity for each row, on the Row Groups option at the bottom, click on the small arrow and then navigate to Add Totals | After.
  7. To add the total quantity for each column, on the Column Groups option at the bottom, click on the small arrow and then navigate to Add Totals | After.
  8. There are three total boxes in the design. Each represent the row total, column total, and the grand total. Grand total tallies the sum of rows and sum of columns.
  9. As you can see in the following screenshot that the grand total block in the title row is colored and the font is also set to bold. This will give a better appearance for the matrix:

  10. The header for the row is present but there is no similar header for the column group. So right-click on the first column and navigate to Insert Row | Outside Group – Above. This will give a header for the column. Enter the expression string ItemGroupId using the label ID or the static text. The design appears as shown here:

  11. Save the report and preview it:

How it works…

A matrix data region is actually a Tablix control behind the hood. The Tablix control combines the behavior of table, list, and matrix reports. Though the UI has table, matrix, and list controls, they are the same controls under the hood; they open up with a different configuration. The matrix data region has both row and column group, whereas a table control has only column group. Matrix helps create summary type reports.

Creating a multicolumn matrix report

This recipe will show how a multicolumn matrix can be implemented. Here we will expand our report to see how we can dissect it into detail where the rows are further split by months following years and the columns will also show the average price other than the quantity.

Getting ready

This recipe requires that you complete the recipe Creating a matrix report in this article.

How to do it…

  1. Create a new design as in the previous recipe or extend the design created in the previous recipe with the steps detailed here.
  2. From the Report Data toolbar, drag the control MonthOfYearId1 to the Row Group control next to Year. When you drop it a blue bar appears and it must face the Year column as shown here:

  3. From the Report Data toolbar drag the control Price to Column Group next to Qty. A vertical blue bar appears and it must face the Qty column as seen here:

  4. The price field, by default, gets placed in as a Sum operation. Change it to Average by right-clicking on the cell and choosing Expression. In the expression field modify the sum and set it to average.
  5. Since price can have decimal values it is important to set the decimal ranges; otherwise, it ends up in an improper formatting. Right-click on price control and choose Textbox properties .
  6. On Textbox properties set the Formatting to Number and ensure Decimal places is set to 2:

  7. If you are extending the previous recipe drop the column totals before doing this; otherwise, directly move to the column group at the bottom and click on the arrow button and then navigate to Add Totals | After. This will add the totals for the quantity and price fields.
  8. The report design appears as shown here. Save the report and preview it.

  9. The first screenshot shows preview with limited data (the Load dataset fully option is not activated):

  10. The second screenshot shows the preview with the full dataset loaded:

How it works…

Here in this report, the summary is broken down by years and months while the columns summarize multiple values than just one value. In this way, a matrix can be used to build detail-drilled summary reports.

The report preview option in Visual Studio always loads a limited sampled data and doesn't bring the entire data behind the system. This is not realized in the case of small datasets. When using big datasets, as in the case in this article, you will notice that data changes every time. In this case, to confirm if everything is loaded, activate the Load data set fully option available in the right corner of your preview window.

Creating a column chart report

This recipe will guide you to create chart-based reports in Dynamics AX. Charts are interesting pictorial representations of data and the SSRS reports support a multitude of chart types. It is v to switch between chart types in SSRS. In this recipe we will create a column chart that represents the total sale of quantity over a couple of years.

Getting ready

Complete the RDP defined in the Getting ready section of the recipe Creating a matrix report in this article.

How to do it…

  1. Since the RDP class can return a large number of item groups, it is ideal to limit it to two item groups to test this recipe. This will make it faster and easier to work through this recipe.
  2. Modify processreport in the RDP to add ranges for the item group in the query.
  3. In Visual Studio create a new report PktColumnChartReport and link the RDP class to it. Remember to set the Dynamic filter property in the dataset to false.
  4. Create a new precision design and name it ChartDesign.
  5. Open the editor, right-click and then navigate to Insert | Chart. In the prompting dialog window that shows the different chart types available, choose the Column chart:

  6. Resize the chart to a required size and double-click on the chart area. This will show additional square boxes around the chart image for category, series, and data as seen in the following screenshot. Drag the following fields to the specified region accordingly:


    Drop area







  7. As fields are dropped in the chart area you can see the report changing at design time. Though this may not reflect the exact data, it will give you a feeling of how it looks at runtime.
  8. Modify the chart title and the axis title. Save the report design.

  9. The report preview appears as seen here:

How it works…

The report seen here is a multi-series chart, which means the category is applied across the series. A static series is where we represent a single series item; for example, item group. In this case if the item group is not restricted, then each year would be showing up for all the item groups in the system. Care has to be taken in what is added to the series so that the comparison makes sense to the user.

Creating a line chart

This recipe will discuss another chart-based report. Here we will try to show the monthly quantity trend for the item groups over the years. The line chart is the best option to reveal trends. This recipe will also focus on the aesthetical properties of charts, such as color and axis design.

Getting ready

This recipe requires the Creating a column chart report recipe in this article to be completed beforehand.

How to do it…

  1. Create a new precision design for the report. Call it line design.
  2. Open the editor and insert a new chart.
  3. Double-click on the chart area to open up the field editor. On the field editor drop the following fields as specified:


    Drop area

    Year, MonthOfYearId






  4. The next few steps will deal with how the aesthetics of a chart can be made better. On the General Properties window, set Palette to SeaGreen. This will apply a different set of colors for each series.
  5. To set up markers for the series on the value points, right-click on the chart area and select Series Properties. On the Markers tab set the type of marker as Square.

  6. The axis may not show all the labels for the category axis. To make sure that it shows all the months as labels right-click on an axis and open the Axis properties form. In the property form on the Axis options tab under Set axis scale and style, set the interval to 1. This will ensure that all the labels are visible.
  7. Right-click on the Legend option and choose Legend properties. On the General tab under the Legend position choose from the circular radio buttons the position of the legend:

  8. To disable the quantity axis right-click on any axis, uncheck Show Value Axis, and then the design should be similar in style to the screenshot here:

  9. Save the report and preview it.

How it works…

This recipe details how the appearance of a report can be controlled through the various associated properties. This recipe discusses only limited options while there are tons of more options that you can explore.

There's more…

Though the chart design shown here is from precision design, auto design also supports charts.

Chart reports in auto design

In auto design the category, series, and data appear as group nodes as seen here. The chart type can be changed from the Properties node of the chart control. The fields can be dragged into it to from the datasets.

Precision design is more flexible and convenient than auto design for the following reasons:

  • Modify and preview the chart in design time
  • Clarity to understand the field and its corresponding axis
  • Extent of customization and visibility of the available charts
  • More control on where and how the data is rendered, such as the marker and legend

    Prefer using precision design over auto design when it comes to charts.


This article dived in to using the other types of report formats, such as chart, lines and matrix reports.

Resources for Article:

Further resources on this subject:

Books to Consider

comments powered by Disqus

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free