locked
Report Slow to render with expressions/formulas RRS feed

  • Question

  • Hi, using Report builder 3.0 to view/print transactional documents, eg invoices and orders.

    I use table matrix (Tablix) to display the products dataset.

    the tablix only has 1 row that just repeats the product information, with 6 columns.

    I have simple IIF expressions in about 4 of the columns, yet these expressions slow down the report rendering dramatically.

    eg:  =IIf(Fields!part.Value > ".", Fields!ordqty.Value, " ")

    also I had an expression to change the backgroundColor on every 2nd row:

    =IIf(RowNumber(Nothing) Mod 2 = 0, "Gainsboro", "Transparent")

    both of these expressions add so much time to the rendering, if i remove them, then i have a PDF rendered in about 10 secs for a tablix filled with about 300 lines.. otherwise its up around 35 seconds..

    is there any optimisation that I can apply to keep the expressions and speed?

    thanks

    Poy

    Thursday, June 19, 2014 9:27 AM

Answers

  • Hi Poy,

    According to your description, you want to improve the report performance so that it will not take too long to process the expression and render the data. Right?

    In Reporting Services, we can go to the ReportServer ExecutionLog to see which part takes more time.

    Then we need to do some troubleshooting to improve our report performance. Here we have a article for your reference:
    Troubleshooting Reports: Report Performance

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

    Monday, June 23, 2014 11:45 AM

All replies

  • Is that possible to add this expression in T-SQL? BTW, how long the report (T-SQL or stored procedure) is running? 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 19, 2014 10:47 AM
  • HI Uri,

    Yes I could get around the problem with modifying the sql, or the dataset before it hits the report, but that is not the best way forward to cater for hundreds of customers and their own rdl layouts.. I need it to be flexible so anyone can change the expression per rdl.

    I'm migrating over from an old version of crystal reports.

    also I'm not using stored procedures and the database provides the datasets within a 1 second, so no optimisation required there.

    like I said, without the expressions being used, the rendering is quick, just wondering if there is some hidden treasure to make it all work faster..

    Thursday, June 19, 2014 11:11 AM
  • Hi Poy,

    According to your description, you want to improve the report performance so that it will not take too long to process the expression and render the data. Right?

    In Reporting Services, we can go to the ReportServer ExecutionLog to see which part takes more time.

    Then we need to do some troubleshooting to improve our report performance. Here we have a article for your reference:
    Troubleshooting Reports: Report Performance

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

    Monday, June 23, 2014 11:45 AM