D365FO Electronic Reporting Datasource Types and When to Use Them

While learning about D365FO Electronic Reporting and trying to make your first ER model mapping configurations, you will notice that numerous D365FO ER datasource types are available in the ER Model mapping designer. You may wonder what the purpose of these different types is. Why so many different datasource types, when and why would I use them? Why would I have both Table and Table records datasource types, to begin with? Microsoft didn’t provide any answers to these questions in the form of basic concepts explained, so my colleagues and I had to learn by trial-and-error.

This article explains D365FO Electronic Reporting datasource types that I have used so far in my ER model mapping customizations. For every listed type, I gave my own understanding of its purpose, so ER beginners can get a better starting position. You won’t find the explanation for every single type, but these are the most commonly used types. I hope my list will help you on your ER journey.

I will use the Sales Invoice and Customer Invoice (FTI) model mapping for the examples in this article. On the image below, notice that I am not using the original Microsoft configuration, but the one derived from the original. Namely, it is not possible to customize the original configurations, but you always need to create your own.

Add a data source in Model mapping designer

You will see a list of all available D365FO Electronic Reporting datasource types on the left side of the Model mapping designer form. It will be the same for every report model mapping you are working on.

The middle section of this form shows you the data sources that have been used on a selected report model mapping. You can add more data sources and it doesn’t matter if you will add them to the top level or to one of the sub-nodes in the existing structure. It is your choice to organize the data sources in a way that is meaningful to you.

Notice that the Group view toggle button is turned ON in the above image. This allows you to see all configured datasources grouped by the datasource type. I find it more convenient to quickly identify the datasource I am looking for

How to add a new data source:

  • To add a new data source to the top-level: simply select the data source type in the left pane and click the Add root button in the middle pane. If there is already a top-level node of that type, the newly added data source will appear as its child. Otherwise, the top-level node of that type will also be created.
  • To add a new data source as a child of already existing node: select the data source type in the left pane, then select the parent node in the data sources structure in the middle pane and click the Add button. See also the Empty container explanation below.

Datasource types explained in this article

Datasource types are explained in the article in the same order as they appear in the form above:

Enumeration (Data model > Enumeration)

Notice that there are two different Enumeration datasource types available, first under the Data model node and another under the Dynamics 365 for Operations node. We are describing the first type here (as pointed out in this chapter’s title).

Use this datasource type if you want to reference an enumeration that you specified in some ER model. For example, in the Sales Invoice model configuration you will notice a top-level node ListCode_1, which is an enumeration type with the following values:

If you want to be able to use these values in the model mapping formulas, you will add a Data model Enumeration datasource type to the model mapping data sources. Check the Sales Invoice model mapping, where you will see the Empty containers > Enums > Data model enumerations > ListCode node. If you expand it, you will recognize the same enumeration values as specified in the previous image. While it is selected, click Edit to open the dialog with its Parameters. In the Parameters dialog you can notice that you first need to select the Model from which you want to reference the enumeration. Once a Model is selected, the Model enumerations defined in it will be offered and you can, for example, pick the ListCode_1 enumeration:

Why do we need this datasource type if we already have the D365FO enumerations? I suppose the idea is that ER models provide abstract definitions of your report data and for those you want to use values that don’t rely on AOT objects, such as D365FO enumerations. A person designing an ER model doesn’t need to know anything about D365FO enums, but should have a freedom of specifying a set of values he or she wants to use on a model level. The price of this freedom is a necessity to implement the transformation from the D365FO enums into the data model enums.

How do we implement this transformation? Or, more generally speaking, how do you use this datasource type? Like most other types, it is usually used in the formulas. In the Sales Invoice model mapping check the formula for the data model node InvoiceBase > ListCode:

As you can see, it takes the value of the Listcode field in the report datasource header and transforms its value from the D365FO enum (yellow values) into the data model enum (blue values). I must say it is kind of annoying that you have to do this at all, but there probably wasn’t a better way to merge the two worlds, D365FO and ER models.

Class

This datasource type allows you to reference any class that exists in D365FO. You would add a class if you are interested in its static methods. If you are wondering how to invoke these methods, the most common way is in the formula for Calculated field.

In standard Microsoft model mapping configurations, you will often find the classes needed for a report grouped as containers. For example, check the Customer Invoice model mapping:

There are several classes under the Empty containers > Classes > Classes node. If you expand any class, as we did above with the DirParty class, you will see its static methods automatically listed. You can use all of these methods in your formulas.

Enumeration (Dynamics 365 for Operations > Enumeration)

Notice that there are two different Enumeration datasource types available, first under the Data model node and another under the Dynamics 365 for Operations node. We are describing the second type here (as pointed out in this chapter’s title).

By using this datasource type, you will bring any enumeration available in D365FO (more specifically: Base Enums in AOT). You will then be able to use this enum in other data sources, typically in their formulas.

In standard model mapping configurations, you will often see some Enumeration data sources already added to the configuration, often under the Empty containers > Enums > Dynamics 365 for Operations enumerations node. For example, you will often find a data source named AxNoYes, which refers to the D365FO NoYes enum, like in the Sales Invoice model mapping presented below:

How would you use this data source? As you can imagine, an enum based on D365FO NoYes enum is the simplest one and the way it is used in the formulas should also be the simplest. Well, it is, but I still find it too complex and non-intuitive! Find some mapped boolean field in the model mapping and analyze its expression. For example, in Sales Invoice model mapping, find the Invoice Base > PrintInvoiceVATNum node in the data model pane, click the Edit button and you will see the following expression:

This is a common pattern you will find in many standard model mappings. Its purpose is to transform the NoYes enum value into a boolean value in the model. The left side of the formula references the PrintFreeTextInvoiceVATNum field in the first Sales Invoice line, and the right side of the formula references this enum’s Yes value. If the field on the left has the value NoYes::Yes, the formula will return the boolean true, otherwise false.

This is just a special case of transforming the D365FO enumerator values into the corresponding data model enumerator values. A more generic example was explained earlier in the Data model enumeration chapter.

Object

Use this datasource type to reference any D365FO class if you need to access its instance methods. Like the above explanation for the Class datasource type, you will typically use these methods in the Calculated field formulas.

In the example of the standard Sales Invoice model mapping, three Objects are already added. We will focus on ReportDataProvider, which is an object of SalesInvoiceDP class. Click Edit to see the form where its Name and Class are specified.

When you expand the ReportDataProvider node, notice how you automatically get a list of all available class instance methods. The most interesting for us when customizing this model mapping are getSalesInvoiceHeaderFooterTmp() and getSalesInvoiceTmp(), as they will return the report datasource records (header and lines) as we know them from SSRS. I’m saying that they are the most interesting because everything else in this model mapping is built around the SSRS report datasource records.

Table

This datasource type can reference any table that exists in D365FO. Use the Table type if you need to reference the table’s static methods. This is similar to the Class data type: you only specify that this will be your table definition and that you want to use its static methods. You will need to provide the data source name and the referenced table.

When you work on your derived model, you can Edit the selected table, which opens the dialog for specifying this data source’s parameters. The example below shows the parameters for one data source of the Table type. We specified that we want to use the CustInvoiceTrans table and the name of this data source as it appears in the Data sources list should be CustInvoiceTransTable.

In standard Microsoft model mapping configurations, you will often find the tables needed for a report grouped as containers. For example, check again the Customer Invoice model mapping:

There are several tables under the Empty containers > Tables > Tables node. If you expand any table, as we did above with the CustInvoiceJour table, you will see its static methods automatically listed. You can use all these methods in your formulas.

Table records

This type fetches the records from the specified table, view, map or data entity and gives you access to the table’s instance methods. We often reference the data sources of this type from the Calculated field datasource types. You will also be able to perform database-related operations, such as filtering, grouping, etc. on the fetched Table records.

As opposed to this type, the Table datasource type merely represents a table definition you use to access the table’s static methods. One can say that Table to Table records has the same relation as Class to Object.

As is the case with the Table datasource type, you will need to specify the data source name and referenced table. In the example below, we use the CustInvoiceTrans table and the data source name when it appears in the Data sources list will be CustInvoiceTransRecords.

Notice the Ask for query flag in the dialog above. If set, it will, in runtime, open the dialog for filtering the table records. This isn’t applicable to the SSRS-based reports that are started from their menu items. Instead, you would use it for your ER reports created from scratch that you execute by clicking the Run menu item in the ER module.

Calculated field

This will be the one you will probably use the most, or to be more precise: will spend the most time in writing their formulas. But first I must say that this data type’s name is counterintuitive. One would expect that it has something to do with creating new data fields using formulas, but as a matter of fact, it holds a result of some calculation and the result can be of various types, like a scalar value, a record, record list, etc. You will need to specify a formula over the existing data sources and the result of executing that formula will be a calculated field (or, as I think of it: Variable). It can be a final value you need in your report, or just an intermediate value you will further use in another formula for calculating yet another calculated field.

If you expand the Calculated fields node in data sources, you will get something like this:

Notice that I have set Show details to ON, that is why on the right side of every calculated field, we also see its formula. You can open the formula designer form by clicking the Edit button, and then Edit formula.

The Microsoft convention, which is also worth preserving in your newly added calculated fields, is to start the variable name with a $ sign. It will help you to quickly distinguish between the calculated fields and other datasource types in formulas.

Empty container

The Empty container datasource type allows you to organize your datasources. You can add groups of different datasource types, give them arbitrary names and combine them the way it suits you.

Conclusion

In this article, we have explained D365FO Electronic Reporting datasource types commonly used when customizing ER model mappings, with emphasis on model mappings for Configurable Business Documents. Check also our other articles on Electronic Reporting to learn more on model mapping customization techniques and writing formulas.

Leave a Reply

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

*

Docentric respects your privacy. Learn how your comment data is processed >>