Tuesday, February 12, 2008

Database Dependent Duo Delivers Dazzling Dynamic Data Display

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.)

Well, here's how you do it using Windows SharePoint Services (WSS) and SQL Reporting Services (SRS)...  (If you're a developer, and  you look at .Net 2.0 Web Parts and such, you'll probably realize that WSS is not necessarily a required piece, here, but it sure helps with the presentation...  And it's free!)  We'll also need some HTML and a bit of JavaScript, but I'll give you most of that.  (I am going to assume that you know your way around the basics of WSS and SRS - if not, then you'll probably have to do a bit of reading up on that before you can implement this stuff.)

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.

The first web part to add is the Report Viewer Web Part.  Configure this guy to display the parent report - the one with the customer detail and embedded orders list.  Below that, add a Content Editor Web Part.  This is where a good part of the magic takes place.  This Web Part is going to contain two important elements: An HTML iframe definition and a short JavaScript function to update the source attribute of the iframe.  Make sure that when you edit the Content Editor Web Part, you use the Source editor, not the Rich Text editor.  Here's what the contents of this Web Part will look like:

Content Editor JavaScript:
<script type="text/javascript">
  function LoadOrderDetails(OrderNum)
    document.getElementById('OrderDetail').src =
    'http://win2k3/reportserver?http://win2k3/ilm/reports/' +
    'OrderDetail.rdl&rs:Command=Render&rc:Toolbar=false' +
    '&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.)

A quick overview of what this code does...  It exposes a JavaScript function to the page.  In this case, the function is called LoadOrderDetails().  The function accepts a single parameter which, in our example is the order number for the order we need to display details about.  We take this information and embed it into a URL that we assign to the src attribute of the iframe.  The document.getElementById() bit is how we get a reference to the iframe.  If you examine the URL that's being generated, you'll see that it points to the reportserver virtual directory, and passes along the URL to the report that we want displayed.  That second URL has a series of parameters embedded into it.  Most of them tell SRS how to display the report: basically, render it, hide the toolbar and hide the parameters window.  The last item passed in the URL is the name of the parameter as defined in the report that we're calling.  And we concatenate the value passed into the function as the value for that parameter.  In our example, our OrderDetails report defines a parameter called "OrderNum".  If we didn't include this in the URL, the report would expect to have the user type it in as they do in the parent report.  But since we're hiding the parameters window and the whole point of this exercise if to eliminate the need for manually linking the reports, we include it as part of the call to display the report.

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.

At this point, we have two pieces of the puzzle assembled.  We have a report that displays the customer detail, along with a list of orders for that customer.  We also have an iframe poised to display a specific report at the whim of a JavaScript function call.  All we need to do now is connect them so the order details report displays the details of any order the viewer clicks on in the parent report.

This final step is to configure the parent report to call the JavaScript function which updates the iframe source.

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:

SRS Textbox Navigation Settings

As you can see, on the navigation tab, you enter the call to the JavaScript function in the "Jump to URL:" box.  The code there inserts the value of the OrderNum field of the report's datasource.  When this is rendered in html as the report displays, it become a client side call to the JavaScript function we defined in the in the Content Editor Web Part in SharePoint.

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.
  • Configure a Content Editor Web Part with the appropriate JavaScript and iframe definition.
  • Configure the appropriate navigation property in the parent report to call the JavaScript function.

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:

  1. Configuring the textbox to make the JavaScript call does not mean it will appear as a hyperlink when rendered.  In the report definition, you'll have to format it in a way that catches the viewer's eye and let's them know they can click on it.  (I typically go for the standard bright blue, underlined text.)
  2. Setting this up creates a tightly bound relationship between these elements.  You couldn't use this parent report anywhere that you don't also supply the JavaScript that it's expecting to call.  (At least not without getting browser side errors.)
  3. 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".
  4. You don't have to stop at one level of detail.  For our ILM portal, I use a "search box" report to get a list of metaverse identities that match a search string.  Click one to get details on the identity, including connector history and, in our case, a list of workflows that are associated with it.  Click a workflow id and you get a rendered image of the current status of the workflow in another panel.  Now, since the workflow image report is actually being called from within the iframe that the identity detail is displayed in, you have to scope the JavaScript call: ="javascript:parent.LoadOrderDetails()" (and if you want another level of detail you may find yourself calling a parent of a parent...

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.


Anonymous said...

It was extremely interesting for me to read the blog. Thanx for it. I like such topics and everything that is connected to this matter. I definitely want to read more soon.

Anonymous said...

It is extremely interesting for me to read this post. Thanx for it. I like such topics and everything that is connected to this matter. I would like to read more on that blog soon.