Product Release: 1704
The latest “1704” Aqilla Cloud platform is already running this release. To take advantage of the new features including optimised reporting from Microsoft Excel, users are encouraged to download the updated client software.
New Release of Aqilla’s Advanced Reporting Suite - SharperLight 4.1
London, May 2017: We are pleased to announce the general availability of a new 4.1 version of the advanced Aqilla reporting suite which is available to download with immediate effect.
This release in particular features some new “In-memory Cache” technology which can be used to report at speed across very large volumes of data, so reports will run much quicker than previously, along with a number of other Aqilla enhancements to support widescreen displays etc.
The latest “1704” Aqilla Cloud platform is already running this release, but to take advantage of the new features and optimised reporting when reporting from Microsoft Excel, users will need to download the updated client software from www.aqilla.com/resources. It is not necessary to uninstall any existing SharperLight client software, the installer will do the upgrade automatically.
SharperLight Query In-memory Cache
The new Query In-memory Cache option minimises the query interchange between the Excel workbook and the Aqilla server and reduces the chance that the same query is refreshed multiple times in the same recalculation run. In other words unless it is expressly required to do so, SharperLight will report against a set of data held in the computer’s local memory rather than constantly going back to the server and downloading what could be a large of volume of data to deliver the results to Excel. In-memory Cache is not enabled by default but when it is turned on, it will:
- Create a query plan for each worksheet. The query plan speeds up the communication with the Aqilla Cloud platform (remote) service.
- The results of a Query are held in memory (or cached) for re-use..
The net effect is that worksheets that use Value Formulas extensively will then recalculate between five and ten times faster.
How To Use In-memory Cache
In-memory cache is enabled with the “Use Cache” checkbox on the ribbon and this setting will be remembered and maintained for the same windows user on the same computer.
Every time a worksheet with Value Formulas is recalculated, it will update the query plan. The plan also includes the last returned result for each unique query and this fact is important when considering how the cache impacts recalculation behaviour.
if the Query In-memory Cache has a result in memory, it will use this value whenever Value
Formulas are refreshed with the native Excel automatic or manual recalculation. In other words if the underlying data changes this will not directly affect the data being held in cache and so the report will not take account of any such change.
In order to force the rebuilding of the cached values, the user must use the Sharperlight recalculate Range, Worksheet or Workbook functions available from the ribbon, left hand mouse click worksheet menu or the equivalent Sharperlight macro buttons.
NB: This subtle change is extremely important because cached values are saved in the workbook and they can be reused for weeks, months and even years.
How To Use In-memory Cache
With Query In-memory Cache the management of query plans and cached values becomes an important extension of using the cache. From the Cache group on the Sharperlight ribbon, there is a Manage icon that can be used to oversee each worksheet cache. It opens the Query In-memory Cache window and it shows the size in kilobytes and age of each cache, and there are options to clear the caches on selected sheets.
In this example, the workbook was saved 17 days ago and the data was refreshed from Aqilla 6 minutes ago. If the workbook is resaved then the new data will be saved, otherwise the old data will still be held.
The persisting of cached query results in a workbook creates some interesting possibilities for users. If the author of a workbook recalculates a worksheet multiple times using different filter criteria and then saves the workbook and gives it to someone else; provided the recipient has the Sharperlight Excel Add-in, if they open the workbook and change the worksheet filter criteria to one that has be recalculated and saved, then the cached values will be used and the user is not forced to log into Sharperlight.
In effect caching is a simple method of turning normal Value Formulas into off-line formulas. In addition, if the data is being held in the workbook, the response in displaying the data is almost instantaneous.
Depending on the use of the workbook, it may be desirable to add buttons to the worksheets to allow the user to trigger a Sharperlight recalculation of the worksheet or the whole workbook. These buttons can be easily added as in the following example.
The example shown is a simple Aqilla report pack that has a front sheet that accepts two parameters (Financial Year and Period Number) and displays some charts and a brief overview of the figures derived from the other worksheets. The two other worksheets use Sharperlight Value formulas to display a Profit and Loss report and a Balance Sheet report.
When the user changes either the Financial Year or Period, the workbook recalculates. If the data for these selections is already cached in the workbook, this data will be used to populate the worksheets. Otherwise the data is extracted from Aqilla.
If the report has already been run for a particular year and period combination and the data has been cached but the data in Aqilla may have changed since running the report, a refresh can be achieved by using one of the Sharperlight recalculate options. These options can be added to a worksheet as follows:
When one of these buttons is selected, it is placed onto the current worksheet and the user may drag the button to a suitable position in the sheet.
In future, the use can simply press the button to refresh the data from Aqilla.
Fixed Assets API
New Support For Widescreen Displays
Some users may already be aware of the ability for various views throughout Aqilla to switch into a wide screen view to make better use of commonly found wider 16:9 format screens. In the following example starting with a standard Account Enquiry, we can see that there is data to the right that can scrolled left-right to view:
....can be transformed thus by double-clicking on the ribbon displayed above the report columns:
* Keen eyed users will also note that this example also features the lines and shading introduced in Aqilla “16H” released in October 2016.
The Back Button Works!!
After many years of trying to avoid inadvertently pressing the browser back button when using Aqilla, we have now made some changes such that it will now reliably work within the application pretty much as one would expected it to. In other words it will return to the previous screen or view in exactly the same way as the Aqilla ‘blue’ back button always did.
Additionally if a user presses Refresh (F5 or cmd-R on a Mac) this action now opens the Aqilla Home Page.
Debtor Statement Selection
Debtor Statements can now be selected to ‘Smart-search’ on Account Name as well as Account Code. This saves the need when for example generating a Statement for an individual account to know the explicit account number.
Month or year end doesn’t have to be slow or difficult. Here is a best practice guide to the busiest times of the accounting and finance year with 5 tips to facilitate a quicker and more efficient period end.