NAV has a nice feature (table) that holds period date information. This allows us to easily create reports that take any date range and create period based columns (or Rows or Sheets) by days, weeks, months, quarters or years. This is basically the same functionality used in the Budgets trendscape window in NAV where you can see the data by day, week, month, etc.
The resulting report will look like this:
Account Jan-2008 Feb-2008 Mar-2008
1000 45,679 38,945 52,955
1100 2,676 3,886 6,765
1200 49,623 56,109 59,980
The NAV date functionality enables the columns of Jan 2008 to Mar 2008. With the change of one filter, this same report could be run by weeks for the same 3 month period.
The date table has 3 important fields and a few others that are nice to have. They are: Period Start, Period End and Period Type. If you wanted to see all the Period Types of "Month" during the period of Jan 2008 to Dec 2008, then we would use an NL() Jet Reports function on the NAV Date table to do this. Here is the formula:
=NL("Columns","Date","Period Start","Period Type","Month","Period Start","01/01/2008..31/01/2008")
This means… create a column per Period Start entry, for only periods of type Month for the range of all of 2008. This returns only the start of the period and then another NL() Jet function is necessary to obtain the end of that period. Here is that formula:
=NL(,"Date","Period End","Period Type","Month","Period Start",C$2) (where C2 is a reference to the first formula listed above)
This gives you the end of the period that goes for one month from the start of the period obtained in the first formula.
See the attached sheet for the exact formulas and a mock up of the functionality. The NAV Date Formulas.xls can be printed and is for learning purposes and the NAV Date Example.xls is a base report with only this functionality. The nice thing is the example can be used as a base for any period bucketing type of report.
For additional help, see Jet Report's online help for Date functionality.
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Is anyone aware of a way to do this with a bi-annual report?
I have a created a P&L reports using the dating method listed in #0. I then created an options page where the viewer can choose the starting date and ending date, along with a lookup of the period type. This works wonders. Any viewer can, with the same report, choose if he wants a monthly, quarterly or yearly report, and I don't have to do anything myself.
I was recently asked for a collective report for the first half of the year, 01012016..30062016. How would you with the NL date funktion in #0. get this out in ONE column? I tried simply using the type = Year, to create 1 column. But, as it should, it replaces my given end date with the systematic end date of the Fiscal Year. So it annoyingly does as it should :) I tried writing Half Year, Half-Year and HalfYear in the period types, but they don't exist.
Any good ideas on this? Or would you simply do another report where you don't use the =NL("columns","Date") function, and normal starting and ending date? I would just love if I could have them all in the same report.
/Kemad -
Jet Reports Historic Posts It looks to me like the period types are hard coded in. There's probably some horrible hack you could do to get around it (by simulating it in excel, then doing one lots of calculations if it's half year and one if it's any of the others), but it would bastardise your otherwise beautiful report.
If they want half year, do another report!
Please sign in to leave a comment.