Sometimes you will notice that your report is interacting slowly in the browser and even in Report Designer. There are a couple causes for this, some that are application based and some that are Excel based. Below you will find some tricks you can do in the application along with some items to check within Excel.
The following settings, when disabled will improve rendering times and reduce delays when interacting with reports.
- In Solver Cloud/PH we have introduced a Report Properties to disable formulas upon rendering. The pro of this is that what gets rendered in the web is a number (string value) with now excel formula/calculation driving the number. This can improve report speeds because there are no calculations happening.
The negative to this is that if you like to analyze and change numbers after the report is rendered, this should not be used.
Note: this is a Reporting Template only feature. This setting is not used by Planning Templates
- For budget templates, if you do not plan to use LIDs, turn off Allow Line Item Details. This will improve the rendering times of your report.
Excel Properties to Check
- A large file: the larger your Excel file, the more processing Excel & your computer must do. Ask yourself if a file this large is needed.
- Volatile Excel formulas: Some Excel formulas are labeled as "volatile". What this means is that they can only be calculated one cell at a time vs multiple cells at a time. When dealing with small Excel files this can have no impact, but when dealing with larger Excel files, this can significantly increase the calculation times of the file and delay the interaction/responsiveness of the report. A listing of volatile Excel files can be found here. Consider a different formula that is not volatile.
- Excessive named ranges: In Solver, many named ranges are created post rendering of the report. Depending on the number of named ranges, this can add some delay time to the report. Depending on report requirements, there many not be much that can be done here, it may just be something users must be aware of.
- Unused Excel Styles (FRx Styles): Excel styles are added after a report is ran and are also calculated. Styles can add to the rendering times of reports and can also delay the responsiveness of a report. Consider removing all unnecessary styles where applicable.
In many cases we have seen a 25%+ improvement in reports.
To remove unused Styles, simply click the styles dropdown from within Excel and delete the custom styles. This can be pretty tedious if you have many styles as the delete action is one style at a time. Below is a macro you can use to delete all custom styles with "one click". Add this macro at the "This Workbook" level within the VBA editor.
Sub RebuildDefaultStyles() 'The purpose of this macro is to remove all styles in the active 'workbook and rebuild the default styles. 'It rebuilds the default styles by merging them from a new workbook. 'Dimension variables. Dim MyBook As Workbook Dim tempBook As Workbook Dim CurStyle As Style 'Set MyBook to the active workbook. Set MyBook = ActiveWorkbook On Error Resume Next 'Delete all the styles in the workbook. For Each CurStyle In MyBook.Styles 'If CurStyle.Name <> "Normal" Then CurStyle.Delete Select Case CurStyle.Name Case "20% - Accent1", "20% - Accent2", _ "20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _ "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _ "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _ "60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _ "Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _ "Bad", "Calculation", "Check Cell", "Comma", "Comma ", "Currency", _ "Currency ", "Explanatory Text", "Good", "Heading 1", "Heading 2", _ "Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _ "Note", "Output", "Percent", "Title", "Total", "Warning Text" 'Do nothing, these are the default styles Case Else CurStyle.Delete End Select Next CurStyle 'Open a new workbook. Set tempBook = Workbooks.Add 'Disable alerts so you may merge changes to the Normal style 'from the new workbook. Application.DisplayAlerts = False 'Merge styles from the new workbook into the existing workbook. MyBook.Styles.Merge Workbook:=tempBook 'Enable alerts. Application.DisplayAlerts = True 'Close the new workbook. tempBook.Close End Sub