Account Schedules / Column Layouts issue in Nav 2013 R2

Vickster69

We have been trying to setup an Account Schedule with a Column Layout that shows all 12 months of the year. We have done this without issue in previous versions of Nav but whatever we try in 2013 R2 it just won't pull the right data through into each Column.

Has anyone else had any similar problems in this area? Any suggestions would be gratefully received

I can post more detailed info tomorrow if required, but just wanted to get it logged for starters.

davmac1

I moved across a NAV 2.6 financials with 12 columns to NAV 2013R2, and it runs fine.
Share the details and I will see how my copy differs.

Which report objects are you using?

Vickster69

Sorry for the slow reply - I've been having fun trying to get the Customer's database setup locally!

Now that's done I have found a couple of issues...

1) There appeared to be a problem with Nav not correctly picking up the Acc Period calendar and instead showing the System calendar dates. I deleted everything from Table 10560 Accounting Period GB and thought I would start again - however I cannot see how to Create Fiscal Year from within RTC? Apparently if you search for Accounting Periods you will get to the right place, but my search returns nothing! In the end I had to set them up manually, direct from the Table.

2) The Account Schedule and Column Layout works for any Month with 30 days or less, but for any 31 day months it is missing off the last day. I'm sure we have worked around this problem in the past without any trouble, but whatever we try now, we are either missing the last day, or we ONLY get the last day. Any suggestions for how to get around this?

Please remember we are using the UK/GB version and therefore do not have the Comparison Period Formula field and only have the Comparison Date Formula field.

We have got 12 columns and the Comparison Date Formula field is as follows;

Column 1 (blank - ie the current month)
Column 2 +1M
Column 3 +2M
Column 4 +3M
Column 5 +4M
Column 6 +5M
Column 7 +6M
Column 8 +7M
Column 9 +8M
Column 10 +9M
Column 11 +10M
Column 12 +11M

Many thanks in advance

davmac1

If you are starting with the current month, shouldn't the other months be minus and not plus?

The one mod we have that is more specific, but less flexible, is to add a date range to the column layout.
When I get time, I am planning to make it period based.

Vickster69

Apologies, I forgot to add that we are setting a Date Filter on the Account Schedule of 010413..300413 so the current Month is April 2013 and then we work forwards up to March 2014.

We tried doing it the other way round as well, as that definitely worked for another Customer on an earlier version of Nav - but it didn't work in Nav2013 R2.

Any other suggestions would be gratefully received

davmac1

Instead of the calendar date use P-11 for the date filter.
It seems to work for me.
(P=accounting period)
Keep the -1M, etc. in the column layout (it won't accept P there).

Vickster69

Thanks but entering P-11 just reverts to 010413..300413 so I am still missing the last day of any month with 31 days.

However I have just thought of a workaround - enter my date filter as 010313..310313 and shift my Comparison Date Formula on by 1 month for each column - ie April is now +1M instead of blank, May is now +2M instead of +1M, etc. This means that I now get the whole month for every month, including those with 31 days! It might not be a long term solution but it will certainly work for now.

davmac1

The 30 days month was causing your problem.
When I used P instead, it seemed like it picked up the full month for every month.
I was testing this in 2013 not 2013R2 - when I get a chance I will test it in 2013R2 and see if it misses entries.

Vickster69

Thanks, that's great - I've been asked to log it with Microsoft as an issue if relevant, but obviously want to make sure we have covered every possible angle before doing that.

Vickster69

Aha I've found a better way to do it now - enter the Comparison Date Formula as;

-1Y+1M for April 13
-1Y+2M for May 13

(etc)

...and enter your Date Filter as 010314..310314

Easy when you know how...and after you have spent ages over thinking it!!