Decision Support Services Report Catalog

Two-sentence description

I designed and wrote a searchable or browsable catalog to facilitate the location of reports within the UW’s enterprise data warehouse. And I gave it a bit more pizzaz than just a table full of metadata.

Background

The UW is in the midst of creating a massive data warehouse to house information about finances, academics, administrative, and human resources all across campus. Microsoft’s Reporting Services was chosen as the interface into all this data, and departments began developing reports to replace many older, less functional tools. It’s an ambitious and important initiative, and it needed a front-end site: a place to consolidate information about getting access to the reports, how developers can write them, and give context to the data they provide. And of course, provide a list of the reports themselves.

I served as one of the eight committee members tasked with developing a website to meet these needs. For nearly a year, the committee performed a needs analysis for the site’s audience, gathered information about the data custodians and the reports they were writing, and crafted policies regarding campus access to and use of the reports. The process was involved but in late April, 2010, the University of Washington Decision Support website was launched.

Another developer was responsible for the site’s structure; I provided some CSS help but did not build the site itself. Instead, I wrote the Report Catalog. Initially, the mock-up for the report catalog was simply a table with column after column of information: report title, subtitle, link to the actual EDW report, data custodians, etc. It was not a very usable presentation of the data, so I presented a more sophisticated structure: the title, subtitle and the link to the report—the most important information for most users—is shown, with a link for the less-frequently accessed information.

The committee was split on how users would prefer to find the reports they wanted: by searching, or by browsing a hierarchy? Fortunately, the web service developed by UW Information Technology actually enabled me to provide both.

Skills and technologies

There were three technology facets to this project:

1. Querying the report catalog web services, which provided yet another opportunity to re-purpose existing PHP code I’d written previously to access the XHTML payloads returned by the UW’s Student Web Services.
2. Displaying the reports resulting from the user’s search, which I described above. Clicking a report’s name invoked a call to jQuery’s .post function to load the additional report information, which then populates a slide-down panel.

3. 
   

   

   The folder hierarchy can also display reports, but the committee decided to keep them hidden. Clicking a folder name displays all reports within that folder, including sub-folders.

   
   

   Creating the dual browse-and-search approach to finding reports. The search form was easy, and another AJAX call allowed me to load the results without refreshing the page. Creating the browsable folder hierarchy was more complicated and involved every first-year computer science student’s nightmare: recursion! The web service returned a list of all reports and subfolders of reports contained within a given folder in the Enterprise Data Warehouse, so my function started at the root and traversed the subfolders until I’d created an mulit-level unordered list in HTML of reports. The excellent jQuery UI framework provided an accessible way to transform that into the familiar folder hierarchy a lá Microsoft’s Windows Explorer. jQuery UI also gave me a nice calendar-based date picker and the tabs that let me cram in both options into the same space. The “Search” and “Browse” tabs themselves created a problem while solving another: which to show by default? I resolved this internal committee debate by using a cookie to remember which tab was showing during the visitor’s last visit to the Report Catalog: now that’s compromise!

Take aways

Working as part of this committee showed me both the good and the bad of such an arrangement. On the positive side, not being responsible for the entire site’s content, nor its overall aesthetic, allowed me to focus on the design and implementation of the catalog. On the negative side, hours and hours were spent in meetings discussing aspects of the site that we not directly relevant. 

From a technology perspective, this project allowed me to solve real business needs with flexible tools like jQuery, jQuery UI, and web services. More importantly, they allowed me to adapt the site to shifting requirements that were a result of the “development by committee” approach.