Learn how to preserve formulas in older versions of Sage Intelligence Reporting

In our previous two tips we focused on Distribution in Sage Intelligence Reporting.  In the first tip we showed you how Intelligence Reporting gives you the option to send reports either with embedded formulas or without. When doing so, you also need to take into consideration whether your recipients have Intelligence Reporting installed or not. In the last tip, we shared scenarios to help you know which options to choose when working with certain scenarios.

The two tips can be found here:

In version 7.5 onwards of the software, you can send reports with embedded formulas by deselecting the Convert Each Worksheet to Static Values option when setting up your distribution instruction.


This obviously provides convenience as recipients now have greater flexibility in analyzing the information they receive. If you are on an earlier version than 7.5 then you’ll be happy to know that this is still possible and in this tip we are going to show you how to do this using a Report Designer layout in Sage 300 2014 Intelligence Reporting.

When you distribute a report normally, you should know that the information is converted to static values for both Excel and Sage Intelligence formulas as can be seen in the below two images.



To ensure that these remain as formulas after distribution, do the following:

1. Un-hide all the source sheets in your workbook. It needs to be sent with these otherwise any formulas that pull values from them will return #NA errors.


2. Make sure that all cells containing formulas you want to preserve are locked. Excel locks all cells by default in a new workbook so unless you’ve made changes, you won’t need to do this. You can check this for a cell or group of cells by right clicking on the selection, choosing Format Cells and then looking at the Protection tab.


3. Protect the sheet containing the formulas. You can do this by right clicking on the sheet name, selecting Protect Sheet, entering a password, clicking OK and then confirming the password.


4. Setup up your distribution instruction as you normally would and include the password you used with it. This could be in the workbook itself, file name or email body depending on what option you use. Then when it comes to selecting the sheets for it, make sure that all of them are checked. As per point 1, this needs to be done to prevent #NA errors.


5. Send your report, when it is opened by the recipient, the formulas will remain intact and values will be returned as can be seen in the below two images. If the user would like to edit it further, they can unlock the sheet with the password included with it.






For more free reports suitable to your business needs, download the Report Utility and follow the easy steps to select the reports you would like.

Can’t find the report you are looking for? We’d love to hear your report ideas.

Click here to submit your idea