SSRS - Reducing Processing time

0
votes

My reports are paramaterized stored procedures, with no filtering at the report. No graphs or eye candy, just data, with 1 or 2 levels of grouping, with the data ordered in SP. The user can chose which columns they wish to see - typically they choose the supplied defaults, but can choose from up to 100 additional optional columns. The Tablix has logic to "hide" columns the user doesn't want to see. The stored procedure part is fast, but the Processing Time at SSRS takes typically about 95% of the Total Time. Any ideas on how to make SSRS process a set of columns (that could be different for each user) more quickly? Even hidden columns seem to be fully processed - is there any way to make SSRS more efficient at ignoring what it won't need? Thanks for your thoughts. SSRS 2016, Oracle 12G

1
reporting-servicesssrs-tablix

1 Answers

1
votes

I would use a matrix and have the optional columns returned as rows from the SP so instead of something like

ColumnA | ColumnB | Optional1 | Optional2 | Optional3 | Optional4
ABC       DEF       5           10                      15
GHI       KJL       20                                  25

It would return something like

ColumnA | ColumnB | OptionalCol | Amount
ABC       DEF       'Optional1'   5
ABC       DEF       'Optional2'   10
ABC       DEF       'Optional4'   15
GHI       KJL       'Optional1'   20
GHI       KJL       'Optional4'   25

In report you could use a matix with a column group grouped on OptionalCol

This might make the SP slightly slower but would mean SSRS only has to render enough columns for the data selected. It also makes the design a lot simpler as you don't have to worry about hiding columns.