Setting a date range for a report

Question

Hello, 
 I have a "previous 12 months stock usage" report that I use to help with predicting stock reorder levels. 
 Its set up with a pre-set date range that I have to change every so often to reflect usage of items that fall outside of the original date range since time has moved on and that original date is not catching all the current data. 
 Is there a way of setting criteria (or an expression) to automatically select the data from the last 12 months from the date when the report is created? 
 Also is there a way of showing graphs/charts within reports to visualize trends or does that involve exporting data out to excel/access? 
 Regards 
 
 Andy Stoneman

Darron Cockram · Accepted Answer

Ah, I think we are hitting up against an annoying limitation in Report Designer where it cannot build a proper query when using 'special tables' in the filter. Frustratingly the error message leads you down the wrong path in this case. It would be the correct action and would if it were any other normal table being used but will not work for the special ones REPORT and CRITERIA. 
 The workaround would be to remove the INVOICE.DATE >= (REPORT.DATE - 365) part from the report filter and apply it to a section filter instead. Click on the details section and then in the properties (View -> Properties if you do not have that tool window open already) and then in the filter property click the ... button and enter the expression INVOICE.DATE >= (REPORT.DATE - 365) there. 
 That approach is less efficient than using the report filter but should work and, unless you have a huge number of invoices in your data, you are unlikely to notice too much of a performance impact Vs the report filter/criteria method of filtering.

Darron Cockram · Answer

Andy Stoneman said: Is there a way of setting criteria (or an expression) to automatically select the data from the last 12 months from the date when the report is created? 
 Not criteria but an expression could be used in the filter to achieve this. The REPORT.DATE variable will give you the date on which the report is being run, so taking a very simplistic approach you could do something like this: 
 AUDIT_HEADER.DATE >= (REPORT.DATE - 365) 
 Don't forget to remove the criteria from the report too. 
 A few caveats to bear in mind with this 
 1. I'm assuming you are using AUDIT_HEADER as the basis of your report there, but if this is not the case you'd need to adjust accordingly for the correct table. 
 2. This is essentially hard coding the report to be the last 12 months (or more specifically the last 365 days) so you'd not be able to easily vary the date at run time. 
 3. This formula takes a very simplistic approach to determining the last 12 months and does not handle things like leap years. It should be possible to create a formula to determine if we should subtract 365 or 366, but that would be a fairly complex formula so it's only something I would suggest attempting if you really need the accuracy. 
 Andy Stoneman said: Also is there a way of showing graphs/charts within reports to visualize trends or does that involve exporting data out to excel/access? 
 It's been on my personal wish list for a long time to be able to produce charts/graphs directly in Report Designer, but alas this is not possible to do currently. Export and using Excel or Access (or other similar tool) would be the only way to generate graphs or charts. 
 Hope that helps

Darron Cockram · Answer

That is because by removing the criteria and filter we are now retrieving all of the records regardless of the date, and simply hiding the details section for any records outside of the last year. This is the reason why I say it is a less efficient approach, but is unfortunately one we are stuck with due to limitations of Report Designer. The key thing is that it is only the details section we are hiding, the ORDER_NUMBER Header section hasn't been hidden at all so will show for all of the records in the returned data. Fortunately it is easy to fix. Simply apply the same filter to the ORDER_NUMBER Header section as you have on the details section and that should remove the additional headings you don't want in the report. 
 Hope that helps.

Sage 50 Accounts

Setting a date range for a report

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING