SSRS hidden columns expression consuming time while rednering

0
votes

I am converting one crystal report to SSRS In which There are multiple group header having expression like

IIF(Fields!MainGroupOrder.Value = 2 And Fields!DaysOrHours.Value = "Hours",IIF(Sum(Fields!Count_01.Value) = 0, 0, Sum(Fields!Jan_CountTurnTime.Value)/Sum(Fields!Count_01.Value)/60),IIF(Sum(Fields!Count_01.Value) = 0, 0, Sum(Fields!Jan_CountTurnTime.Value)/Sum(Fields!Count_01.Value))/60/9)

There are 12 such statements in each row with 5 rows

This row with 12 columns has visibility condition at row level

When I am viewing report after removing these expression, Report is taking 37 seconds to render With same parameter when I am rendering report with these expression It is taking 1 minutes and 20 seconds (these expressions are not visible with given parameters)

I read somewhere that SSRS after 2005 does not calculate hidden column till its not used in any other visible condition Then why time is increasing on inclusion of these expression even though these are not visible and also not used in any visible condition

Can you explain why and it would be great if you can help me in decreasing the time this report is taking due to these expression which are not visible

Thanks

1
performancereporting-servicesexpressionhidden

1 Answers

3
votes

In SSRS, every expression takes time to evaluate. Even invisible objects complete all their calculations. You can easily test this by making everything invisible, it doesn't save any time.

Here are some general tips to make SSRS reports faster:

  • Optimize the original query.
  • Cache the report beforehand.
  • Do more of the calculations in the SQL as opposed to in the report.
  • Use a stored procedure instead of plain SQL.
  • Use parameters to filter the query to just the data you need as opposed to filtering it in the report afterwards.
  • Use a calculated field to consolidate logic that is used in multiple places in the report. This way, the calculation is only done once per row.