SQL Reporting Services parameter memory

The great thing about Google is the way you can find a way to do just about anything.

I’ve been using SSRS for quite a time and haven’t had this request, but as people expect things to be so much more intuitive these days, it seems logical for a reporting system to remember what they typed last time.

I found I had to combine techniques from a couple of sites as this one says it will only cope with up to 32 entries and I had a few thousand to handle.

I’m selecting from an account number list and originally this used a long human readable code rather than a foreign key. This burst the size of reporting parameter passing and the SQL string limit. Luckily, I could painlessly convert to the unique ID.

So, I claim no original work in this, I used this link to get the basic logic functions to work.

http://www.purplefrogsystems.com/blog/2008/01/report-parameter-selection-memory/

I found the spit function on another site. The guy claimed to have written it 6 years ago.

http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql

I opted for creating a separate database to store the reporting memory. It’s a third party app and they might get upset if they found new tables appearing. I toyed with the idea of adding database and server name into the options, but decided it wasn’t a problem today and might never be.

The only issue here was the dim gotcha that the split function I’d opted to use had the parameters the other way around to purple frog’s. Took a few minutes of head scratching on that one Doh!

Works like a charm.

Thanks to the two people who did all of the hard work.