Step by step create SSRS Report in D365FO

Overview:

Here we will create SSRS Report in D365FO. I have given easy step to create SSRS Report. In SSRS Report you can design your Report easy way. Grouping, Formatting Text, Header design, Footer design, you can design as you wish in SSRS Report. There are multiple ways to create SSRS Report Dynamics Ax and D365. If you have Data provider class or else AOT Query or else Store procedure.

Before create SSRS Report you need to verify which object you need in your data provider class you have to check first those object belongs to which model and you have to add those models in your model. You can simply add by clicking on D365 Tab from Visual studio and select Update model then you can update model in your model.

Updating model parameters

If you must change the parameters for a model, you can use the Update model parameters dialog box.

1. On the Dynamics 365 menu, point to Model Management, and then click Update model parameters.
2. In the Model name field, select the model to update parameters for.
3. Update the parameters as you require.
4. Click Next.
5. Update the dependency information for the current model, if changes are required.
6. Click Next. The summary information for the model is displayed.
7. Click Finish.

Syncs Model if you got any database issue.

Update model if you forget to add model or check from Object in AOT from which model object you are using in your model.

Here I have used my custom table, class name for creating SSRS Report in D365FO.

Step-1: Create table and name it dev_GeneralJournalReportTmp. To create table right click on Solution explorer tab and over select option and select new item…

Popup will open in that select Data Model node and select Table and name it dev_GeneralJournalReportTmp.

To add Fields in the table right click on the Fields node and select data type which data type fields you want to add in the table. If you want to add relation with another table then you have to add that table extension in the current project. If you want to add EDT then select the EDT fields from the selected field’s property. It will might take time to add EDT in the selected fields.

Below fields I have created in table.

1. JournalName (String)
2. JournalNum(String)
3. AccountingDate(Date)
4. SubledgerVoucher(String)
5. MainAccountId(String)
6. Name(String)
7. Credit(Real)
8. Debit(Real)

Now change the table type property to Regular to TempDB.

Now next step is report parameters. For this purpose we use Data contract class. In current report we required parameters base on Extended Data Type LedgerJournalNameId, FromDate and ToDate. At simplest our data contract class will be look like as follow.

To Add Contract Class in the project right click on Solution explorer tab and over select option and select new item… on the Code table select class and name it dev_GeneralJournalReportContract.

Now create code for parameter like below.

[DataMemberAttribute]
class dev_GeneralJournalReportContract
{
    LedgerJournalNameId LedgerJournalNameId;
    TransDate           FromDate, ToDate;

    [DataMemberAttribute('From date')]
    public TransDate ParmFromDate(FromDate _FromDate=FromDate)
    {
        FromDate = _FromDate;
        return FromDate;
    }

    [DataMemberAttribute('To date')]
    public TransDate ParmToDate(ToDate _ToDate=ToDate)
    {
        ToDate = _ToDate;
        return ToDate;
    }

    [DataMemberAttribute('Journal Name')]
    public LedgerJournalNameId ParmLedgerJournalNameId(LedgerJournalNameId _LedgerJournalNameId=LedgerJournalNameId)
    {
        LedgerJournalNameId = _LedgerJournalNameId;
        return LedgerJournalNameId;
    }
}

Now we required to write some logic which you above mention class as its data contract and populate the custom temp table we build in pervious step.

Now create another class like above and name it dev_GeneralJournalReportDP. Create methods and your login to get data like below.

[SrsReportParameterAttribute(classstr(dev_GeneralJournalReportContract))]
class dev_GeneralJournalReportDP extends SRSReportDataProviderBase
{
    dev_GeneralJournalReportTmp   dev_GeneralJournalReportTmp;
    dev_GeneralJournalReportContract  Contract;

    public void ProcessReport()
    {
        LedgerEntryJournal                  LedgerEntryJournal;
        GeneralJournalEntry                 GeneralJournalEntry;
        GeneralJournalAccountEntry          GeneralJournalAccountEntry;
        LedgerJournalTrans                  LedgerJournalTrans;
        LedgerJournalTable                  LedgerJournalTable;
        DimensionAttributeValueCombination  DimensionAttributeValueCombination;
        MainAccount                         MainAccount;
        LedgerJournalName                   LedgerJournalName;
        TransDate                           _FromDate,_ToDate;
        LedgerJournalNameId                 _LedgerJournalNameId;

        Contract = this.parmDataContract();
        _FromDate = Contract.ParmFromDate();
        _ToDate = Contract.ParmToDate();
        _LedgerJournalNameId = Contract.ParmLedgerJournalNameId();

        while select * from GeneralJournalAccountEntry
        order by GeneralJournalEntry.SubledgerVoucher asc
        join GeneralJournalEntry
        where GeneralJournalEntry.RecId == GeneralJournalAccountEntry.GeneralJournalEntry
        && (!_FromDate || GeneralJournalEntry.AccountingDate >= _FromDate)
        && (!_ToDate || GeneralJournalEntry.AccountingDate <= _ToDate)
        join DimensionAttributeValueCombination
        where DimensionAttributeValueCombination.RecId == GeneralJournalAccountEntry.LedgerDimension
        join MainAccount
        where MainAccount.RecId == GeneralJournalAccountEntry.MainAccount
        join LedgerEntryJournal
        where LedgerEntryJournal.RecId == GeneralJournalEntry.LedgerEntryJournal
        join LedgerJournalTable
        where LedgerJournalTable.JournalNum == LedgerEntryJournal.JournalNumber
        join LedgerJournalName
        where LedgerJournalName.JournalName == LedgerJournalTable.JournalName
        && (!_LedgerJournalNameId || LedgerJournalName.JournalName == _LedgerJournalNameId)
        {
            dev_GeneralJournalReportTmp.JournalName = LedgerJournalName.JournalName;
            dev_GeneralJournalReportTmp.JournalNum = LedgerJournalTable.JournalNum;
            dev_GeneralJournalReportTmp.AccountingDate = GeneralJournalEntry.AccountingDate;
            dev_GeneralJournalReportTmp.SubledgerVoucher = GeneralJournalEntry.SubledgerVoucher;
            dev_GeneralJournalReportTmp.MainAccountId = MainAccount.MainAccountId;
            dev_GeneralJournalReportTmp.Name = MainAccount.Name;
            if(GeneralJournalAccountEntry.TransactionCurrencyAmount < 0)
            {
                dev_GeneralJournalReportTmp.Credit = GeneralJournalAccountEntry.TransactionCurrencyAmount;
            }
            if(GeneralJournalAccountEntry.TransactionCurrencyAmount > 0)
            {
                dev_GeneralJournalReportTmp.Debit = GeneralJournalAccountEntry.TransactionCurrencyAmount;
            }
            dev_GeneralJournalReportTmp.insert();
        }
    }

    [SrsReportDataSetAttribute('dev_GeneralJournalReportTmp')]
    public dev_GeneralJournalReportTmp GetData()
    {
        select dev_GeneralJournalReportTmp;
        return dev_GeneralJournalReportTmp;
    }
}

At this step build the solution, never forget to check the project Synchronized database on build set to true. So when you build your visual studio project then it will automatically synchronized with data base. You do not need to synchronize every time.

Right click Solution explorer and select property Synchronized Database on Build to True. Now add new report in project.

Right click on Solution explorer tab and over select option and select new item… on that select Reports Node and Select report and name it dev_GeneralJournalReport.

Double click on Report in solution and open in designer screen. And right click on datasets and create new Data Set.

From new data set update the following properties. Data Source Type to Report Data provider. Change Name to dev_GeneralJournalReportDP.

Click on Query and from dialog select the data provider class we have created before. Click on next and select which fields you want it to get in the report dataset. After click OK.

After that you can see all fields which you had selected in query. After that expand the parameter node from the report and set the prompt string which will display in the report label.

Now right click on designer node of report and click on precision design. In precision design, we can create report designer by our need. New designer will be added, rename it and double click to open it. From left pane, toolbar to drag to table to add in designer.

When all fields are added in report. Right click outside the report body and click on report property.
And select report print layout. Our target is A4 page. So set it from report properties.

Now right click on report body and check it’s with. Its width must be under 6 because at run time 1 inch on both side must be skip.

After that right click on report in solution explorer and click on deploy. After the add new display menu Item update the it’s following properties.

Here there is one advancement, in 2012 there is very difficult to debug report. But in Dynamics 365 for operation is its very simple. Just put break point and set report as starting object and run the project.

Select parameter. Click on ok and my break open hit. So it is very easy to debug report. So our report logic fine, but one step is pending which is report should be run from Client with particular module.

So I decide to add this report in General journal module and under Inquery report . For this purpose we have to create a new menu extension General ledger Menu.

New menu extension added in solution explorer. Expand menu extension in designer and drag display menu items in required menu. From property update Display In Content Area to True to create show crumb bar. Now save and compile again the project. And open client url in Browser.

Login and goes in Sales module for identification purpose, I set the label of display menu as General Journal report.