Introduction
This article describes a template-driven reporting tool which allows drilldown and charting. Resulting reports may be exported to Excel or XML. It supports MS-SQL Server natively, and Oracle, MySql, etc. through ODBC.
Using the Code
Default.aspx shows you what you have to do to select a report. Basically, you are navigating to Reports/Reporter.aspx with a query string parameter telling which report to process. The reporting tool and all report templates are kept in their own folder so that reporting may be easily added to any existing project.
The following is a sample report template. You provide the complete HTML for the report, so you are completely in charge and can do whatever you want. It is easiest to take one of the samples and modify it for your own use. Tokens of the form @@@xxxxx@@@ are replaced with the report parameters that you have specified.
<html>
<head>
<title>Sales by category</title>
<link id='Link2' href='Reports.css' rel='STYLESHEET' type='text/css' />
<!---->
<style>
BODY, TD
{
font-family: Arial;
font-size: small;
}
.ReportFormatDDL
{
font-size: xx-small;
}
.rowclass_odd
{
background-color: gray;
}
.rowclass_even
{
background-color: lightblue;
}
#title
{
color: Green;
}
#subtitle
{
color: orange;
}
</style>
</head>
<body>
<div id="title" align="center">
Sales by category</div>
<div id="subtitle" align="center">
<!- the parameter section below tells the reporting engine to ask for a
start and end date (into the variable @@@Start_Date@@@ and @@@End_Date,
these values are substituted in the next line -->
(@@@Start_Date@@@ - @@@End_Date@@@)</div>
<!---->
<table align="center">
<tr>
<td>
<div style="width: 150px;">
</div>
</td>
<td valign="center">
<!---->
<div id="chart" ChartType="donut" width="300" height="300" xcol="2" ycol="3"
onclick="ShowMenuEvent(event);" style="cursor: hand;">
<table align="center">
<tr>
<td>
Chart appears here
</td>
</tr>
</table>
</div>
</td>
<td valign="center">
<!---->
<iframe src='Reporter.aspx?
path=MSSQL$Sales$Manager$Sales_by_Customer_ThumbNail_en.html&
format=html&Start_Date=@@@Start_Date@@@&End_Date=@@@End_Date@@@'
style="width: 150px; height: 300px;" frameborder="0"
scrolling="no" marginheight="0"
marginwidth="0"></iframe>
</td>
</tr>
</table>
<!---->
<table id="table" border="1" style="border-collapse: collapse" align="center">
<thead>
<tr>
<td>
</td>
<td style="font-weight: bold">
Category
</td>
<td style="font-weight: bold">
Sales
</td>
</tr>
</thead>
<tbody>
< the first TR of the TBODY defines the template to use to display odd
numbered rows -->
<!---->
<tr class="rowclass_odd" onclick="ShowMenu(this, '@@@1@@@');"
style="cursor: hand;">
<td>
<!---->
@@@row@@@)
</td>
<td style="font-weight: bold">
<!---->
@@@2@@@
</td>
<td align="right"
id="some_unique_id_@@@row@@@">
<b>$</b>@@@3@@@
</td>
</tr>
<!---->
<tr class="rowclass_even" onclick="ShowMenu(this, '@@@1@@@');"
style="cursor: hand;">
<td>
@@@row@@@)
</td>
<td style="font-weight: bold">
@@@2@@@
</td>
<td align="right">
<b>$</b>@@@3@@@
</td>
</tr>
</tbody>
<tfoot>
<tr>
<td>
</td>
<td>
Totals
</td>
<!---->
<td formula="sum">
<b>$</b>@@@3@@@
</td>
</tr>
</tfoot>
</table>
<!---->
<table align="center">
<tr>
<td>
<select name="format" onchange="document.forms[0].submit();">
<option value="">Export to ...</option>
<option value="xls">excel</option>
<option value="xml">xml</option>
</select>
</td>
</tr>
</table>
<!---->
<div id="menu" class="DDL_Menu" style="position: absolute; top: 300;
left: 0; display: block;
width: 300px" onclick="CloseMenu();" runat="server">
<fieldset style="background-color: lightblue;">
<legend style="color: black">Drill-down menu </legend>
<table id="menutable">
<tr>
<td>
<!---->
<div id="parameters">
<parameter name="Start_Date" type="date" default="1993/01/01" />
<parameter name="End_Date" type="date" default="2009/01/01" />
</div>
<div id="data">
<!---->
<series sql="EXEC Report$Sales$Manager$Sales_by_Category_en
@Start_Date='@@@Start_Date@@@', @End_Date='@@@End_Date@@@'" />
</div>
</div>
</html>
Parameters
The parameter section allows you to define the parameters which will be asked for when the report runs. Each parameter has the following attributes:
name - the name of the parameter. When displayed, all underscores are replaced with spaces, so "Start_Date" displays as "Start Date". type - the parameter type. Supported values are: date, int, bigint, money, real, float, string. droplist - creates a dropdown list, e.g.: droplist="select val, txt from table order by txt". default - the default value to originally select in a droplist, e.g.: default="some text".
Here is a sample:
<parameter name="My_List" type="string" droplist="select val,
txt from table order by txt" default="some text" />
Before prompting for a parameter value, the reporting engine tries to satisfy the parameter values
by matching the parameter name with a corresponding cookie, session, form variable or querystring variable (in that order).
CustomerHelpers.GetParam contains the algorithm. Change it to suit your needs.
For example, if you have a multi-department site and you want to restrict the viewable data for a logged in employee to her own department, then create a cookie called DepartmentId and ensure that DepartmentId is a parameter to the Stored Procedure or select statement that gets the result set.
The reporting engine will always satisfy the DepartmentId parameter with the cookie value.
Getting Other Variables
There will be times when you need more information. For example, you may have the identity value for a category but you need its name. Add the following to the template (see ODBC$Sales$Manager$Sales_by_Product_by_Specific_Category_en.html):
<div id="sets">
<!---->
<set name="CategoryName" sql="select CategoryName from Categories
where CategoryId=@@@Category@@@" />
<set name="SomeOtherVariable" sql="EXEC SomeStoredProcedure @@@Category@@@,
@@@SomeOherInputParamter@@" />
</div>
Accessing your Database
Web.config contains an appsettings section, like this:
<appSettings>
<add key="DBType" value="ODBC"/>
<add key="MSSQLConnStr" value="Data Source=DRYDEN-D6A544AB\SQLEXPRESS;
Initial Catalog=NorthWind;User Id=sa;Password=pwd;"/>
<add key="ODBCConnStr" value="DSN=NorthwindODBC;Uid=sa;Pwd=pwd;"/>
</appSettings>
Choose DBType of "ODBC" or "MSSQL" and set the appropriate connection string.
The sample stored procedures (in the folder (SPs) run against the Northwind database, which is not included in the zip file, but can be downloaded from here.
Adding native support for other database types:
The files DBMsSql.cs and DBOdbc.cs are good examples of how little code you need to write to natively support any database with a .NET provider.
Security
CustomerHelpers.cs contains a static method named CanAccess. It has a single parameter which is the filename name of the report that is being run. You may want to use cookies, session variables and login information to determine if the user is allowed to run the specified report.
Returns true if the report can run, otherwise false.
Tip: If you name your reports as follows: Report$Department$Role$ReportName_Language.html, then you can use cookies and session variables to determine if the logged in user should be able to run the specified report.
Language
If you suffix the report name with a two character language code, e.g.:
Reports/ODBC$Sales$Manager$Sales_by_Customer_by_Specific_Category_en.html
Reports/ODBC$Sales$Manager$Sales_by_Customer_by_Specific_Category_fr.html
then the report parameters and column titles can be tailored to a specific language.
Charting
Charts are built with the freely downloadable MSChart.exe and MSChart_VisualStudioAddOn.exe.
See this article to get you started with charting. I suggest you build your own charting project before you try using charting in the reporting engine as there is a bit of a learning curve to getting charting installed and working correctly.
Thumbnailing
In the example provided above, the second chart was in an iframe and when you click on it, it expands in another window.
The expansion works because the charting div in the iframe contains an onclick handler
(see ODBC$Sales$Manager$Sales_by_Customer_Thumbnail_en.html) which actually opens up a new window with a different report (see ODBC$Sales$Manager$Sales_by_Customer_en.html).
Conclusion
I hope you enjoy using this reporting tool. If you have any suggestions for improvement, please contact me. If you make any changes (e.g.: add a new formula type or add a new native database provider), please send it along and I'll incorporate it into the source code (with full bragging rights going to you!).
History
- 25th February, 2010: Initial version
