report based on more than one year

Business Central/NAV User Forum
1

Hi all, I need to create a turnover report based on the G/L Entries amounts of one particular a/c only. But the problem is that I need that the total of each month of the last 5 years are given.

For example if the user needs to see 2009 turnover, the report will show Jan-Dec of the last 5 years. Any hints of how to go about it?

thanks a lot

2

Maria,

Analysis by Dimensions is your friend here. If you don’t need Dims, simply ignore them, you’ll get G/L Acc amounts not subdivided by Dimensions.

Then, select Export to Excel (under Functions). This will create not simple rows/cols with data, but an Excel Pivot table, where the posibilities are muc more to slice & dice yor data, format them and if needed, print it out. You, however, must have some skills working with Pivot Tables in Excel, of course, but that’s nothing very difficult to learn…

3

Edit: I suggest Modris’s post as solution.

PS: I posted some suggestion in this post but later edited that…

4

I think u can use Account Schedule for this, will be easy as u can set ur Column layout in A/c schedule itself and have ur calc done in NAV and then export it to excel.

5

Kapil,

Acc Schedules are not so “elastic” as Analysis By Dims and:

  1. you need to create many Colums with fixed Comparison Formulae like -1FY, -2FY etc and these are tricky to set up them as needed (help is poor about setting them up, even not all “keys” are enumerated - FY isn’t in help, but it is legal there);
  2. export to Excel is flat, not Pivot table, so no additional “slicing & dicing” is possible

So, quicker solution IMHO still remains Analysis by Dims…

6

Yeah i wud agree as online help is not very good for A/c schedules and it is tricky. [:)]

7

hey,
thanks all for your posts i did something like this and it worked, probably it’s not as efficient as the other methods but it worked.

i worked around it by creating a two dimensional array and filling each slot

regards

8

Hi Maria,

Can you “Verify Solution” that helped you solved this issue - it will help others searching for a solution