Adding Slicers to a Reporting Services Report

  • Comments posted to this topic are about the item Adding Slicers to a Reporting Services Report

  • Hi,

    Can you attach the RDL and images in the zip file

  • Thanks for this post. I also like to receive the images.

    Can you tell how the parameter will be updated and the report will be refreshed? I can't see anything what will be fired when you click on an country.

  • Interesting but.. Schema names mixed up. Missing objects from zip.

    Nice to test it all works before publishing.

  • Excellent article well explained!

    I have a couple of performance-related comments and suggestions which may be of interest if consistent latency is paramount in your user-base.

    Never underestimate the effects of parameter-sniffing when adopting a parameterised stored procedure to feed your dataset(s)! A query plan persisted in cache generated by one parameter may be wildly inappropriate for a different parameter. If this is true of your data or query, your report which returned in four seconds when you tested it may suddenly experience unacceptably high latency when run with different parameters.

    If you're unfamiliar with the concept of parameter-sniffing, I direct you to the numerous articles on parameter-sniffing readily available on the web (Brent's Elephant and Mouse offers a good intro: http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/).

    So your report which ran in three seconds with an argument of "Luxembourg" is taking four minutes (and counting) for "USA".

    Should you resort to "recompile" or "optimise for" hints to combat parameter-sniffing in this scenario? Perhaps, but before you do, consider removing the parameter from the stored procedure altogether and placing it over the dataset as a filter instead i.e. grab all the data from the database but present just the bits relating to the selected parameter in the report. It might seem like overkill, but remember that:

    (a) the data may still exist in cache. If not,

    (b) a parallel plan aggregating results for all parameter values might well execute in roughly the same time as a serial plan for a single value - can you rewrite your procedure and force a parallel plan? (see articles on "parallel plans with cross apply" for more info)

    (c) if the parameter's domain is dense, the volume of additional aggregated data being returned from the database may be insignificant

    Whether this technique is effective - or even possible - in your scenario will depend on many things: the size, shape and density of your data, the type of indexes present, the volatility of your plan and data caches, the types of operations being performed in the query e.g. sum, count, avg, min, dense_rank etc, to name but a few.

    Personally, when I've experienced dramatic effects of parameter-sniffing, this method has proved successful at consistently achieving acceptable latency for all parameter arguments when my dataset is highly aggregated and can be filtered after aggregation (as per the HAVING clause).

    As in all-things-engineering there is no "right way". Evaluate the impact this method has on your scenarios and decide for yourself.

    That said, thanks again to Adam for his great article!

  • Seems like a lot of extra work to “pretty up” what could be done more simply and efficiently with a standard parameter.

  • Thanks for the step by step instructions.

  • Thanks for the detailed write-up.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • When I download the ZIP and extract I only have CarSales_SSC.bak - no other files for resources.

    When I try to Restore the BAK file into SQL Server 2012, it gives the error:

    System.Data.SQLClient.SQLError: The media family on device 'C:\BIWithSSIS\CarSales_SSC.bak' is incorrectly formed. SQL Server cannot process this media family.

    I re-download and re-extracted with the same results.

    Please advise - I would like to try this out because previously I developed reports to include 'Country' parameter and would like to see this method.

    Thanks

    I re-read - this must have been a SQL Server 2014 database backup.

    Problem solved - we can't install 2014 yet - waiting on CyberSecurity approval.

  • great work!

    I would like to have your image file in the zip.

  • I re-read - I guess you used SQL 2014 to back up the database. 2014 has not been approved by cyber security in our organization.

    Once it's approved - I may come back to this.

    Thanks

  • It seems we are not aware what version of SQL Server is at play here. Thus the error messages some have encountered. Also no images are in the zip file, only the one .bak file others have mentioned.

    ----------------------------------------------------

  • Please post image files if possible. Thanks.

  • Is this possible in SSRS 2008 R2?

  • The resource file has been updated with a new version from Adam.

Viewing 15 posts - 1 through 15 (of 29 total)

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