Excel Reports – What, how, where?

Since NAV 2018, Excel reports have been available in the accountant’s Role Center. But what data is actually used as a basis here? Is it possible to adapt or extend these reports?

Excel Reports in the Accountant Role Center

Problem

However, since this is an output directly in Excel and you can no longer view the data set for reports since the introduction of the web client, I would like to take the opportunity to look behind the scenes of the Excel reports.

Procedure

I took a quick look at AL and found out that the Excel reports are created based on web services, so I opened the Web Services page and found the following entries:

Web services that serve as the basis for Excel reports

Now I tried to go to one of the specified pages, but to no avail. None of the pages can be accessed via the application search. So again I just had the way back to AL to see why. Again, I couldn’t find the pages under the specified object names. So I tried to access one of the pages directly from the URL:

Business Central URL with direct access to Page 5502

This worked and the trialBalance page was opened. With the key combination Ctrl+Alt+F1 I called the Page Instection:

Page 5502 Page Review

Here I found some useful information. The base page is of type API, so it is not actually used to be displayed in the client. The base table is the “Trial Balance Entity Buffer” table and this is a temporary table.

I therefore assume that the table will be filled at run time, and I would like to take a look at AL, which data are used here.

When I open the page and scroll to the OnOpenPage trigger, I can find the following code:

OnOpenPage Trigger der Page 5502 – Trial Balance Entitiy

I looked at this for all pages and found that the codeunit “Graph Mgt – Report” is always called. As a small help, I enter a table here, which indicates the numbers and names of the pages and the base table of the data. If the base table is the Account Schedule table, I have specified the field in the General Ledger Setup that specifies which account schedule is used.

Name in the menu Page No.Page Name BaseField in the FiBu setup
Cash Flow Statement5493Cash Flow Statement EntityAccount scheduleAcc. Sched. for Cash Flow Stmt
Retained Earnings Statement5497Retained Earnings EntityAccount scheduleAcc. Sched. for Retained Earn.
Aged Accounts Receivable5499Aged AR EntityCustomer entries
Aged Accounts Payable5500Aged AP EntityVendor entries
Balance Sheet5501Balance Sheet EntityAccount SchmemaAcc. Sched. for Balance Sheet
Trial Balance5502Trial Balance EntityLedger accounts
Income Statement5503Income Statement EntityAccount scheduleAcc. Sched. for Income Stmt.

Extensions

Now, as a programmer, the last question is whether I can also expand these reports. Here I keep the answer short: unfortunately, no. This is because API pages cannot be extended.

My conclusion

Without the help of a programmer, it is hardly possible to find out exactly which data the individual Excel reports access. I therefore hope that this blog entry will help consultants get more clarity here.

Leave a Reply

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

*

code