Quickly and easily automate the calculation of your data

Gone are the days of manual reporting and repetitive copying and pasting to set up your reports.  Sage Intelligence saves you time by automating reporting at the click of a few buttons.  For example, when you add formulas to a source sheet to calculate additional data fields and run out the report again, the calculations for any additional transactions or accounts are not brought through unless you extend the formulae to cater for them.  You may have already been doing this manually.  Did you know you can automate it?

The ExtendFormulaeToUsedRows Add-In in the Report Manager automatically extends formulas to any new rows of data, every time a report is run, saving you hours.

Setting up the ExtendFormulaeToUsedRows Add-In

1. Open the Report Manager and run your report.  In the source sheet in Microsoft® Excel®, add the desired calculation(s) after the last column of source data.  For example, using a Sales report, we will add a column to calculate Gross Profit which is TotalSale – TotalCost.  The formula only needs to be added to the first row.






2. Go back to the Report Manager and select the report that you ran.  You need to apply the ExtendFormulaeToUsedRows Add-In to this report.

3. To do this, enable the Show Advanced properties of the report at the bottom of the properties tab.

4. Select the Run Add-Ins ellipses button.






5. Select the Add-In Library: Alchemex Add-In 1.

6. Select the Add-In Library Module: General Excel extensions Module.

7. Select the Add-In Function: ExtendFormulaeToUsedRows.









8. Click OK. The Specify Function Parameters window will appear.  You will need to specify the parameters that relate to your particular Excel template.  Take a look at the table below:



















9. When you have specified the Function Parameters, select OK.  You will notice that a string has been added to the Run Add-Ins field on the properties tab of your report.

10. Save the Excel Template to the Report in the Report Manager.  The Add-In will now automatically run each time the report is run and your formulas will automatically be updated and extended to include all data in the source sheet.






Prefer to watch a video to see how this is done? Watch it here.

The ExtendFormulaeToUsedRows Add-In gives you the ability to add calculations to your source data in Excel and ensure that those calculations are updated as the source data in your report changes.  In our next tip we’ll look at Bulk Importing in the Report Manager and how you can use it to quickly and easily access your new reports.