Matrix-based SSRS Data Provider Report Development in Dynamics 365 Finance and Operations

A Matrix-based SSRS report is particularly useful when displaying data in a pivot-table format, where rows and columns dynamically expand based on the dataset. This article provides a step-by-step guide to creating a Matrix-based SSRS report in D365FO.

Understanding of Matrix Reports

Matrix reports in SSRS allow data to be grouped both vertically and horizontally. Unlike a tabular report that has static columns, a matrix dynamically generates columns based on data values. This is particularly useful when reporting financial data, sales performance, or inventory movements where period-based comparisons are necessary.

Steps to Create a Matrix-Based SSRS Report in D365FO

• In D365FO, SSRS reports typically use either a Query or a Data Provider (RDP).
• For complex calculations and dynamic data structures, an RDP class is often preferred.
• Develop an RDP class and an RDP contract class to define the report parameters.

Scenario

Suppose a business or client requests a report to view item sales by warehouse.

The report will include two parameters: From Date and To Date. Based on the selected date range, the report will generate item-wise sales data for all warehouses. Since the number of warehouses and items is dynamic, static columns cannot be used. This is where a matrix report provides an effective solution.

Development Process

1) Contract class

[DataContract]
public class AA_MonthlySalesContract
{
    TransDate fromDate, toDate;

    [DataMemberAttribute("From Date")]
    public TransDate parmFromDate(TransDate _fromDate = fromDate)
    {
        fromDate = _fromDate;
        return fromDate;
    }

    [DataMemberAttribute("To Date")]
    public TransDate parmToDate(TransDate _toDate = toDate)
    {
        toDate = _toDate;
        return toDate;
    }

}        

2) Data Provide (DP) Class

[SrsReportParameterAttribute(classStr(AA_MonthlySalesContract))]
public class AA_MonthlySalesDP extends SrsReportDataProviderPreProcessTempDB
{
    AA_MonthlySalesTmp      tmpTable;
    AA_MonthlySalesContract contract;
    TransDate               fromDate,toDate;
    CustInvoiceJour         invoiceJour;
    CustInvoiceTrans        invoiceTrans;
    InventTableExpanded     itemNameView;

    [SrsReportDataSetAttribute(tablestr(AA_MonthlySalesTmp))]
    public AA_MonthlySalesTmp getTmpTable()
    {
        select tmpTable;
        return tmpTable;
    }

    public void processReport()
    {
        contract = this.parmDataContract() as  AA_MonthlySalesContract;
        fromDate =    contract.parmFromDate();
        toDate =       contract.parmToDate();

        tmpTable.clear();
        insert_recordset tmpTable(InventLocationId, TransDate, ItemId, Qty, Amount)
            select inventLocationId, InvoiceDate from invoiceJour
            group by invoiceJour.inventLocationId, invoiceJour.InvoiceDate,  invoiceTrans.ItemId, invoiceTrans.Qty, invoiceTrans.LineAmount
            join ItemId, sum(Qty), sum(LineAmount) from invoiceTrans
            where  invoiceTrans.SalesId ==  invoiceJour.SalesId
            && invoiceTrans.InvoiceId ==     invoiceJour.InvoiceId
            && invoiceTrans.InvoiceDate == invoiceJour.InvoiceDate
            && invoiceTrans.numberSequenceGroup == invoiceJour.numberSequenceGroup
            && invoiceJour.InvoiceDate >= fromDate
            && invoiceJour.InvoiceDate <= toDate
            && invoiceJour.inventLocationId != '';

        update_recordset tmpTable
            setting ItemName = itemNameView.ProductName
            join itemNameView where itemNameView.ItemId ==  tmpTable.ItemId;

    }

}        

3) Temporary (Tmp Table)

4) Report Design

a) Configure the report dataset properties as shown in the figure below. If you encounter an error during this setup, first set the query and then change the Default Layout to Matrix at the end.

b) Report designer

In the report designer, choose Matrix instead of Tablix control

c) Month group property

Expression value in the group is =MonthName(Month(Fields!TransDate.Value))& "-"&Year(Fields!TransDate.Value)

d) Item Group property.

e) Warehouse group / Column group property

5) Output menu item

6) Attach the menu item on the menu extension

7) Build sync. your project and deploy the report.

Output

This is the end of this blog.

Happy Learning,

Syed Amir Ali.