How many of you out there - raise your hands - have databases with more than one table? Now, how many of you - raise your hands, again - actually raised your hands? You know I can't actually see you, right? (And in case you answered that out loud... I can't hear you, either.)
I do a lot of work with Microsoft's Identity Lifecycle Manager and I'm quite privileged to do much of that work with one, Brad Turner - Identity Management MVP and all around nice guy. (Funny looking, but nice.) Brad has developed a series of SQL Server Reporting Services based reports for viewing the status and history of the identity data and management agent processing for your ILM (MIIS) system. Over the course of several implementations, we have used these reports as the foundation for developing an ILM Management Portal... We are co-presenting on this topic at the upcoming DEC 2008 conference in Chicago.
But, this is not the story of our ILM portal. This is the story of a nifty little technique I developed in order to make the data we present in our portal a bit more dynamic. So, the only reason I bring up the history is to brag a bit about our awesome portal solution and our upcoming DEC presentation. The rest of this is best illustrated with a simple example...
Let's go stereotypes... You're a business. You have data. You have data about your products. You have data about your customers. You have data about the products your customers order. It's probably safe to assume that you might want to view data about a particular customer with a list of their orders and the ability to see the product details of each order. But if you are using SQL Reporting Services, there's no clean way to do this... Yes, you can create sub-reports. You can even create reports that let you drill down into the details of a child record, but with SRS that typically means the child detail report replaces the parent, leaving just a link to navigate back up the tree. But what you really want is to be able to click on an order and see the details on the same page. (This is for an online reporting solution... I'm not suggesting that you'll be able to click on a line in a printed report and have the data change. Just want to be clear about that.)
The basics: We have a system with WSS installed and SRS installed in SharePoint integration mode. (This gives us a handy little Report Viewer Web Part that we use as the anchor for our dynamic report.) We also have a few reports:
- Customer Detail - Displays information about our customer: Name, address, customer number and so forth.
- Customer Orders - Displays a list of orders for a particular customer.
- Order Detail - Displays the details of a particular order.
Our desired end result for this example is a two paneled report. The upper panel showing the customer detail, along with a list of their orders, each displayed as a hyperlink. The lower panel showing the detail of any order that you click on.
The first step is to create the parent report for the upper panel. (This is not a tutorial on creating SRS reports, there're plenty of them out there. I'm just giving you the basic chunks of what needs to happen.) This is actually two reports. You create a customer detail report and embed a customer orders report within it. You can develop and test this within Visual Studio. You'll most likely have the customer detail report accept the customer name or number as a parameter, then pass that along to the embedded orders report. (We're going to add some funky navigation to the orders report, but we'll come back to that.)
Next, create an order details report that accepts an order number as a parameter.
Now, let's move to WSS... As part of the SRS / SharePoint integration, you'll already have a document library for your reports - configure this doc lib as your deployment target in Visual Studio. We need a nice new Web Part page to act as the canvas for our dynamic report. For our project, I created a new document library called Dashboards that I used to collect these pages. I used the Web Part template that just has a single column of web parts the full width of the page.
'&rc:Parameters=false&OrderNum=' + OrderNum;
<iframe name="OrderDetail" scrolling="no" id="OrderDetail" src="" width="100%"></iframe>
(Obviously you'll have to tweak that a bit - use URLs appropriate to your setup. You might have to play with the URL format a bit ,too, depending on where your SRS virtual directories get placed.)
The iframe definition is just below the script block. Make sure that your tokens match up! If you make a call to document.getElementById('OrderDetail') then make sure you assign "OrderDetail" as the value for the id attribute of the iframe.
In the report definition of the customer order report (that's the one embedded into the parent report), right click on the textbox that displays the order number itself and choose properties. you'll get a window that looks a bit like this:
See? ... It's a piece of cake! Now each of the order numbers is a hyperlink that dynamically updates a sub-report displayed on the same html page.
So let's go over the basic steps:
- Define a parent report in SRS that lists a collection of related values. (Customer Detail, Listing Order Numbers)
- Define a child report that accepts a parameter to display further detail of an item. (Customer Order, Accepts Order Number)
- Create a Web Part page to host the cooperative reports. (I like that... Cooperative Reports. I have coined a new term.)
- Configure a standard SRS Report Viewer Web Part to display the parent report.
I truly hope that folks find this useful. I've seen quite a few inquiries on many forums about how to do something like this. Many people are surprised (as I was) to discover that SRS does not have this capability. But, then, it's a reporting engine, not a UI driven application.
Here are a couple of things to note about this technique:
- The parent report does not have to be very complex and it does not have to have an embedded sub-report. In one instance, I implemented a parent that does nothing but lets the viewer search for users in a directory with a search string. The resulting list of users are clickable links that update a user detail panel below the "search box".
Well, I certainly think that's quite enough to try and wrap your brain around for one sitting... Please let me know if you have success (or not) with this technique. And if you can add any more interesting angles, I'd love to see what you come up with.