RD PH/Cloud How-To: Speed up Slow Reports

Summary:

This article details how to improve render speeds for reports that are interacting slowly in the browser and even in Report Designer. Some causes for this are application based and some are Excel based. Below you will find some tricks you can use in the application along with some items to check within Excel.

Article Sections

• Method
  • Excel Properties to Review
• Properties

Method:

The following settings, when disabled, will improve rendering times and reduce delays when interacting with reports:

1. In Solver Cloud/PH we have introduced 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 an excel formula/calculation driving the number. This can improve report speeds because there are no calculations happening.
   
   The con 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.
2. For Planning 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 Review

1. 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. 
2. 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 formulas can be found here. Consider a different formula that is not volatile. 
3. 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. 
4. 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. 

References: http://support.microsoft.com/kb/291321

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 [0]", "Currency", _
               "Currency [0]", "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

Properties:

Applies to:

• Solver Cloud
• Private Host