Electronic Reporting - Model Mapping example

I am writing this blog to help people who are new to Electronic Reporting because when I started I faced many issues while making one and I had to look through many websites, videos to understand the process. 

Its my first blog , so you can expect few errors from me but I can ensure you that would be able to generate the report  and would get the jist of it.

What is Electronic Reporting?

ER is a configurable tool that helps you create and maintain regulatory electronic reporting and payments.

It basically has configuration model where we define our model like what fields or records we would be needing to configure for our report. Then that model should be mapped to data source. After that a format of excel should be made to connect it through the configuration. After that we can generate.

Let me help you with an example - We need to generate the electronic report for all the customers transactions.

Path --> Accounts receivable-->Customers -->All customers--> Action Tab Customers --> Transactions

 

On clicking on transactions, you will be able to see the list of all the transactions.

We will be printing this for our electronic reporting.

Step 1 - 

First check if the configuration is there or not. You can check that on -->

Workspaces -->Electronic reporting

Go to Related links and click on Configuration providers 


You will find the list of configurations. The purpose of this list is that you can take any existing format of your report which you might need for your report. I would suggest to create one . I created the one with  Dynamics Manual.  We can even create one with Litware too. Thanks to Ties Philippi for this dynamics manuals repository which I used for my assignment.



Move to previous page and then there you could see the tile of Dynamics manuals. Click on it and then click on three dots and set it as active.


 

Step 2-

  • Now under configurations click on Reporting configurations.
  • Click on the Create configuration on action tab and click Root. Give a meaningful name and description of the report.


  • Click on the Designer present on the action tab with your report name highlighted.
Step 3-
  • Now we need to add nodes and fields. As we can have multiple account numbers, we will add the account number with type record list.
  • Under Account number add customer account as a string type.
  • We would have multiple transactions so add the transactions under account number as type record list.
  • Under Transaction node add new fields which you want in report.
        



Step 4 -
  • Click on the map model to datasource on the action pane
  • Click on new . Select the definition from the lookup. Give direction to model. Click save and then click on designer.

Step 5 -
  • Under data source types click on the table records.
  • Fill the data as per the screenshot below. 
  
  • Make ask for query as yes. Making the field yes will open the query dialog to select the customer account.
  • You can see the relations also with the tables. So thats the plus point. You dont need to add another table for transactions. You will get all the table fields, methods which you want to use.
Step 6 -
  • Bind CustTrans.custable account num with account number.
  • Bind the accountNum with CustAccount.
  • Bind other fields with their respective fields.           
  • If you check the reporting currency amount , for credit amount is negative. So to read the credit and debit amounts. We can assign one formula.
  • So click on the CreditAmt - and then edit the formula. 
  • The formula is if the reporting currency amount is less than 0 then return the positive value of the reporting amount otherwise 0.
  • Same we will be doing for the debit amount. For debit amount the reporting currency is always greater than 0. So we can apply this edit formula 
  • So if it is greater than 0 return same value otherwise zero.
  • We can always test our formula by clicking on the test on action pane and see the results.
Step 7 -
  • Close the current form and then you will be prompted to model to datasource mapping form. 
  • To test if data is coming or not , click on run button.
  • Query class will get open and add the account number. 
  • After that the XML file will be generated where you can see all your fields with the respective data .
I hope you liked this post. In my next post I will show how to do format mapping/designer that is how to print the report in excel/pdf.




 







Comments

Post a Comment

Popular posts from this blog

Electronic Reporting - Format Designer/Mapping to PDF/Excel Example

Image
 This is the second part of my blog of how to create the format designer and mapping to export the Electronic report. If you haven't read my previous blog, please read it  Electronic Reporting - Model Mapping example .  Step 1 -  Click on the node which you have created -CustomerTransactionReport and the click on the Create configuration button.    Now give the name, description. For format you can keep excel for now or leave it empty. Select the data model which you have created from previous post. Now click on the designer which will take you to the page of format designer. Step 2 - Now before mapping you need to create design document for which we will be importing our excel document. One needs to know the excel so I took little help from my friend for creating the excel designs. The way you make this is very important as it will help for multiple customer accounts and transactions. For A1 cell we can give any heading like transactions .  For A3 gi...
Read more

How to extend the Electronic Report without model mapping configuration?

Image
This post is about how you can extend the Electronic Report, if one doesn't have model mapping configuration. If you want to read how to extend the ER having model mapping, please read  How to extend the standard Electronic Reporting?   Lets take an example of how to extend the Fixed Asset roll forward report. Step 1 - Click on the root Fixed assets model. Click create configuration and then click on the Derive from Name: Fixed assets model, Microsoft. Give suitable name and description.   Step 2 - Click on the designer. We would add the last run depreciation date of each book of the Fixed asset So under Fixed assets entry point ----> AssetRollForwardTotalsByGroup---> lines, click on new then click on child of active node. Give the name as LastDepDate and type as date. Give the label too.   Step 3 - Click on map model to data source. Click on the designer. Under Data model, expand AssetRollForwardTotalsByGroup --> lines --> Last depreciation dat...
Read more