I have a simply issue regarding Navision reports column layout. What I want is that if I put for example period 01.01.2010…31.10.2010 in date filter it should show me each month only from January to October + YTD in different columns. Now I got a column layout that do this but there is only a slight problem. With that 01.01.2010…31.101.2010 datefilter it shows also November and December columns with transactions posted on those months. YTD value is totally based on that datefilter as it is supposed to be so basically my current column layout shows months from January to December + YTD, and the YTD value isn’t from January to December but from January to October on basis of that datefilter I have set.
What I would want this report to do is leave Novembers and Decembers columns blank or even better leave those columns totally away from the visible report view if novembers and Decembers dates aren’t present in datefilter. Then of course if I put 01.01.10…31.12.2010 in datefilter it should show also Novembers and Decembers columns with transactions. But as put in simply words if I put datefilter 01.01.2010…31.03.2010 it should show only columns January-March+YTD, 01.01.2010…30.06.2010 it should show only columns from January to June+YTD etc. Is this possible? If it is not possible that visible columns are based on datefilter then is it possible that column layout leaves those months blank from transactions if those months aren’t on that defined datefilter? I can live with my current report but it is annoing that it shows transactions from months from the future that aren’t closed yet.
Hi Aleksi and welcome to this forum.
What you are asking for - if I understand you correctly - is that you want to see one column per month but only those columns which are inside the date filter applied.
If so, there is no standard way to achieve this with only ONE column layout as the columns - especially in the matrix preview - cannot be hidden or shown dynamically.
You have two ways to achieve this:
- Define one layout per month, i.e. make an october layout which shows January to October (10 columns)
And define another one for november showing January to November (11 columns)
- Let your partner add some functionality to hide the columns not within the date filter.
thank for the help, I think that using different layout for each of the months would do the trick but when thinking the fact that there are multiple users and list of different column layouts would be too large if there would be 12 different layouts to pick just for this task besides all other layouts. I think that adding new functionalities would be the wiser thing to do since we are currently updating to new version and this kind of adjustmets could be done within it.
The problem is, that MatrixForm, which displays Acc.schedules is clumsy enough already “by design” to tamper with it furthermore. It is next to impossible to do such modifications to MatrixForm.
If it was easy achievable, there would be all this included in standart. Hiding colums --never mind, in code or manually, as in ListForm-- is one unsolved problem, another boring unsolved case is impossibility of filtering rows by value. The last has been asked here in forum many times - how to hide “empty” rows…
Analysis by Dims, when exported to Excel, create Pivot Table, when you can play with it as you wish - filter, add hierarchical subtotals, etc etc, you name it.
Acc.schedule is built internally in a different way, it is exported as flat Excel sheet, but even there you have some limited Excel features to filter the result.
Maybe export to Excel as described is usable for you, and it is by no means easier and far less expensive, than trying to modify MatrixForm. Many have tried, as it really has these serious shortcomings you and other forum readers before you complained about, but I haven’t heard about a successful solution up to now (BTW at least 10 years…)