(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 (queryRun.next())
       {
           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.