/ Microsoft / Create SSRS report in D365FO

Create SSRS report in D365FO

Learn, share and spread knowledge

Microsoft Dynamics 365 for Finance and Operations and Microsoft Dynamics AX 2012 uses the SSRS reports for the business reporting purposes. It is a very important and significant feature. It helps the right users to get the right information about the business processes and transactions. In this tutorial, we will create SSRS report in D365FO using DP, Controller, and Contract class.

I have created two temporary tables for the report as follows:

  • PurchaseOrderHeaderTmp
  • PurchaseOrderLinesTmp

Set the Table Type property of table to TempDB.

Drag and drop fields which you want to show on the report, from PurchTable to your temporary header table i.e. PurchaseOrderHeaderTmp and from PurchLine to your temporary line table i.e. PurchaseOrderLinesTmp respectively. Alternatively, you can also create new fields by right-clicking the fields node -> new -> selecting data type. But, this is slow process as you have to set the properties for each field as well.

After creating tables and adding the fields, Build and Synchronize your project.

See the temporary tables in the pictures below:

PurchaseOrderHeaderTmp and PurchaseOrderLinesTmp (L-R)

Add three classes in your project as follows:

  • PurchaseOrderDP
  • PurchaseOrderController
  • PurchaseOrderContract

Add the following code in the respective classes:

Contract class:

class PurchaseOrderContract
{
    PurchId purchId;
    [DataMemberAttribute('Purchase Id')]
    public PurchId parmPurchId(PurchId _purchId = PurchId)
    {
        purchId = _purchId;
        return purchId;
    }
}

DP class:

[SRSReportParameterAttribute(classstr(PurchaseOrderContract))]
class PurchaseOrderDP extends SRSReportDataProviderBase
{
    PurchaseOrderHeaderTmp  purchaseOrderHeaderTmp;
    PurchaseOrderLinesTmp   purchaseOrderLinesTmp;

    [SRSReportDataSetAttribute(tableStr(PurchaseOrderHeaderTmp))]
    public PurchaseOrderHeaderTmp getPurchaseOrderHeaderTmp()
    {
        select * from purchaseOrderHeaderTmp;
        return purchaseOrderHeaderTmp;
    }

    [SRSReportDataSetAttribute(tableStr(PurchaseOrderLinesTmp))]
    public PurchaseOrderLinesTmp getPurchaseOrderLinesTmp()
    {
        select * from purchaseOrderLinesTmp;
        return purchaseOrderLinesTmp;
    }

    public void processReport()
    {
        PurchTable  purchTable;
        PurchLine   purchLine;
        PurchaseOrderContract contract;
        PurchId     purchId;
       
        contract = this.parmDataContract() as PurchaseOrderContract;
        purchId = contract.parmPurchId();

        select * from purchTable 
            where purchTable.PurchId == purchId;

        purchaseOrderHeaderTmp.PurchId = purchTable.PurchId;
        purchaseOrderHeaderTmp.InvoiceAccount = purchTable.InvoiceAccount;
        purchaseOrderHeaderTmp.OrderAccount = purchTable.OrderAccount;
        purchaseOrderHeaderTmp.PurchName = purchTable.PurchName;
        purchaseOrderHeaderTmp.InclTax = purchTable.InclTax;
        purchaseOrderHeaderTmp.PaymMode = purchTable.PaymMode;
        purchaseOrderHeaderTmp.PurchStatus = purchTable.PurchStatus;
        purchaseOrderHeaderTmp.CurrencyCode = purchTable.CurrencyCode;
        purchaseOrderHeaderTmp.DeliveryDate = purchTable.DeliveryDate;
        purchaseOrderHeaderTmp.insert();

        while select * from purchLine
            where purchLine.PurchId == purchTable.PurchId
        {
            purchaseOrderLinesTmp.LineNumber = purchLine.LineNumber;
            purchaseOrderLinesTmp.ItemId = purchLine.ItemId;            
            purchaseOrderLinesTmp.PurchQty = purchLine.PurchQty;
            purchaseOrderLinesTmp.PurchPrice = purchLine.PurchPrice;
            purchaseOrderLinesTmp.PurchUnit = purchLine.PurchUnit;
            purchaseOrderLinesTmp.PriceUnit = purchLine.PriceUnit;
            purchaseOrderLinesTmp.LineAmount = purchLine.LineAmount;            
            purchaseOrderLinesTmp.VendAccount = purchLine.VendAccount;
            purchaseOrderLinesTmp.VendGroup = purchLine.VendGroup;
            purchaseOrderLinesTmp.insert();
        }
    }
}

Controller class:

class PurchaseOrderController extends SrsReportRunController
{
    public static void main(Args _args)
    {
       PurchaseOrderController controller = new PurchaseOrderController();     
       controller.parmReportName(ssrsReportStr(PurchaseOrderReport,Report));
       controller.parmArgs(_args);
       controller.parmShowDialog(false);
       controller.startOperation();
    }
}

Add a report in your project named as PurchaseOrderReport. Add two data sets in your report named as PurchaseOrderHeader and PurchaseOrderLines.

Set properties of data sets as follows:

Data Source Type: Report Data Provider

Set query property by clicking on the three dots in the properties window. A new window will open and select PurchaseOrderDP. Click next and then select fields. Select PurchaseOrderHeaderTmp for PurchaseOrderHeader data set and PurchaseOrderLinesTmp for PurchaseOrderLines respectively.

Add the following Reference Models:

  • ApplicationFoundation
  • ApplicationPlatform
  • ApplicationSuite
  • ContactPerson
  • Directory
  • FiscalBooks
  • SourceDocumentation
  • SourceDocumentationTypes

Create an output menu item. Set the properties of output menu item as follows:

  • Object: PurchaseOrderController
  • Object Type: Class
  • Subscriber access level: Unset
  • Needs Record: Yes

Create an extension of form where you want to show the output menu item. In the form, create Menu Item Button and set the properties as follows:

  • Data Source: PurchTable
  • Needs Record: Yes
  • Menu Item Name: Name of OutputMenuItem
  • Menu Item Type: Output

In this way, we can create SSRS report in D365FO. Also see how to Customize SSRS report using extension in D365FO.

If you found any ambiguity or a better solution, please feel free to ask.

Blog: Click here

YouTube: Click here

GitHub: Click here

Ability is what you’re capable of doing. Motivation determines what you do. Attitude determines how well you do it. – Lou Holtz

0 POST COMMENT

Send Us A Message Here

Your email address will not be published. Required fields are marked *

NevoiTech Blog

A blog by Moeen Ahmed SultanNevoiTech logo

To hire freelancer or get personal training

of

Microsoft Dynamics 365 for Finance & Operations

Call Us: +92 321 458 9595