Monday, February 18, 2008

Tabbed Sub-Reports with SRS and SharePoint

Okay...  So the title isn't quite as colorful as the first version, but we'll make up for that with the content.  Consider this a more better, revised, updated, newly interpreted, adjusted, adapted, renovated for your future enjoyment, revisit of the previous post.  This time, I've got lots of pictures.

SRSTabs3

So here's the skinny:  Previously I posted on using SRS and SharePoint (that's the database dependent duo, just in case nobody got that) to create dynamic sub-reports.  Here, I've got a solution that was inspired by my first go-round with this technique.  Except this time I'm using a tabbed interface to present several related sub-reports.

If that was the skinny, I suppose this is the fat:  Quick and clean - here's how to do it...

Assumptions:

  • You are somewhat familiar with SRS and SharePoint - this isn't a tutorial on the basics, there's plenty of that out there.
  • You have a system with access to SRS and SharePoint.
  • SRS is installed in SharePoint integration mode.
  • You will be using Visual Studio.  (Not really required, but that's what my screen shots are of...)
  • You are somewhat familiar with HTML and Cascading Style Sheets (CSS).
  • You know what JavaScript is and, at the very least, seeing it doesn't make you curl up into a fetal position in the corner of the room.

The example depends on a silly little database I created just to have a simple data schema for my purpose.  It's a Family Details database - four tables of completely useless information:

SRSTabs12 

We're working with a collection of 5 very simple reports.  One to act as a search utility and one each to report on the data in each table.  The first screen shot shows you the full layout.  The search report accepts a single parameter that's turned into a search term in the data source query:

SRSTabs7

The basic layout for the report is as simple as could be, but there are two things worthy of note:  First, we have to format the field display to look like a hyperlink, since SRS doesn't do that for us:

SRSTabs9

Second, we need to configure the field to call our JavaScript function.  We'll see the script in a bit, but here's where we configure the call:

SRSTabs6

In this implementation, that's the only place we need to configure anything out of the ordinary in the report.  The rest of the reports are all very similar.  Each accepts a single parameter indicating the ID of the family to report on.  They are not connected in any way within the RDL file or SRS.  All of the connecting magic takes place in our script.  Here's a look at the FamilyDetails report:

SRSTabs8 SRSTabs10

Next we move on to SharePoint (WSS required - MOSS optional).  All of the reports are deployed to the WSS document library configured in the SRS SharePoint integration setup.  For organizational purposes, I created another document library, called Dashboards, to hold the web part page that will become the Family Information dashboard.  I chose the basic Full Page, Vertical web part page template.  I only need two web parts for this implementation.  The first is a SQL Server Reporting Services Report Viewer web part.

image

It's configured to display the search report:

SRSTabs1

A user enters some value in the parameter box and clicks Apply.  This is where we get the report configured with the navigation links (the ones that call our JavaScript - yes...  It's coming...)

SRSTabs2 

The other web part is a Content Editor Web Part.  This is where the JavaScript lives.  But before we go there, there's one more bit we need to make this complete.  The tabs themselves use images as a background.

tabhot100 tab100

One image for the selected (or hot) tab and another image for the unselected tabs.  To make the images easily accessible to the web part, I dropped them here:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES\Ensynch

In WSS, that TEMPLATES folder is accessible through the _layouts virtual directory in any site.

Okay, so this other web part...  Place a Content Editor Web Part under the Report Viewer web part:

SRSTabs11

For clarity, I've broken out the code into sections, here, but it's all in the one web part.  Make sure that when you go to add the code that you click the Source Editor button, not the Rich Text Editor.

The code consists of a bit of style sheet information:

<style> Element:
<style>
  .ensTab100 {    height:25px;
                  width:100px;
                  vertical-align: middle;
                  text-align: center;
                  background-image : ur(/_layouts/images/ensynch/tab100.jpg);
                  color: #ffffff;
                  font-family: Arial;
                  font-size: 12px;
                  font-weight: bold;
                  cursor: pointer; }

  .ensTabHot100 { height:25px;
                  width: 100px;
                  vertical-align: middle;
                  text-align: center;
                  background-image : url(/_layouts/images/ensynch/tabhot100.jpg);
                  color: #000000;
                  font-family: Arial;
                  font-size: 12px;
                  font-weight: bold;
                  cursor: pointer; }
</style>

 

A wrapper <div> that allows us to hide the stuff that has no content until we're ready for it:  (This is strictly for aesthetics.)

Wrapper <div>:
<div id="ReportWrapper" style="visibility: hidden;">

 

An HTML iframe for the main family report: (Note that the src attribute starts out empty and the id attribute is required.)

Main Report <iframe>:
<div style="height: 100px; width: 100%">
  <iframe id="FamilyMain" style="border-style: none; height: 100%; width: 100%" src=""></iframe>
</div>

 

A table structure to hold the tabs: (Note the id, class and onclick event settings - all required.)

Tab <table>:
<table cellpadding="0" cellspacing="1">
  <tr>
    <td id="tab1" class="ensTabHot100" onclick="SetCurrentTab(this.id);">Friends</td>
    <td id="tab2" class="ensTab100" onclick="SetCurrentTab(this.id);">Food</td>
    <td id="tab3" class="ensTab100" onclick="SetCurrentTab(this.id);">Colors</td>
  </tr>
</table>

 

An HTML iframe for the sub-report of the active tab: (Note that the src attribute starts out empty and the id attribute is required.)

Main Report <iframe>:
<div style="height: 100px; width: 100%">
  <iframe id="FamilySub" style="border-style: none; height: 100%; width: 100%" src=""></iframe>
</div>

 

Close the wrapper <div>:

Wrapper </div>:
</div>

 

And, of course, the script that connects it all:

<script> Element:
<script type="text/javascript">
  var currentTab = "tab1";
  var currentID = "";

  function SetFamilyID(famID)
  {
    if (currentID != famID)
    {
      currentID = famID;
      UpdateMain();
    }
  }

  function SetCurrentTab(tabName)
  {
    if (currentTab != tabName)
    {
      document.getElementById(currentTab).className = "ensTab100";
      document.getElementById(tabName).className = "ensTabHot100";
      currentTab = tabName;
      UpdateTabs();
    }
  }

  function UpdateMain()
  {
    if (currentID != "")
    {
      document.getElementById('ReportWrapper').style.visibility = "visible";
      document.getElementById('FamilyMain').src=
        'http://win2k3/reportserver?' +
        'http://win2k3/ilm/reports/FamilyDetails.rdl&' +
        'rs:Command=Render&rc:Toolbar=false&rc:Parameters=false&' +
        'FamilyID=' + currentID;     
      UpdateTabs();
    }
  }

  function UpdateTabs()
  {
    var report = "";
    if (currentID != "")
    {
      switch(currentTab)
      {
        case "tab1":
          report = "FamilyFriends.rdl"
          break;
         case "tab2":
          report = "FamilyFood.rdl"
          break;
         case "tab3":
          report = "FamilyColors.rdl"
          break;
      }

      if (report != "")
      {
        document.getElementById('FamilySub').src=
          'http://win2k3/reportserver?' +
          'http://win2k3/ilm/reports/' + report + '&' +
          'rs:Command=Render&rc:Toolbar=false&rc:Parameters=false&' +
          'FamilyID=' + currentID;
      }
    }
  }
</script>

 

Here's a basic rundown of how the plumbing works:

  • The script sets up a couple of global variables:
    • currentTab - hold the id of the current tab.  Defaults to "tab1".
    • currentID - holds the ID of the family that was selected in the search report.
  • A user clicks on a link presented by the search report, calling SetFamilyID() and passing in the ID.
  • SetFamilyID stores that value in a global variable (currentID) and then calls UpdateMain().
  • UpdateMain, assuming that there is a value in currentID:
    • Makes sure that the wrapper <div> is visible
    • Sets the proper src value for the FamilyMain iframe (including the ID parameter).
    • Calls UpdateTabs();
  • UpdateTabs() then proceeds to:
    • Determine which report to run based on the value in currentTab.
    • Set the proper src value for the FamilySub iframe (including the ID parameter).
  • When a user click on one of the tabs, a call is made to SetCurrentTab, passing in the id of the tab that was clicked on.
  • SetCurrentTab(), assuming that the clicked tab is not already the current tab:
    • Sets the current tab class to ensTab100 so it's no longer "hot".
    • Sets the new tab class to ensTabHot100 so it has the selected appearance.
    • Saves the new tab value into currentTab.
    • Calls UpdateTabs() to get the new report displayed.
  • When a user clicks a new value in the search report (yes, it's still there) - it all starts over again.

SRSTabs3 SRSTabs4 SRSTabs5

Use it in health!  (Well, use it in SharePoint, but you know what I mean...)

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;
  }
</script>

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