Post reply

SSRS Caching

  • October 11, 2011 at 2:05 am

    #249035

    Hi folks.

    First of all, sorry for not posting any code here, but I'll try to explain my issue in detail. The bahaviour below is on SQL Server 2008 R2 Enterprise Edition. What I want to do is using a NULL delivery provider in a data driven subscription to preload cache with multiple versions of the same report (with different parameter settings).

    The report has several parameters, one of these does not have any default value. As far as I've understood the caching of reports, SQL Server will (if enabled) cache a separate copy of the report for each unique combination of the report parameters. However, this does not seem to happen. If I run the report with a set of parameters, that is successfully cached. If I then change a parameter (let's say OrderDate to the next day) and run the report again, it does not have any cached copy. So far, so good. However, if I change the OrderDate back, the previously cached copy is not there, and the report has to be generated all over again. This is not as intended.

    Does anyone have the slightest idea how SSRS can be configured to cache mulitple copies of each report?


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • October 11, 2011 at 2:13 am

    #1392703

    Ole,

    which settings are you using right now. The default setting is "Do not cache temporary copies of this report", so I wonder if any caching is configured yet.

    [font="Verdana"]Markus Bohse[/font]

  • October 11, 2011 at 2:43 am

    #1392723

    Thanks for your response. I wish it was that simple. Attached is my settings for caching.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • October 12, 2011 at 1:57 pm

    #1393694

    Hmm, seems like I've misunderstood the caching:

    Caching Reports (SSRS).

    A cached report is invalidated in response to the following events: the report definition is modified, report parameters are modified, data source credentials change, or report execution options change.

    It seems like I have to autogenerate the reports to a file share instead. What a disappointment. It seems to me that caching is pretty much useless unless you have a report that most of the time runs with the same parameters.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • October 12, 2011 at 5:03 pm

    #1393740

    Hi Ole,

    I don't know if this works.... but have you tried creating linked reports with the parameters defaulted on each linked version to the different required values and see if it will cache each one?

    Ignore that...you can use Cache Refresh Plans, they do exactly what you want..

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • October 13, 2011 at 2:13 am

    #1393855

    In theory, this is fine. However, as the parameter list is dynamic this is not an option for me. Thanks for the response though, as this may be an excellent workaround in other cases. If you'ld like SSRS caching to be more flexible, please vote for my suggestion at Microsoft Connect.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • October 13, 2011 at 3:30 am

    #1393887

    okbangas (10/13/2011)

    In theory, this is fine. However, as the parameter list is dynamic this is not an option for me. Thanks for the response though, as this may be an excellent workaround in other cases. If you'ld like SSRS caching to be more flexible, please vote for my suggestion at Microsoft Connect.

    Can you elaborate on this : "The parameter list is dynamic" - how is it dynamic? What changes?

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • October 13, 2011 at 2:11 pm

    #1394351

    I have a list of parameter values, for which I intended to create cached copies of the report. This list contains the same columns, but the list of parameter combinations is dynamic both in length and values.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • October 13, 2011 at 6:48 pm

    #1394410

    Sorry, I hadn't taken in that you said "data driven subscription"...

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

    Viewing 9 posts - 1 through 8 (of 8 total)

    You must be logged in to reply to this topic. Login to reply