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.