Adding Slicers to a Reporting Services Report

Question

Post reply

Adding Slicers to a Reporting Services Report

Adam Aspin

SSCarpal Tunnel

Points: 4900
More actions
April 22, 2015 at 10:34 pm

#303215

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

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

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

chezhian 70892 SSC Enthusiast Points: 111 More actions · Answer 1

chezhian 70892

SSC Enthusiast

Points: 111

April 23, 2015 at 1:50 am

#1792942

Hi,

Can you attach the RDL and images in the zip file

Erik van der Aar SSC Veteran Points: 201 More actions · Answer 2

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.

stevenp333 SSC Journeyman Points: 77 More actions · Answer 3

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

Nice to test it all works before publishing.

Andrew Diniz SSCommitted Points: 1852 More actions · Answer 4

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!

gmwmn SSC Journeyman Points: 92 More actions · Answer 5

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

April 23, 2015 at 6:25 am

#1792987

Thanks for the step by step instructions.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Thanks for the detailed write-up.

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

alhildreth SSC Enthusiast Points: 158 More actions · Answer 8

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.

huangji8 SSC Enthusiast Points: 148 More actions · Answer 9

great work!

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

alhildreth SSC Enthusiast Points: 158 More actions · Answer 10

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 11

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.

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