asp:CaseStudy
Modern Web Reporting
Using XSL and Test First Development to Take Enterprise Reportingto the Next Level
By Doug Wallace and Joel Aufgang
Equity One (http://www.equityone.net) is a publiclytraded real estate investment trust that principally acquires, develops, andmanages neighborhood and community shopping centers around the United States. Early in 2004, Equity One approachedAgile.Net (http://www.agile.net) and askedus to help them create a solution that would allow their staff, which isgeographically dispersed at locations throughout the country, to generate andretrieve 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 andlogistical complications, Equity One desired a solution that did not rely onacquiring additional ERP licenses, installing new software to desktop machines,or delivering large amounts of new training. To maximize flexibility andease-of-use, they also required the ability to save report criteria andparameters for future re-runs, and that multiple output formats be offered totheir users. Additionally, as a public company, Equity One needed to ensure thehighest levels of security and correctness. Equity Onecomplies with Sarbanes Oxley, which calls for well-documented processes.Therefore, this added a requirement for transparency in the creation andmodification 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, theAltova XMLSpy XML development environment, and the Altova StyleVision reportand stylesheet designer as our primary development tools for the project (http://www.altova.com).
Test First Development
The new Equity One reporting framework allows selectingparameters to report on, saving and reloading the selection made, and savingand reloading the report results. Both the query and the results are saved inthe database in XML format. For a query object, we are using the ISerializableinterface to generate the XML data and also to re-load the query object backfrom XML. The trickiest part of this front-end work is mapping the correctfields from the ERP database, so getting the initial mappings correct was ofcritical importance. Therefore, we decided to use the process of Test FirstDevelopment to ensure accuracy.
As soon as we received specifications for the report andthe mappings, we created unit tests to make sure the code we wrote would becorrect and testable. We used an internally developed unit test framework thatwas created for use within all of our Web applications. This framework exactlymet our needs for ease of use and flexibility. Unlike most testing applicationscurrently available on the Internet, our framework can be directly integratedwith the Web application that requires testing. The unit test sets are added tothe page where the code is written and test results are accessible on aRunTests.apx page that can be made part of a special section of the Webapplication. The page listing the RunTests.apx pages can be made accessible todevelopers and to end users as needed.
After the field to parameter mapping is understood, theAPI exposed for pulling a report is straightforward. The code fragment inExample 1 illustrates how the client side of the reporting tool requests an IncomeReportobject populated from the database. Notice that these methods are created asstatic members in order to be accessible when no instance of the class has beencreated. The test methods are declared using a ?TestCase? attribute. Theinformation 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 eachreport be available online, in one or more print-friendly formats, and in ananalysis format ? in this case, Excel. After a user determines which report torun, he or she can choose to display the report results on the screen (allowingthe user to save the results), as Web pages, in PDF documents, or as Excelfiles. To achieve this, we used the Altova StyleVision stylesheet designer tovisually create in one single step XSL (Extensible Stylesheet Language)stylesheets for all the reporting formats. The XSL file is structured toperform conditional formatting on the XML report, depending on a ?format?parameter, which can have the values ?web,? ?print,? or ?excel.? This parameteris passed to the XSL file by the application when a user chooses to view areport in a particular format. When a report is to be sent to Excel, it issimply formatted as a simplified HTML table (which Excel can import into itsgrid) and sent to the browser using MIME type ?application/vnd.ms-excel.? PDFreports are also formatted as HTML and then printed to PDF on the server beforebeing passed to the client.
To control who can view which reports, we could havecreated a system for user management, but for efficiency of management?s sake,we opted instead to use the existing Microsoft Active Directory. This gave ourclient confidence that as the company?s organization and personnel changes, thecorrect people will see the correct reports.
The application object model uses three base classes thatprovide baseline functionality: ReportQuery.cs, Report.cs, and ReportForm.cs.Every report has three corresponding classes that inherit from these. Becausethe base classes contain the majority of the infrastructure code required by areport, the task of subclassing to create a new report is not difficult.
ReportQuery.cs is the base class that stores theparameters that define a given report. This class uses serialization togenerate the XML query that is stored in the database when the user decides tosave the selections for future use and also to load the object from apreviously generated XML file when the user decides to reload the previouslysaved selections. The XML data that is generated by the user and saved forfuture use is stored in the database.
Report.cs executes a report. Here, the abstract Executemethod is overridden to generate the results for a particular report. We used aDataSet object to extract the results from the database. With the DataSet inhand, 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 ofstylesheets for each report providing the design information necessary torender a specific report in each display format.
ReportForm.cs inherits from System.Web.UI.UserControl andis the base class for all the UserControl classes of the reports. In Example 2,we define the interface for the report parameters selection page. TheReports.aspx page contains the common interface for all the reports and uses aPlaceHolder control to programmatically load the selection page for differentreports.
protected System.Web.UI.WebControls.PlaceHolder ReportForm;
private Common.Reports.ReportForm reportForm;
private string ReportName
{
get
{
if(Request.PathInfo.Length > 0 )
returnRequest.PathInfo.Remove( 0, 1 );
else returnstring.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, theapplication executes the report in a new thread and the user is redirected tothe SavedReports.aspx page. The SavedReports.aspx page provides the user withaccess to previously saved reports, reports run in the current session, and thecurrently executing report. He or she also has the option to share savedreports with other users in the same group. Shared reports are also displayedon the SavedReports.aspx page.
Separate Content and Presentation
Because a reporting tool is all about providing end userswith information in a format they can understand and use, the heart of theapplication is in how the stylesheets are created and managed. XSL programmershave the sometimes-difficult task of taking the raw report data and deliveringit as well formatted reports for end users. As mentioned above, we chose tosimplify this work by leveraging Altova StyleVision?s ability to visuallygenerate synchronized stylesheets.
The first step in creating an Equity One report wascreating a sample XML file. We used Altova XMLSpy to handle this multisteptask. Sample XML files were created based on report specifications that wereceived from Equity One. Because designing a stylesheet requires an inputstructure ? an XML Schema, DTD, or relational database ? and our specificationwas in XML, we generated an XML Schema from that file using XMLSpy?s built-inGenerate DTD/Schema Tool. Then, an application developer used the XML file andSchema as a reference to build the sample report using the actual data from thedatabase. Next, we used XSLT (Extensible Stylesheet Language Transformations)to transform the report from XML into HTML so it could be displayed in abrowser, Excel, or as a PDF. The same CSS (Cascading Style Sheets) file wasused for the entire project to provide a uniform look and feel. As new reportformats are added to the system by the development team, XSL files are createdin Altova StyleVision by dragging and dropping information items from the XMLSchema we generate for the report onto a design canvas. In the background, theoutput stylesheets necessary to run the report are simultaneously created tomatch 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>. Fromthe page template, the XSL file is conditionally structured depending on inwhich format the user selects to view the report (i.e., browser, print, Excel,or PDF). Altova StyleVision?s conditional logic is applied using XPathstatements constructed in an XPath expression builder. The XPath logic reasonsabout the state of the XML or about parameters passed into the XSL processor atrun time. Within the stylesheets, conditional sections provide different outputdepending on the result of evaluating these XPath expressions.
Because each report had a unique specification, eachreport has an XSL file that is specific to that report. Moreover, each XSL fileis created to account for every option that the user could select when runningthe report. While this one-to-one relationship between reports and stylesheetdesigns requires more development work than would be necessary in somescenarios, the use of Altova StyleVision significantly cuts down on the effortrequired and in some cases may make it more practical for Equity One to turnover design maintenance to non-specialists.
Example 3 shows a sample of the XML code that theReportQuery.aspx outputs and the XSL code used for an ?Unposted A/P CheckDetail Records? report.
XML
<report name="Unposted A/P Check Detail Records (F0414)"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<currentDate>2/17/200512: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">
<tablecellpadding="0" cellspacing="3" border="0"class="report">
<xsl:attributename="width">
<xsl:choose>
<xsl:whentest="$Format='Print'">900</xsl:when>
<xsl:otherwise>100%</xsl:otherwise>
</xsl:choose>
</xsl:attribute>
<theadstyle="display: table-header-group;">
<tr>
<tdclass="report" colspan="10" align="center">EquityOne, Inc.</td>
</tr>
<tr>
<tdclass="report" colspan="10" align="center"><xsl:value-ofselect="@name" /></td>
</tr>
<tr>
<tdclass="report" colspan="10" align="center">Forthe period ending:
<xsl:value-ofselect="period" />/<xsl:value-of select="year" /></td>
</tr>
<tr>
<tdclass="report" align="center" width="50px">BatchTY</td>
<tdclass="report" align="center">Batch #</td>
<tdclass="report" align="center" width="75px">DocTy (Ck)</td>
<td class="report"align="center" width="75px">Doc Ty (Vchr)</td>
<tdclass="report" align="center">Doc #</td>
<tdclass="report" align="center">Check (GL) Date</td>
<tdclass="report" align="center">Vendor #</td>
<td class="report"align="center">Name</td>
<tdclass="report" align="center" width="50px">PstCode</td>
<tdclass="report" align="center">Amount</td>
</tr>
<tr>
<xsl:call-templatename="line">
xsl:with-paramname="columns">10</xsl:with-param>
<xsl:with-paramname="count">0</xsl:with-param>
</xsl:call-template>
</tr>
</thead>
<tbody>
<xsl:apply-templatesselect="record" mode="unpostedAPDetail" />
</tbody>
</table>
</xsl:template>
Example 3
Each report is formatted in a tabular structure. Thereport headers that appear on every page are placed in the table head (<thead>)and the report content is placed within the table body (<tbody>). Fromhere we created a template to match the <record> element with each recordappearing in a new table row. For all of the dollar amounts that theReportQuery returns, the number must be formatted using the followingformat-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 differentreports, the old method of individually crafting each report and hard codingthe data and layout was inefficient. The model we used for Equity One let usdefine an overarching framework for reports and then apply these frameworks tonew reports. It also ensured that a change in one place is cascaded to allother instances of the formatting across the system. The test cases we createdup front gave us confidence to make sweeping changes because we confirmed thatthe data integrity of reports can be instantly checked by running the tests. Byleveraging the Altova XML tools, the end result was a more efficient way todevelop and maintain reports over the long term.
Of course, the success of any project is best measured bythe happiness of the client. So we think the following comment made by EquityOne?s vice president and CIO, Ilan Zachar, validates our efforts: ?Most of ourleasing and property managers do not need to access our Oracle application anymore. They can run reports and get access to the data they need from ourintranet. 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 greatease as well as add new ones. We are looking forward to adding many more to ourlist of reports.?
Doug Wallace is thefounder and president of Agile.Net (http://www.agile.net),a consulting company that designs and develops Web sites and e-businessapplications using Extreme Programming and other Agilemethodologies. Previously, he was Manager of New Media at Passport Online andDirector of New Business Development and Strategy for Infinet Communications.He writes on e-business for Marketing Magazineand is a frequent guest expert on Canada?sCable Pulse 24 ?Money Morning? show.
Joel Aufgang is CTOfor Agile.Net (http://www.agile.net) and anearly adaptor and pioneer of XML and C# in the production of complex Web sitesand systems. Prior to joining Agile.Net, he worked on many Weband software projects in Canadaand internationally, and was co-founder of Monkeys and Typewriters, an XML andWeb development consulting firm.