Pardon the bad pun in the title; it's from yet another movie that I really love.
How could I fail to love a movie that includes the memorable line
"You can put a cat in the oven, but that don't make it a biscuit"?
I beg you to think of the software implications of that statement…
Additional irrelevant cinema information, before we get down to business: C & I saw Star Trek this weekend. When Tennessee Williams said "If people behaved in the way nations do, they would all be put in straitjackets", he was exempting most plot-required behavior of movie villians, and of some of the heroes as well.
Nevertheless, if Spock can stick his logic-meter into the secret pocket of his wallet for a couple of hours, along with [oops! possible spoiler in this paragraph] what most other young men probably stash there, then we can too.
This was the most fun I've had at a movie in a long time. Go thou, and soak yourself in the pleasure of meeting "your" fave characters once again.
Moving between report contexts
I'm working with a client who's defining some reporting architecture with interesting constraints. One of the subjects that has come up is the best way to shift focus between different result sets within a report. For example, a one-to-many relationship in a form or a report has a "classic" display method in most environments; what's the best way to do it in Reporting Services?
Depending on what you need, there are several answers to that question in-the-box. But if you are working in an ASP.NET context to start with, I think it is worth thinking about the "Navigation" or "Action" properties of report items, and how you can get creative with them.
What we're talking about here pretty easy and will apply to both SQL 2005 and 2008 Reporting Services equally. One caveat: Report Builder 1.0 can't do some of these tricks, so for Ad-Hoc reports you're more limited in SQL RS 2005.
Standard subspace jumps
Reporting Services offers you "bookmarks" within a report layout, so if you have a "one" and a "many" dataset in the same report you can bring the user back and forth between appropriate places in the layout without much trouble.
You just need to use expressions for the bookmarks that include the appropriate key values, plus some kind of prefix to help you understand what you are doing when you reference the bookmark from another report object, for example: ="City_" & Fields!CityCode.Value.
I find this method difficult to incorporate into a user-defined layout, however, especially if the report data hasn't been denormalized into a single, vast result set already. I also find the "document map" method of handling context navigation clumsy. But they're both worth mentioning, because they are properly part of the full navigational (or "action", in 2008) set of features available to us, so you may see them when you're working on the other methods we'll discuss here. Also, the various in-the-box renderers do have some notion of how to use them if, like Excel, they have some native analog to HTML jump capability.
You may see them… in 2005, not so obviously in 2008. FWIW setting the bookmark or documentmap properties of a report item is a little more difficult to find in the RS 2008 Designer/Builder, but they are there. Go to the Properties window with the report item in question selected (the line, textbox, whatever — not its row or data region) and look under "Other". Guess I'm not the only one who didn't use these very much.
Using subreports is actually the most convenient way to change your reporting context — assuming you have a reporting server available. In the example below, using my beloved MySQL World tutorial database, you can see a row linking a City subreport to a Country report by the country code. To filter the cities properly, I'm passing the current Country code value to the subreport.
Typically, if the City report should also stand alone and report on all cities, you're going to set the CountryCode parameter in that report something like this:
- Hide this parameter, unless it is appropriate for your subreport to navigate to other "parents" — which it can be, and they can actually reference back "up" to that other parent if you wish.
- Allow a null value as the value of the parameter
- Set your query or web service up to do the equivalent of the following:
SELECT *
FROM City
WHERE
(@CountryCode IS NULL) OR
(CountryCode = @CountryCode) -
If there are columns such as country name or code that will be redundant in the City report when it is functioning as a subreport, simply set its Hidden property in this way, so that you see them in standalone mode but they disappear when you don't need them: =Not IsNothing(Parameters!CountryCode.Value).
That's the whole deal. And, allthough a little hard to manage, in certain conditions, using nested data regions works well also.
But the "navigation" or "action" features let you design the jump yourselves. And I think sometimes they meet people's expectations just a bit better.
Warp-factored jumps
Try using a Jump to a report instead. ("Navigate to a report", in 2005, "Action, go to report" in 2008). The first thing you'll notice is that you can use an expression, and of course that includes custom code. The screenshot below incorporates a simple of example of why you might want to — here, I'm using 2005:
As you can also see in the screenshot, you have the same opportunity to pass along parameters. Here, you see the "Omit" option; do you want this value to be passed to the value in the parameter with an equivalent name in the report, or not? Most of the time, the default value of False is probably what you'll want.
Hyperspace jumps
But wait, there's more. We can also jump to a URL, rather than a report, and of course you can still use an expression to generate the proper URL — so you can still feed parameters directly to a report on the report server, if you would like to (using URL Access).
But you can also jump to another page in your app, perhaps with a reportviewer control on it, or a page that lets the user drill down to edit the contents of that line in your report.
Or you can jump to something else entirely. You can even jump to a page that manages the current report, if the current user has privileges to do so, and use expressions for both the jump and visibility to hide the feature from the user if s/he doesn't. For example, a link that says "click here to go to the Subscriptions page for this report" might have the following expression underneath:
="http://localhost/Reports/Pages/Report.aspx?ItemPath=" &
Globals!ReportFolder & "/" &
Globals!ReportName &
"&SelectedTabId=SubscriptionsTab"
… life can get pretty interesting, and I'm not using the Serenity definition of "interesting" here.
Oh, one more thing. With all these interesting capabilities, you may want to have the link open a new window (which you'll define with appropriate window attributes). I'm not sure how this part will translate to your various renderered versions of the output, but for the HTML displayed version, you do it like this:
="javascript:void window.open (
'https://spacefold.com/lisa',
'mywindow',
'status=1,height=200,width=200');"
The "mywindow" argument there names the window you intend to use, which gives you more control over adding more code to re-use and position the new window, of course.
Whitespace jumps?
Any report item — a line, a completely empty text box — can handle navigation for you. You could conceivably underlay your report data with a white rectangle in the layout so that clicking anywhere on the "empty surface" of the page caused some behavior, not that I've tried this.
At least in 2005, you're limited to putting an "action" on the entire report item, you can't create clickable areas within one report pseudo-object.
RS 2008 offers you the interesting possibility of HTML placeholders, with which I haven't played much, but it appears that you can indeed surmount this limitation there (whether they'll translate properly into the Excel rendition, I suppose, is another story). You could potentially set up custom code to do all kinds of stuff in there.
In the example above, I've succeeded in using the javascript method of opening a new window from within the 2008 placeholder code.
That's not required, of course, but it was of interest to me to find out if the expression could work this way, when required. Here's the expression I used; note the encoded quotation marks, which exist in the original code (they're not an error in this post):
="this is a <a href='javascript:void window.open
("https://spacefold.com/lisa",
"mywindow",
"status=1,height=200px,width=200px;");'>
test</a>
of a placeholder."
BTW: the linking behavior may not appear to work in Report Preview, you may have to deploy it to a report server before you can see it.
Note that I haven't succeeded (yet) in getting a title or alt attribute to work within the placeholder, perhaps because its external tooltip element is interfering — so what you see in the screenshot as a tooltip is the external property for placeholders at work.
I haven't even gotten the HTML tag-handling behavior of placeholders to work in debug mode (the separate viewer outside Studio) but of course I might be doing something wrong.
Where no-one can go before you
If subreports are so convenient, what's the problem? Why go through all this? Why bother?
Well… subreports kinda don't work in local-mode for ReportViewer, unless you want to grab events, and they definitely don't work with Ad-hoc reports that have access to report data, but not access to a report server.
And now you know how I got to thinking about using jumps, instead, in the first place.
Set your imagination free, in some parallel universe, where Romulans wear the coolest clothes (but didn't they always?). Think about what they can do for you.
Mr. Sulu, maneuvering thrusters, please. Steady as she goes…