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.

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:

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:

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:

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:

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:

   

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.

It's configured to display the search report:

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

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.

   

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:

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.

       

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