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

 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 give the heading as customer account and then click on the A3 dropdown on top left and give the label as CustomerAccount_Label.      


 
  • For B3 you can give CustValue as the name of the box like the previous one.
  • From A4 to E4 give the names to columns - Voucher, Date, Currency, Debit, Credit.
  • Click on the A4 cell and the shift+right arrow key till E4 cell and give label LineHeaderLabels.

 
  • Give different names to A5-E5 cells according to the header names like eg. A5 can be renamed to Voucher_Val and accordingly for others.
  • Now highlight the cells from A5 to E5 and then give the cell name as LineValues.

  • Now comes the most crucial part - as we need multiple customer account and one customer account can have multiple customer transactions- for that we need to highlight the cells from A2 to E2 and from A2 to A5 till E5. We can name it CustomerTransactionsHeader. 
 
 
  • Save the file and then clcik on the import button on the top right and then import from Excel.
  • Your format designer would look like this - 

 

Step 3 -
  • Click on the mapping and your model should look like this .


The record list here specifies that it can have multiple records.
  • Now bind the fields for your respective fields. 
 
  • For date value click on the edit formula and then type DATEFORMAT(@.Date,"dd-MM-yyyy"). 
Step 4 - 
  • Close the form , then under versions change the status to Complete. 
  • Then click on the Run tab above and select records if any otherwise click okay.
  • The report will be downloaded in excel file with the same format which you have imported .
 


Step 5-
  • Now if you want the ER to be converted into pdf then we need to follow other steps.
  • Go to electronic reporting. Under related links for to Electronic reporting destination.
 
  • Click on new then select the reference from the drop down. You would find the name of the format designer.
  • Under File Destination click new button and then give name, file component name. Mark the field convert to pdf as yes.
  • Then save the record and click on settings on File destination tab .
  • Go to Screen tab and then click enabled yes.
  • Now go to reporting configurations.
  • Change the status to complete and then run the report from action tab.

  • Click ok and the report will be downloaded in the pdf format.
 


I hope you liked this post. And I hope you would be able to have the ER in pdf/excel report. In case of any queries be free to ask in comments.










Comments

Post a Comment

Popular posts from this blog

Electronic Reporting - Model Mapping example

Image
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--> ...
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