Account Schedules-export to Excel

(1) Share

Report

Posted on by MCarrero

774

When creating an account schedule you have the option to only show if not zero or always show (among a few other options).

Is there a way to show zero's? From what I see, if there is no value, the field is just blank.

I have a client who wants to be able to export the acct schedule to an editable format (Excel) but then wants to manipulate the data. A few things she is running into are:

1. The numbers export as text so the format needs to be changed

2. The zero balance fields show up as blank rather than with a zero.

Is there any formatting in D365 for Financials that can rectify either of these situations?

Categories:

Reports and Business Intelligence

All responses (5)

Answers (0)

Suggested answer

AJAnsari 5,754 on at

Like (1)

Report

RE: Account Schedules-export to Excel

Hi Mary,

I did a quick check on YouTube for Account Schedule KPI and found a few videos from other partners in the channel. (www.youtube.com/results) That might be worth checking out.

I hope this helps. If my response has answered your question, please verify by clicking Yes next to "Did this answer your question?"
MCarrero 774 on at

Like (0)

Report

RE: Account Schedules-export to Excel

Thanks AJ, however I cannot access your recording without paying to become a member from what I see.
Suggested answer

AJAnsari 5,754 on at

Like (0)

Report

RE: Account Schedules-export to Excel

What happens if she uses the "Update Existing Document" (the second action item in the screenshot)? Does it revert to the original formatting, requiring her to do what she does over again?

Unfortunately, there is no solution to your template problem. However, you can use the Account Schedule KPI Web Service to extract Account Schedules created in D365FIN and use them in Excel or Power BI. This allows you to use the Account Schedule data but apply your own tabular formatting and/or visualizations and refresh them on demand (or in a scheduled manner, if using Power BI).

If you are not familiar with the Account Schedule KPI Web Service in NAV and D365FIN, I did a session for NAVUG at last year's Summit and and later this year as an encore session (if you are a NAVUG member, you can go to their Recorded Webinars section to watch the encore presentation): www.navug.com/.../charting-with-account-schedules-for

I hope this helps. If my response has answered your question, please verify by clicking Yes next to "Did this answer your question?"
MCarrero 774 on at

Like (0)

Report

RE: Account Schedules-export to Excel

She tried the edit in excel but didn't like the layout so she tried it this way and made herself a template of sorts that she can copy the formatting to the output everytime she runs it.

The issue with what you show (from what I see) is that she would have to do this every time she runs the report and exports to excel.

As far as this part below, thanks for the information. I didn't realize that functionality was even there at your fingertips. The issue I have run into when creating your own account schedules is that if you do use them to print by default in the GL setup, and you accidentally run the "generate account schedules" from the account category screen, it wipes out your custom account schedule.

Also, if you are in the Business Manager Role Center, you will see an Action Item in the ribbon called "Excel Reports" where the default Income Statement, Balance Sheet, Retained Earning and Statement of Cash Flow reports can be directly printed to Excel (you can define which report prints by default from the General Ledger Setup). I've had trouble working with date filters in this report, but again the zeros show up, and the numbers in the columns can be summed/aggregated.
Suggested answer

AJAnsari 5,754 on at

Like (0)

Report

RE: Account Schedules-export to Excel

Hi Mary,

Can you share from which menu/screen in D365 you are exporting the Account Schedules to Excel?

I just went to the Account Schedules page, clicked on Overview for the desired Account Schedule, and clicked on Edit in Excel. This exported the Account Schedule, but I wasn't seeing my column(s) that showed amounts, so I clicked on Design in the Excel add-in, added the desired column (you will see a list of fields called Column1, Column2, etc. and these show our columns from the Account Schedule).

After making the change and refreshing the list in Excel, I can see my amounts in a column. The zeros here show as 0 (and I can change the number of decimal points as I normally do). Also, the values have a type of "General" so I can sum these amounts or do other mathematical functions.

Screenshots below for your reference:

Also, if you are in the Business Manager Role Center, you will see an Action Item in the ribbon called "Excel Reports" where the default Income Statement, Balance Sheet, Retained Earning and Statement of Cash Flow reports can be directly printed to Excel (you can define which report prints by default from the General Ledger Setup). I've had trouble working with date filters in this report, but again the zeros show up, and the numbers in the columns can be summed/aggregated.

Regarding your question about modifying the Excel templates, you can change templates that use the Microsoft Dynamics Office add-in (the two noted above), but you cannot modify templates in D365FIN associated with "Send to Microsoft Excel" that you see an option when you click the "Print" button on a report.

I hope this helps. If my response has answered your question, please verify by clicking Yes next to "Did this answer your question?"