ER - Use horizontally expandable ranges to dynamically add columns in Excel reports- D365FO

-

 

What's the Problem:



Customer requests for different GL-related reports in which we will surely have Financial dimensions in different columns. For example, one customer asks for financial dimensions like Business Unit, Department, Cost center, etc, others can ask for other financial dimensions to be reflected in the report. Since the financial dimensions are dynamic in nature based on the requirement of customers.

As a consultant, you might require to make changes in the report to show different financial dimensions as per the customer's request. It takes a lot of effort in making changes in the report format, model mapping [I assume you are familiar with Electronic Reporting - Data model, Model mapping, and Format designer]


Here is the solution:

Create horizontally expandable ranges to dynamically add columns in Excel reports in the Electronic reporting. 

Steps to create the report: 

  1. Create a data model that looks like the below screenshot:



2. Click on Map Model to Datasource. Create new model mapping and click designer.


3. Add Table records "LedgerJournaltrans". Bind the Main Account and dimensions with the record list created in step 2. We are binding the Main account and dimensions to both the record list. Record list "Dimension Settings" will be used to create the column names dynamically. Record list "Dimension Values" will be used for showing the Dimension code and its name.


4. Add Financial dimension details option available in Datasource types -> Functions. 
Click Add root and then select "Ask for dimensions" so that it would be available for selection by user at runtime. There are three options for Financial dimension selection. (Explained in the screenshot). There is no need to bind this data source to any data model.


5. Save the Model mapping and then complete this data model.
6. Create a format based on the data model with Excel format.
7. Download Excel file from Microsoft downloads.




8. Import the excel file in Format designer and it will create Excel cells and ranges based on the Name Ranges defined in the excel file.
9. Add the ranges with horizontal replication.

10. Bind the Format mapping with Format as shown below:


Final Output:

Now it's time to run the report. Complete the above configuration and press on run. Select the desired Financial dimensions that are to be shown in the report. For instance, we selected all available Financial dimensions - BusinessUnit;CostCenter;Department;MainAccount

We get the four columns based on our selection of financial dimensions.



That's it for this blog. See you in the next blog. Please comment if you have any suggestions about use of Horizontal replication.

Happy Learning! 
By Neeraj Kumar

Comments

  1. iamJoshKnoxDecember 1, 2021 at 10:47 PM

    This is a great post - I didn't know about that ask for dimensions parameter. Thank you!

    ReplyDelete

Post a Comment

You might love these

Run ER reports using Electronic Messaging Functionality in D365

-
Image
 What's the Problem: After you've created your ER format, mapped it to data sources, and completed it, you can run it from the  Electronic reporting  workspace. After the report is generated, you can save it locally.  Due to this, we need to give access to the Electronic reporting Workspace to Business Users. To control the following aspects of the reporting process, set up electronic message processing: Log information about who generated the report. Log information about when the report was generated. Save the reports that were generated for previous periods. Solution: Create an electronic message processing in the system to run the required ER reports. In the Electronic messaging functionality, we can create a sequence of steps for users when trying to generate the EM processing. The following example shows how you can set up electronic messaging to generate a report that is based on an exporting ER format for Microsoft Excel. If you want to follow this example, t...
Read more

Default descriptions - Dynamics 365 for Finance and Operations Part-01

-
Image
Introduction Transactions generated from different modules often do not generate a description on the transaction in General ledger or in the submodule from where it is generated. For example, when you post purchases invoice from Accounts payable module, system passes voucher entry with description "Vendor invoice". This description is not much meaningful. So to make the description meaningful, we will use " Default Description " functionality in Dynamics 365 Finance and Operations. Let's start with setup and then test with one vendor payment journal. Configure a default description Select “New”, chose the “Description” you want to configure and select “Language”. [ Navigation: General Ledger > Journal Setup > Default Descriptions] Two different types of variables can be configured: 1.    Fixed variable: date, form and voucher. Date and voucher is already included in a transaction and I normally don’t use these variables. Form on the other ha...
Read more

Bulk Posting of Journals in Dynamics 365 Finance and Operations

-
Image
  Bulk Posting of Journals  Have you got bored with posting journals on one-to-one basis? Okay, let’s explore something to make it interesting. There is a functionality of posting journals by selecting multiple journals with required criteria. After selection of desired journals, post all the journals in a single click ( OK ). All the processing will be same as of normal posting from the Journal form. Navigation: General Journal >  Journal Entries >  Post Journals Description of highlighted numbers: 1.     Select – Option for Select the desired Journals based on different criteria. 2. Late Selection This Boolean can be used for excluding the particular transaction or including it. If this Boolean is selected as “ No ”, then all the lines with Boolean “Yes” in the column “Late selection” will be get deselected and remaining lines will be available for posting. And vice versa. Note : By default, Column for “ Late Selection ” is not v...
Read more