How To Create A D365 SSRS Report

In this article, learn how to create a basic D365 SSRS reports. “SSRS” stands for SQL Server Reporting Services. Reports are defined by Microsoft as any visualization of a structured data set. Learn how to display data retrieved from within Microsoft Dynamics 365 for Finance and Operations. Importantly, SSRS reports can be created to show data that exists in a SQL Server database. However, in this article, I will explain how to create an SSRS report specifically for D365.

Example SSRS Reports

A D365 SSRS report is run by clicking on a Menu Item within Microsoft Dynamics 365 for Finance and Operations. Afterwards, the report displays data to the user.

Open Transactions Report

To see an example report, in D365 go to Accounts receivables>Inquires and reports>Open transactions report. A dialog opens allowing the user to set parameters that control what data is shown.

When the dialog opens, click the ‘Ok’ button.

After some time, the finished report is shown.

Notice, the data shown in a report can be displayed in a variety of different ways. Most commonly, it can be shown in one or more tables or grids. Often shown with repeating headers, and summed up totals.

Sales Invoice Report

Conversely, D365 reports are used to generate picking, packing and invoice reports. Go to Accounts receivables>Orders>All sales orders. Notice, the data displayed is laid out in specific places on the page.

Reports Vs Forms

Generally speaking, forms and D365 SSRS reports are similar. They both show data. So when should you use each?

Before moving on, I will explain what you should consider when deciding which one to use.

Forms should be used when:

• Data needs to be created, modified or deleted.
• The displayed data needs to be quickly filtered or sorted.
• The user wished to dynamically add or remove columns using Personalization.
• The user plans to take immediate action on the data.

D365 SSRS Reports have the following limitations compared to a Form.

• The data only needs to be read. Reports are read-only.
• Parameters specified up front allow the data shown on a report to be filtered and even sorted. But changing these values, requires the report to be re-generated.
• The columns on a report cannot be dynamically added or removed using Personalization.
• Unlike a form, a user cannot select a record in a report, and push a button to take action on that data.

Despite these limitations, D365 SSRS reports have some great benefits.

• Reports load and display all of the data queried. Whereas, forms typically show just what is viewable within the browser. When the user scrolls down, forms will need to go back to the database to retrieve and show the next set of records.
• In a report, the layout of the data on the page can be precisely specified. This is crucial when printing data on paper with print already on it. Such as a 1099 tax form template. The data shown on a form will move based on the dimensions of your browser window.
• Reports offer multiple options to print the report to a printer. Or, export the data to a file.

D365 SSRS Report Architecture

Now that you understand what a D365 SSRS report is, and when to use one, learn how to create a basic report using Visual Studio.

Before walking through the steps in Visual Studio, it helps to understand the overall pieces required.

Functionally, when you think of a report, there are three components.

Behind the scenes in Visual Studio, we actually need to perform eight steps to accomplish this.

In case it helps, creating a D365 SSRS report is similar to creating a SysOperation Framework batch job. The Contract and Controller classes are needed in both. However, a report needs a Data Provider class and a Report as well.

Let’s get started. In this example, create a report that shows all customer groups that use a specified payment term.

Create A D365 Project

Before we even begin, as always, open Visual Studio and create a new D365 Finance Operations Project.

If you need a reminder, follow this article for the detailed steps.

For this example, I named my project TutorialSSRSReports.

Contract Class

First, we need to create a Contract class. To explain, the contract class contains any data we need to send to the system to help it generate the report. Most commonly this class is used to cause the Dialog to show and pass parameters to the report.

To start, right click on your project in Visual Studio. Then, select Add>New Item.

Next, select Class from the list. Enter the name tutorialSSRSReportContract. It is a best practice to end the name with the word ‘Contract‘. Replace the beginning part with what makes sense for your report. Then, click ‘Add‘.

Enter the following code into the code editor window.

[DataContractAttribute]
class tutorialSSRSReportContract
{
    PaymTermId paymTermId;

    [DataMemberAttribute('PaymTermId')]
    public PaymTermId parmPaymTermId(PaymTermId _paymTermId = paymTermId)
    {
        paymTermId = _paymTermId;
        return paymTermId;
    }

}

In this example, I have specified a member variable named ‘paymTermId‘. And a method named ‘parmPaymTermID‘. If the report you are creating does not take any parameters, leave the inside of this class blank. If your class does take parameters, replace these methods with the parameters your report needs.

Data Provider Class

Second, create a Data Provider class to retrieve the data to be shown on the D365 SSRS report.

Note, there several ways of retrieving the data used by a report. You can use Query objects as well as other ways. For this example, we will write an x++ query to retrieve the data and store it in a temporary table. This is a very common approach that allows for x++ methods to be run to retrieve the data.

To start, right click on your project in Visual Studio. Then, select Add>New Item.

Next, select Class from the list. Enter the name tutorialSSRSReportDP. It is a best practice to end the name with the word ‘DP‘. DP is short for Data Provider. Replace the beginning part with what makes sense for your report. Then, click ‘Add‘.

Then, enter the following code into the code editor window.

[SRSReportParameterAttribute(classStr(tutorialSSRSReportContract))]
class tutorialSSRSReportDP extends SRSReportDataProviderBase

{
    tutorialSSRSReportTmp reportTmp;

    [SRSReportDataSetAttribute(tableStr(tutorialSSRSReportTmp))]
    public tutorialSSRSReportTmp getTmp()
    {
        select reportTmp;
        return reportTmp;
    }

    public void processReport()
    {
        tutorialSSRSReportContract contract =
            this.parmDataContract() as tutorialSSRSReportContract;
        
        CustGroup     custGroup;
        PaymTermId    paymTermId = contract.parmPaymTermId();
        
        while select custGroup
            where custGroup.PaymTermId == paymTermId
        {
            reportTmp.clear();
            reportTmp.CustGroup         = custGroup.CustGroup;
            reportTmp.Name              = custGroup.Name;
            reportTmp.PaymTermId        = custGroup.PaymTermId;
            reportTmp.ClearingPeriod    = custGroup.ClearingPeriod;
            reportTmp.insert();
        }
    }
}

Query The Data

Notice, there are several pieces of code that we need to pay attention to and change.

In the first line of code, inside classStr(), specify the name of the Contract class you created in the first step. In this example, type tutorialSSRSReportContract. Also, replace the name of the contract class in the first line of the processReport method.

Next, everywhere the text has “tutorialSSRSReportDP“, replace this with the name of your data provider class.

Then, inside the processReport method, notice I have written a ‘while select‘ loop that retrieves data from the CustGroup table. Also notice that the while select loop has a ‘where‘ clause that filters the results based on the value stored in the contract class. (where custGroup.PaymTermId == paymTermId).

Change this ‘while select‘ loop to query the data that should be shown in your report. Writing this query is likely where most of your effort will be spent.

Finally, notice that the data retrieved from the CustGroup table is inserted into a temporary table named ‘tutorialSSRSReportTmp‘. This temporary table has not been created yet. We will do this in the next step. But I find it helpful to write the query first, so that you understand what fields you need to create in the temp table.

Why Use A Temporary Table?

The purpose of the temp table, rather than just using CustGroup by itself, is that a temp table allows you to store data from multiple tables. Consider that we could have joined CustGroup to other tables and retrieves fields from those related tables.

Additionally, you can call methods on the CustGroup table, or any joined table to retrieve data that may not be explicitly stored in a field. While this can dramatically slow down the performance of this query, it is still a very powerful tool to help show the data you want on the report.

Create The Temporary Table

Thirdly, we need to create the temporary table used by the data provider class. This temporary table does not store the data permanently. But instead, stores the data in memory just for use on this instance of running the D365 SSRS Report.

To start, right click on your project in Visual Studio. Then, select Add>New Item.

Next, select Table from the list. Enter the name tutorialSSRSReportTmp. It is a best practice to end the name with the word ‘Tmp‘. Tmp is short for Temporary. Replace the beginning part with what makes sense for your Report. Finally, click the ‘Add‘ button.

Add Fields To Temporary Table

After creating the table, right click on the table and select Open. The designer will open.

Right click the table, and select Properties. In the Properties window set the TableType property to ‘TempDB‘.

Finally, add a field to the table for each piece of data you wish to show on the report. Importantly, make sure that each field uses the same Extended Data Type property as the field on the table you are retrieving the data from. This will help ensure any String fields store enough characters, and data is not cut off.

In this example, add the following String fields: CustGroup, Name, PaymTermID, and ClearingPeriod.

After you create the temporary table, if you have not already, go back and update your Data Provider class.

Create The Report

Fourthly, now that you have the data you want to show in the D365 SSRS Report, it is time to design how to show it.

To start, right click on your project in Visual Studio. Then, select Add>New Item.

Next, select Report from the list. Enter the name tutorialSSRSReport. It is helpful to end the name with the word ‘Report‘. Replace the beginning part with what makes sense for your Report. Finally, click the ‘Add‘ button.

Finally, double click on the report to open the Report in the Designer window.

Add A Dataset

Before specifying what the D365 SSRS report will look like, we need to tell the report what data to use.

First, right click on the Datasets node, and select ‘New Data Set‘. Note this is similar, but different, to how we specify a ‘Datasource‘ on a form.

Next, right click the new Dataset node and select ‘Properties‘.

Then, in the properties, window set the ‘Data Source Type‘ to Report Data Provider.

Importantly, reports absolutely can be created using a ‘Query‘ as a Dataset. However, for this example, I am showing you how to use a Data Provider class. Queries can be faster to run. But Data Provider classes allow you to run existing x++ methods on tables and later show those values on the report.

Next, set the Name property to ‘CustGroup‘.

Lastly, click the three dots button in the Query property.

Notice, a dialog will open, asking which Data Provider class to use. For this example, ‘tutorialSSRSReportDP‘. If you don’t see it in the list, compile the class and retry. Then, click ‘Next‘.

Finally, click the ‘All Fields‘ checkbox, then click ‘Ok‘.

Add A Report Design

Now that we have specified what data to show, we need to specify where in the report layout to show the data. To do that, we need to add a report Design.

First, right click on the ‘Designs‘ node, then click New>Precision Design.

After the design node is created, double click on it to open the report design.

Notice, in this designer, there is a window on the side named ‘Report Data‘. If you do not see this window, go to View>Report Data to re-open it.

Additionally, there is a ‘Toolbox‘ button. Click on the ‘Toolbox‘. This shows all of the user interface controls that can be shown on a report. To keep things simple, in this example will create a basic Table report.

Click on the ‘Table‘ node. Then, drag it onto the main Design area.

Populate The Report Table

Note, a table control will be shown as a grid. Initially none of the headers or rows are set. The next steps are to set the Dataset on this control, and specify what field from the Dataset should shown in each column of the table.

Drag the table control to position within the design window.

Next, click on the control, then click on the top left corner of the control to select the entire Table.

Then, in the Properties window, set the ‘DataSetName‘ to the Dataset we added earlier. In this example, set the value to ‘CustGroup‘.

Next, click and drag the ‘CustGroup‘ field from the CustGroup Dataset onto the Data row of the Table control.

Doing so, will automatically set the Data row field with the right value. Additionally, the system will set the Header equal to the label of that field.

If you need to change the value of either box, right click the box, and select ‘Expression…‘

Without too much explanation, you can change the name of the field to be a different field, then click ‘Ok’.

Next, repeat the steps to drag the Name and PaymTermID fields onto table as well.

Now, add one more column. Specifically, right click on the grey bar above the right most column, then select Insert Column>Right. Note, you can also use this menu to Delete Columns if you make a mistake.

Finally, drag the ClearingPeriod field to the new column you just added.

Deploy Report

After creating or changing your report, it is extremely important to remember to Deploy your Report. To do that, right click on your report, and select ‘Deploy Reports‘.

If you do not deploy the report after you create it, you will receive error messages when you try to run your Report.

Additionally, If you do not deploy the report after you make changes to the Report, the changes will now show up when you run the Report in the browser.

Controller

Fifth, create the Controller class. This class tells the system what report to run.

Right click on your project in Visual Studio. Then, select Add>New Item.

Next, select Class from the list. Enter the name tutorialSSRSReportController. It is a best practice to end the name with the word ‘Controller‘. Replace the beginning part with what makes sense for your report. Then, click ‘Add‘.

Enter the following code into the code editor window.

class tutorialSSRSReportController extends SrsReportRunController

{
    public static tutorialSSRSReportController construct()
    {
        return new tutorialSSRSReportController();
    }

    public static void main(Args _args)
    {
        tutorialSSRSReportController controller = tutorialSSRSReportController::construct();

        controller.parmArgs(_args);
        controller.parmReportName(ssrsReportStr(tutorialSSRSReport, Report));
        controller.startOperation();
    }

}

Notice, there are several important things to understand in the Controller class.

First, everywhere where I have written tutorialSSRSReportController, you need to replace that text with the name of your Controller class.

Secondly, unlike in the SysOperation Framework Controller class, this class extends SrsReportRunController.

Thirdly, in the call to the method parmReportName, you need to specify the name of your Report and Design that you created earlier.

Menu Item

Six, in order for a user to run the D365 SSRS Report you created, you need a Menu Item. Menu items are the links shown within the modules of Microsoft Dynamics 365 for Finance and Operations. They tell the browser how to navigate to forms, reports, batch jobs, and more. Read my article on How To Create Menu Items in D365 for more information.

Right click on your project in Visual Studio. Then, select Add>New Item.

Next, select Output Menu Item from the list. Enter the name tutorialSSRSReport. It is a best practice to name this menu item the same as your Report. Then, click ‘Add‘.

Double click on the Menu Item. Then, right click the main node and select Properties.

There are three properties that need to be set.

First, set the Label property to the text you wish to show for the link in the browser. For example, set the Label property to “Customer group report“.

Second, set the Object Type property to ‘Class‘.

Third, set the Object property to the name of your Controller class. In this example set the value to tutorialSSRSReportController.

Menu Extension

Seventh, add the Menu Item created in the previous step to a Menu.

In this example, add the Menu Item to the existing Menu named ‘Accounts Receivable‘. To accomplish this, create an extension of the existing Menu.

If you have already extended the Accounts Receivable Menu, find the node under AOT>User Interface>Menu Extensions. Then, right click and select ‘Add To project‘. Then, right click on the node and select ‘Open designer‘.

If you have not yet extended the Accounts Receivable Menu, then in the Application Explorer, go to AOT>User Interface>Menus>AccountsReceivable, and select ‘Create extension‘.

Next, drag the Menu Item you created to open the report into the Menu Extension in the designer.

If you want to move the Menu Item up or down in the list, hold down the Alt key, and then press the Up or Down arrow keys on your keyboard.

Security

Eight, whenever you create a new Menu Item, you need to add that menu item to a Privilege, Duty or Role. See my article on how to Create Security In D365 for more details.

If you do not add the new Menu Item to a security object, only users with the Role of System Administrator will be able to run your report.

Demonstration

Now that all the necessary objects are created, built, and the report is deployed, it is time to run the D365 SSRS report.

For this example, go to Accounts Receivable>Reports>Customer group report.

Set the Terms of payment parameter, then click ‘Ok’.

After a few moments, see the report printed to the screen.

Conclusion

There is a LOT more to learn how D365 SSRS reports. In this article you learned how to create the required components necessary to run and display data to a user. There is a whole lot more to cover on various ways to display that data in a design. As well as other ways of retrieving the data. For now, this should give you a good foundation to get started.