Modern Web Reporting

Using XSL and Test First Development to Take Enterprise Reporting to the Next Level

asp:CaseStudy

 

Modern Web Reporting

Using XSL and Test First Development to Take Enterprise Reporting to the Next Level

 

By Doug Wallace and Joel Aufgang

 

Equity One (http://www.equityone.net) is a publicly traded real estate investment trust that principally acquires, develops, and manages neighborhood and community shopping centers around the United States. Early in 2004, Equity One approached Agile.Net (http://www.agile.net) and asked us to help them create a solution that would allow their staff, which is geographically dispersed at locations throughout the country, to generate and retrieve reports from the centrally located Oracle JD Edwards EnterpriseOne ERP (enterprise resource planning) software at their head office in Miami, Florida.

 

In an effort to avoid both prohibitive costs and logistical complications, Equity One desired a solution that did not rely on acquiring additional ERP licenses, installing new software to desktop machines, or delivering large amounts of new training. To maximize flexibility and ease-of-use, they also required the ability to save report criteria and parameters for future re-runs, and that multiple output formats be offered to their users. Additionally, as a public company, Equity One needed to ensure the highest levels of security and correctness. Equity One complies with Sarbanes Oxley, which calls for well-documented processes. Therefore, this added a requirement for transparency in the creation and modification of the intended system.

 

Although daunting, these requirements were tailor-made for .NET and XML. The Agile team decided to use Microsoft Visual Studio.NET, the Altova XMLSpy XML development environment, and the Altova StyleVision report and stylesheet designer as our primary development tools for the project (http://www.altova.com).

 

Test First Development

The new Equity One reporting framework allows selecting parameters to report on, saving and reloading the selection made, and saving and reloading the report results. Both the query and the results are saved in the database in XML format. For a query object, we are using the ISerializable interface to generate the XML data and also to re-load the query object back from XML. The trickiest part of this front-end work is mapping the correct fields from the ERP database, so getting the initial mappings correct was of critical importance. Therefore, we decided to use the process of Test First Development to ensure accuracy.

 

As soon as we received specifications for the report and the mappings, we created unit tests to make sure the code we wrote would be correct and testable. We used an internally developed unit test framework that was created for use within all of our Web applications. This framework exactly met our needs for ease of use and flexibility. Unlike most testing applications currently available on the Internet, our framework can be directly integrated with the Web application that requires testing. The unit test sets are added to the page where the code is written and test results are accessible on a RunTests.apx page that can be made part of a special section of the Web application. The page listing the RunTests.apx pages can be made accessible to developers and to end users as needed.

 

After the field to parameter mapping is understood, the API exposed for pulling a report is straightforward. The code fragment in Example 1 illustrates how the client side of the reporting tool requests an IncomeReport object populated from the database. Notice that these methods are created as static members in order to be accessible when no instance of the class has been created. The test methods are declared using a TestCase attribute. The information from the attribute can later be obtained by using Reflection.

 

#region Tests

   

public static IncomeReport CreateIncomeReport()

{

     IncomeReport report = new IncomeReport();

 report.Query = IncomeQuery.CreateIncomeQuery();

 return report;

}

[TestCase]

public static void TestReportExists(CodeTestSet testSet)

{

 IncomeReport report = CreateIncomeReport();

 string reportXML = report.Execute();

 testSet.Assert( "Report Exists", reportXML != string.Empty );

#endregion

Example 1

 

Allow for Multiple Formats

As in most reporting work, it is important that each report be available online, in one or more print-friendly formats, and in an analysis format in this case, Excel. After a user determines which report to run, he or she can choose to display the report results on the screen (allowing the user to save the results), as Web pages, in PDF documents, or as Excel files. To achieve this, we used the Altova StyleVision stylesheet designer to visually create in one single step XSL (Extensible Stylesheet Language) stylesheets for all the reporting formats. The XSL file is structured to perform conditional formatting on the XML report, depending on a format parameter, which can have the values web, print, or excel. This parameter is passed to the XSL file by the application when a user chooses to view a report in a particular format. When a report is to be sent to Excel, it is simply formatted as a simplified HTML table (which Excel can import into its grid) and sent to the browser using MIME type application/vnd.ms-excel. PDF reports are also formatted as HTML and then printed to PDF on the server before being passed to the client.

 

To control who can view which reports, we could have created a system for user management, but for efficiency of management s sake, we opted instead to use the existing Microsoft Active Directory. This gave our client confidence that as the company s organization and personnel changes, the correct people will see the correct reports.

 

The application object model uses three base classes that provide baseline functionality: ReportQuery.cs, Report.cs, and ReportForm.cs. Every report has three corresponding classes that inherit from these. Because the base classes contain the majority of the infrastructure code required by a report, the task of subclassing to create a new report is not difficult.

 

ReportQuery.cs is the base class that stores the parameters that define a given report. This class uses serialization to generate the XML query that is stored in the database when the user decides to save the selections for future use and also to load the object from a previously generated XML file when the user decides to reload the previously saved selections. The XML data that is generated by the user and saved for future use is stored in the database.

 

Report.cs executes a report. Here, the abstract Execute method is overridden to generate the results for a particular report. We used a DataSet object to extract the results from the database. With the DataSet in hand, we iterated to build the resulting XML data in the required format. Then, the XML data is displayed using an XSL stylesheet. Naturally, there is a set of stylesheets for each report providing the design information necessary to render a specific report in each display format.

 

ReportForm.cs inherits from System.Web.UI.UserControl and is the base class for all the UserControl classes of the reports. In Example 2, we define the interface for the report parameters selection page. The Reports.aspx page contains the common interface for all the reports and uses a PlaceHolder control to programmatically load the selection page for different reports.

 

protected System.Web.UI.WebControls.PlaceHolder ReportForm;

private Common.Reports.ReportForm reportForm;

private string ReportName

{

 get

 {

   if( Request.PathInfo.Length > 0 )

     return Request.PathInfo.Remove( 0, 1 );

   else return string.Empty;

 }

}

private string ReportFormPath

{

 get

     {

     return string.Format( "{0}/{0}.ascx", ReportName );

     }

}

private void Page_Load( object sender, System.EventArgs e )

{

     reportForm = (Common.Reports.ReportForm) LoadControl( ReportFormPath );

 ReportForm.Controls.Add( reportForm );

}

Example 2

 

After the user selects the parameters for a report, the application executes the report in a new thread and the user is redirected to the SavedReports.aspx page. The SavedReports.aspx page provides the user with access to previously saved reports, reports run in the current session, and the currently executing report. He or she also has the option to share saved reports with other users in the same group. Shared reports are also displayed on the SavedReports.aspx page.

 

Separate Content and Presentation

Because a reporting tool is all about providing end users with information in a format they can understand and use, the heart of the application is in how the stylesheets are created and managed. XSL programmers have the sometimes-difficult task of taking the raw report data and delivering it as well formatted reports for end users. As mentioned above, we chose to simplify this work by leveraging Altova StyleVision s ability to visually generate synchronized stylesheets.

 

The first step in creating an Equity One report was creating a sample XML file. We used Altova XMLSpy to handle this multistep task. Sample XML files were created based on report specifications that we received from Equity One. Because designing a stylesheet requires an input structure an XML Schema, DTD, or relational database and our specification was in XML, we generated an XML Schema from that file using XMLSpy s built-in Generate DTD/Schema Tool. Then, an application developer used the XML file and Schema as a reference to build the sample report using the actual data from the database. Next, we used XSLT (Extensible Stylesheet Language Transformations) to transform the report from XML into HTML so it could be displayed in a browser, Excel, or as a PDF. The same CSS (Cascading Style Sheets) file was used for the entire project to provide a uniform look and feel. As new report formats are added to the system by the development team, XSL files are created in Altova StyleVision by dragging and dropping information items from the XML Schema we generate for the report onto a design canvas. In the background, the output stylesheets necessary to run the report are simultaneously created to match the visual design.

 

The parent node of every page in the Equity One project is <page>, so we have one main XSL template that matches <page>. From the page template, the XSL file is conditionally structured depending on in which format the user selects to view the report (i.e., browser, print, Excel, or PDF). Altova StyleVision s conditional logic is applied using XPath statements constructed in an XPath expression builder. The XPath logic reasons about the state of the XML or about parameters passed into the XSL processor at run time. Within the stylesheets, conditional sections provide different output depending on the result of evaluating these XPath expressions.

 

Because each report had a unique specification, each report has an XSL file that is specific to that report. Moreover, each XSL file is created to account for every option that the user could select when running the report. While this one-to-one relationship between reports and stylesheet designs requires more development work than would be necessary in some scenarios, the use of Altova StyleVision significantly cuts down on the effort required and in some cases may make it more practical for Equity One to turn over design maintenance to non-specialists.

 

Example 3 shows a sample of the XML code that the ReportQuery.aspx outputs and the XSL code used for an Unposted A/P Check Detail Records report.

 

XML

<report name="Unposted A/P Check Detail Records (F0414)"

 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <currentDate>2/17/2005 12:53:24</currentDate>

 <year>2004</year>

 <period>12</period>

 <record>

   <batchType>K</batchType>

   <batchNumber>17935</batchNumber>

   <docTyCk>PK</docTyCk>

   <docTyVchr>PV</docTyVchr>

   <docNumber>19827</docNumber>

   <period>7</period>

   <year>3</year>

   <vendorNumber>60001005</vendorNumber>

   <vendorName>P.S.I. Roofing</vendorName>

   <pstCode>P</pstCode>

   <amount>-3942400</amount>

   <checkDate>7/23/2003</checkDate>

 </record>

</report>

XSL

<xsl:template match="report" mode="unpostedAPDetail">

 <table cellpadding="0" cellspacing="3" border="0" class="report">

   <xsl:attribute name="width">

     <xsl:choose>

       <xsl:when test="$Format='Print'">900</xsl:when>

       <xsl:otherwise>100%</xsl:otherwise>

     </xsl:choose>

   </xsl:attribute>

   <thead style="display: table-header-group;">

     <tr>

       <td class="report" colspan="10" align="center">Equity One, Inc.</td>

     </tr>

     <tr>

       <td class="report" colspan="10" align="center"><xsl:value-of select="@name" /></td>

     </tr>

     <tr>

       <td class="report" colspan="10" align="center">For the period ending:

        <xsl:value-of select="period" />/<xsl:value-of select="year" /></td>

     </tr>

     <tr>

       <td class="report" align="center" width="50px">Batch TY</td>

       <td class="report" align="center">Batch #</td>

       <td class="report" align="center" width="75px">Doc Ty (Ck)</td>

        <td class="report" align="center" width="75px">Doc Ty (Vchr)</td>

       <td class="report" align="center">Doc #</td>

       <td class="report" align="center">Check (GL) Date</td>

       <td class="report" align="center">Vendor #</td>

       <td class="report" align="center">Name</td>

       <td class="report" align="center" width="50px">Pst Code</td>

       <td class="report" align="center">Amount</td>

     </tr>

     <tr>

       <xsl:call-template name="line">

         xsl:with-param name="columns">10</xsl:with-param>

         <xsl:with-param name="count">0</xsl:with-param>

       </xsl:call-template>

     </tr>

   </thead>

   <tbody>

     <xsl:apply-templates select="record" mode="unpostedAPDetail" />

   </tbody>

 </table>

</xsl:template>

Example 3

 

Each report is formatted in a tabular structure. The report headers that appear on every page are placed in the table head (<thead>) and the report content is placed within the table body (<tbody>). From here we created a template to match the <record> element with each record appearing in a new table row. For all of the dollar amounts that the ReportQuery returns, the number must be formatted using the following format-number function call:

 

<xsl:value-of select="format-number(amount, '###,###,###,##0.00')" />

 

Conclusion: Successful Enterprise Reporting

In a company that needs to generate a lot of different reports, the old method of individually crafting each report and hard coding the data and layout was inefficient. The model we used for Equity One let us define an overarching framework for reports and then apply these frameworks to new reports. It also ensured that a change in one place is cascaded to all other instances of the formatting across the system. The test cases we created up front gave us confidence to make sweeping changes because we confirmed that the data integrity of reports can be instantly checked by running the tests. By leveraging the Altova XML tools, the end result was a more efficient way to develop and maintain reports over the long term.

 

Of course, the success of any project is best measured by the happiness of the client. So we think the following comment made by Equity One s vice president and CIO, Ilan Zachar, validates our efforts: Most of our leasing and property managers do not need to access our Oracle application any more. They can run reports and get access to the data they need from our intranet. This process is much easier than the one they were used to before. The platform is extremely stable and allows us to modify the reports with great ease as well as add new ones. We are looking forward to adding many more to our list of reports.

 

Doug Wallace is the founder and president of Agile.Net (http://www.agile.net), a consulting company that designs and develops Web sites and e-business applications using Extreme Programming and other Agile methodologies. Previously, he was Manager of New Media at Passport Online and Director of New Business Development and Strategy for Infinet Communications. He writes on e-business for Marketing Magazine and is a frequent guest expert on Canada s Cable Pulse 24 Money Morning show.

 

Joel Aufgang is CTO for Agile.Net (http://www.agile.net) and an early adaptor and pioneer of XML and C# in the production of complex Web sites and systems. Prior to joining Agile.Net, he worked on many Web and software projects in Canada and internationally, and was co-founder of Monkeys and Typewriters, an XML and Web development consulting firm.

 

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish