Walkthrough: Rolling your own script in SSRS

Tom wrote to ask how to use javascript in an RDL; his use case is collapsing and expanding groups.

As you must know, collapse-expand for groups is a native RDL feature, if you use the “toggle item” capability.  Tom thinks this isn’t efficient for his report, because the postback takes a long time.  I’m not sure whether this is a network issue, or some inefficiency in the overall report design, and I’m definitely not sure that javascript will be any faster (it depends: how many rows need to be hidden and made visible on every action?).

But I had seen an interesting post on javascript injection for RDLs, and thought I would give it a shot.

Two additional, quick notes on the native feature:

• Tom was surprised that the native collapse-expand feature does a postback. I’m not surprised.  A lot of people treat collapse-expand like a better form of drill-down; they start with the groups all closed up and they don’t bring back all the data unless they need it.  If javascript were the collapse-expand mechanism, all the data for all the groups would have to be send up-front.  Not so good.

• Have you used the native collapse-expand mechanism, started with the groups closed up, and not been able to fix the toggle icon to behave the way you want?  There’s a trick to this.  I’m not sure if it is a general issue, or just something I find unintuitive, so give me a shout-out if you’d like this explained.

Where I got the original idea

I found the original post that I recommended to Tom here. I’d never had any reason or opportunity to want to do this.  Still, it seemed to me that the author was showing a great trick: create an HTML placeholder that respected HTML markup, with an expression embedding some evaluated script that would write the URL for your .js into the header of the page.

The post is on an “InfoSupport” blog, but it looks like this specific author has moved on. He might be this guy.

Anyway… After I gave Tom the reference, I decided to play around with this myself, using Tom’s requirement as the test.  As it turned out, much as I liked the idea, it needed some improvement to work as we might expect.  As a result, I’m writing up what I did here, both for Tom’s use and yours.

The first improvement I made in what you’ll see in the original blog post is minor: to really write script like that, don’t mess around with a single string value, as the author did (cautioning, as he did so, that you need to remove all the spaces to be sure it would work!).  Break it up so you can read, and debug it.  You’re definitely going to need to debug it.  You’ll see how I broke it out in a screenshot, below.

The second improvement is definitely not minor for Tom’s use case.  As you’ll see if you read the original post, he injects the javascript in one link and then you have to click another link to invoke the script you injected.  In a “collapse/expand” scenario, this doesn’t make sense.  You want to click and see the action, once, period. This proved to be a tricky thing to fix, but I think you’ll like the result.

The third improvement is one I didn’t need, but I will suggest to you anyway: the original trick is going to put multiple references to the same script into your header.  If your script is lengthy (say, jquery), you can use my adapted version to reference only a small loader script, which then checks to see whether the big script is already loaded before attempting to load it again. 

While this is a really good idea in some circumstances, it might put you back into the original requirement to have two different click actions, unless you don’t mind that the very first click serve to load the larger script, but it won’t perform the “real” action afterwards, because the newly-loaded script won’t be referenceable at that point.  After the first click, everything proceeds as planned. The user might never notice this — or, in some situations, it might be entirely appropriate to separate the two actions.

Ready?

The report I’m using in this example is a really simple example report I’ve used for a previous blog post.  It really didn’t matter what report I used, as long as it was something that had an appropriate group that somebody might want to collapse/expand.  In this case  I’m grouping Cities within Countries.

Keep in mind that our javascript is looking at the generated HTML in your report, so some of the code I’ll use below should be adjusted for your layout.  Nevertheless, I’ve tried to make it as generic as possible for the collapse-expand use case, and I’ve tested in FireFox and IE, so I hope it’s largely usable for you.

Step 1: Adjust your report group header expression.

Since my original report group header caption supplied the name of the Country, I’ll adjust what actually displays to a prefix consisting of either “+” or “-“, following by a single space, followed by the Country name. 

You can use whatever convention you want, but your javascript has to be able to telll, first, what elements are headers (as opposed to detail lines) and, second, what state each header’s detail group is currently in (collapsed or expanded). 

As you’ll learn from the original post, you also need to make sure that HTML tags are interpreted as styles.

Turning to the actual expression used for this caption, I’m following the original method, except for two small changes.  First, as explained earlier, I’m breaking up the expression into separate strings for legibility:

I’ll repeat that expression below, to make sure you can read it properly, but also to highlight the second, and most significant change I’ve made to the expression:

=  “<a href=” & CHR(34) & 
 “javascript:eval(unescape” &
 “(‘function addScript(scriptFile)”  &
 “{var head = document.getElementsByTagName(\’head\’)[0];” &
 “var script = document.createElement(\’script\’);” &
 “script.setAttribute(\’type\’, \’text/javascript\’);” &
 “script.setAttribute(\’src\’, scriptFile);” &
 “head.appendChild(script);” &
 “}” &
 “var tObj = \'”& Fields!Country.Value.ToString().Trim() & “\’;” &
 “addScript(\’http://localhost/test/test3.js\’); ‘)” &
 “);” & CHR(34) & “>- “& Fields!Country.Value.ToString().Trim() & “</a>”
 

The highlighted line of code, as you can see, creates a variable and assigns it the name of the country, before adding the script reference.  What does that do for us?

Quite a lot, actually.  That’s where my superior trick comes in, and it makes all the difference between needing to invoke the referenced script in a second action, and being able to use it right away.

Step 2: Writing the javascript referenced in the report

Turning to the .js file, you’ll find that it has a global piece of code, not contained in a function at all — which will be evaluated as soon as the script is loaded:

 
toggle(tObj);
 

… and of course this single line of code is followed by an appropriate “toggle” function.  I’m providing the full script here, which includes a lot of comments, plus the directions for using this injected script as a pre-loader for loading additional, more lengthy, scripts, including appropriate checks for only loading them once.

 test3.js.txt (4.03 kb)

This really works and I think my javascript is kind of cool, even though it has to include the usual bonehead checks to handle cross-browser DOM object model incompatibilities.  Do remember to look at the source for your particular report, if you find my method of drilling into TD elements doesn’t work with your layout.

If you think you have a better way to pass the “which group are we adjusting” information from the RDL to the script, feel free to give it a try.  But keep in mind that we don’t have a “this” reference to the current anchor, or other niceties, and we can’t add an id attribute into the <a> element we added to our expression, either.   When we’re evaluating the script, we’re not actually on the anchor, which rules out the former, and SSRS won’t let us do the latter, it seems.

A quick note on testing

A number of people asked about testing this technique, in questions to the original author.  While it’s true that you can’t test it directly in BIDS’ preview, because the clicks won’t actually go anywhere, you don’t necessarily need to deploy to a report server to see this work.  You can Save As MHTML, and then open in Internet Explorer. 

Be sure to tell IE to allow script to be executed from your local MHTML file (why are files on the local disk with script treated more stringently than files coming from a web server? I’ve never understood that). 

To test in other browsers besides Internet Explorer, you can then tell IE to Save As a complete web file (HTML), and load that additional local file to the other browsers.  If your script is at all complex, you will want to do this, because IE has the lousiest debugging facilities of the lot.

And that’s all she wrote.

I could try to show you a screenshot of the result but… it just works.  It doesn’t seem special at all, never mind that it was a big deal to figure it out.   Well, maybe just one screenshot for the road, but honestly there’s nothing to see.

Whaddya think? Not sure it will really be faster at runtime for Tom’s report, but there it is.