|
<html><body><pre>
Title: Drilldown Reporter with Charting
Author: Gary Dryden
Email: gary.dryden@gmail.com
Member ID: 541560
Language: C# 3.5
Platform: ASP.NET 3.5
Technology: ASP.NET
Level: Intermediate
Description: Drilldown Reporter with Charting
Section ASP.NET
SubSection
License: CPOL>
<h2>Introduction</h2>
<p>This article describes a template-driven reporting tool which allows drilldown and charting.<br />
Resulting reports may be exportd to excel or xml.<br />
It supports MS-SQL Server natively, and Oracle, MySql, etc through ODBC.</p>
<p>
<img src="DrilldownReporterWithCharting1.PNG" border="1"/><br />
<img src="DrilldownReporterWithCharting2.PNG" border="1"/>
<br />
Download: <a href="DrillDownReporter.zip">DrillDownReporter.zip</a></p>
<h2>Using the code</h2>
<p>Default.aspx shows you what you have to do to select a report.<br />
Basically you are navigating to Reports/Reporter.aspx with a query string parameter telling which report to process.<br />The reporting tool and all report templates are kept in their own folder so that reporting may be easily added to any existing project.</p>
<p>The following is a sample report template.<br />
You provide the complete HTML for the report, so you are completely in charge and can do whatever you want.<br />
It is easiest to take one of the samples and modify it for your own use.
<br />Tokens of the form @@@xxxxx@@@ are replaced with the report parameters that you have specified.</p>
<html><br />
<head><br />
<title>Sales by category</title><br />
<link id='Link2' href='Reports.css' rel='STYLESHEET' type='text/css' /><br />
<!-- set the css any way you want --><br />
<style><br />
BODY, TD<br />
{<br />
font-family: Arial;<br />
font-size: small;<br />
}<br />
.ReportFormatDDL<br />
{<br />
font-size: xx-small;<br />
}<br />
.rowclass_odd<br />
{<br />
background-color: gray;<br />
}<br />
.rowclass_even<br />
{<br />
background-color: lightblue;<br />
}<br />
#title<br />
{<br />
color: Green;<br />
}<br />
#subtitle<br />
{<br />
color: orange;<br />
}<br />
</style><br />
</head><br />
<body><br />
<div id="title" align="center"><br />
Sales by category</div><br />
<div id="subtitle" align="center"><br />
<!- 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 --><br />
(@@@Start_Date@@@ - @@@End_Date@@@)</div><br />
<!-- this is any old HTML you want to make your report look nice --><br />
<table align="center"><br />
<tr><br />
<td><br />
<div style="width: 150px;"><br />
</div><br />
</td><br />
<td valign="center"><br />
<!-- "chart" is a special id. The contents of this div are replaced with the chart.<br />
Notice the "onclick" handler, this causes your drill down menu to appear.<br />
The contents here are just a placeholder so that you can preview the report in Visual Studio.<br />
If id "chart" exists then it must appear before id "table".<br />
The charting div has the following named attributes:<br />
ChartType - one of the MSChart charting types<br />
width - width of the chart<br />
height - height of the chart<br />
xcol = the result set column to use for the x co-ord (origin 1)<br />
ycol - the result set column to use for the y co-ord (origin 1) --><br />
<div id="chart" ChartType="donut" width="300" height="300" xcol="2" ycol="3"<br />
onclick="ShowMenuEvent(event);" style="cursor: hand;"><br />
<table align="center"><br />
<tr><br />
<td><br />
Chart appears here<br />
</td><br /><br />
</tr><br />
</table><br />
</div><br />
</td><br />
<td valign="center"><br />
<!-- Here I show you how you can embed another report inside this one.<br />
Note that the variables @@@Start_Date@@@ and @@@End_Date@@@ are substituted<br />
with the currrent reports values so that they will not be asked for again. <br />
Clicking in the iframe will cause a new window to open with this report. --><br />
<iframe src='Reporter.aspx?path=MSSQL$Sales$Manager$Sales_by_Customer_ThumbNail_en.html&amp;format=html&amp;Start_Date=@@@Start_Date@@@&amp;End_Date=@@@End_Date@@@'<br />
style="width: 150px; height: 300px;" frameborder="0" scrolling="no" marginheight="0"<br />
marginwidth="0"></iframe><br />
</td><br />
</tr><br />
</table><br />
<!-- "table" is a special id. Its cotents are replaced with the reports tabular data --><br />
<table id="table" border="1" style="border-collapse: collapse" align="center"><br />
<thead><br />
<tr><br />
<td><br />
</td><br />
<td style="font-weight: bold"><br />
Category<br />
</td><br />
<td style="font-weight: bold"><br />
Sales<br />
</td><br />
</tr><br />
</thead><br />
<tbody><br />
< the first TR of the TBODY defines the template to use to display odd
numbered rows --><br />
<!-- the first column of the data result set must be an identity column used for drill down.<br />
@@@1@@@ is replaced by the identity column for each row --><br />
<tr class="rowclass_odd" onclick="ShowMenu(this, '@@@1@@@');" style="cursor: hand;"><br />
<td><br />
<!-- @@@row@@@ is a special variable containing the current row number.<br />
You can also use it to create unique ids if you need them for some reason --><br />
@@@row@@@)<br />
</td><br />
<td style="font-weight: bold"><br />
<!-- column 2 of the result set is substituted here --><br />
@@@2@@@<br />
</td><br />
<td align="right"
id="some_unique_id_@@@row@@@"><br />
<b>$</b>@@@3@@@<br />
</td><br />
</tr><br />
<!-- This is the template for the even numbered rows --><br />
<tr class="rowclass_even" onclick="ShowMenu(this, '@@@1@@@');" style="cursor: hand;"><br />
<td><br />
@@@row@@@)<br />
</td><br />
<td style="font-weight: bold"><br />
@@@2@@@<br />
</td><br />
<td align="right"><br />
<b>$</b>@@@3@@@<br />
</td><br />
</tr><br />
</tbody><br />
<tfoot><br />
<tr><br />
<td><br />
</td><br />
<td><br />
Totals<br />
</td><br />
<!-- The footer row can contain summary data for a column (like Excel).<br />
The formula value can be: sum, count, avg. --><br />
<td formula="sum"><br />
<b>$</b>@@@3@@@<br />
</td><br />
</tr><br />
</tfoot><br />
</table><br />
<!-- You can export the report to Excel or xml. --><br />
<table align="center"><br />
<tr><br />
<td><br />
<select name="format" onchange="document.forms[0].submit();"><br />
<option value="">Export to ...</option><br />
<option value="xls">excel</option><br />
<option value="xml">xml</option><br />
</select><br />
</td><br />
</tr><br />
</table><br />
<!-- This is your drill down menu --><br />
<div id="menu" class="DDL_Menu" style="position: absolute; top: 300; left: 0; display: block;<br />
width: 300px" onclick="CloseMenu();" runat="server"><br />
<fieldset style="background-color: lightblue;"><br />
<legend style="color: black">Drill-down menu </legend><br />
<table id="menutable"><br />
<tr><br />
<td><br />
<!-- @@@1@@@ is substituted with the identity column of the row that the menu has been displayed for __><br />
<a style="color: black; text-decoration: none" onclick="return(DoDrillDown(this));"<br />
href="Reporter.aspx?path=MSSQL$Sales$Manager$Sales_by_Product_by_Specific_Category_en.html&amp;Category=@@@1@@@"<br />
target="_blank">Sales by Products by Specific Category</a><br />
</td><br />
</tr><br />
<tr><br />
<td><br />
<a style="color: black; text-decoration: none" onclick="return(DoDrillDown(this));"<br />
href="Reporter.aspx?path=MSSQL$Sales$Manager$Sales_by_Customer_by_Specific_Category_en.html&amp;Category=@@@1@@@"<br />
target="_blank">Sales by Customer by Specific Category</a><br />
</td><br />
</tr><br />
</table><br />
</fieldset><br />
</div><br />
<script><br />
// we left the drilldown menu visible for design purposes, hide it!<br />
document.getElementById("menu").style.display="none";<br />
</script><br />
</body><br />
<div style="display: none"><br />
<!-- These are the parameters that will be asked for when the report is run --><br />
<div id="parameters"><br />
<parameter name="Start_Date" type="date" default="1993/01/01" /><br />
<parameter name="End_Date" type="date" default="2009/01/01" /><br />
</div><br />
<div id="data"><br />
<!-- This is the SQL that will be executed to generate the report data.<br />
In this example I am using named parameters (@Start_Date, etc) just to show how it is done,<br />
but you can use positional parameters (as you must for ODBC type connections) --><br />
<series sql="EXEC Report$Sales$Manager$Sales_by_Category_en @Start_Date='@@@Start_Date@@@', @End_Date='@@@End_Date@@@'" /><br />
</div><br />
</div><br />
</html><br />
<p />
<h2>Parameters</h2>
<p>The paramter section allows you to define the paramters which will be asked for when the report runs.<br />
Each paramter has the following attributes:<br />
name - the name of the paramter. When displayed,all underscores are replaced with spaces, so "Start_Date" displays as "Start Date".<br />
type - the paramter type. Supported values are: date, int, bigint, money, real, float, string.<br />
droplist - creates a dropdown list, eg: droplist="select val, txt from table order by txt".<br />
default - the default value to originally select in a droplist, eg: default="some text".<br />
Here is a sample:<br />
<parameter name="My_List" type="string" droplist="select val, txt from table order by txt" default="some text" /><br />
<br />
Before prompting for a parameter value, the reporting engine tries to satisfy the paramter values<br />
by matching the paramter name with a corresponding cookie, session, form variable or querystring variable (in that order).<br />
CustomerHelpers.GetParam contains the algorithm. Change it to suit your needs.<br />
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 paramter to the Stored Procedure or select statement that gets the result set.<br />
The reporting engine will always satisfy the DepartmentId parameter with the cookie value.
</p>
<h2>Getting other variables</h2>
<p>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.<br />
Add the following to the template (see ODBC$Sales$Manager$Sales_by_Product_by_Specific_Category_en.html)<br />
<div id="sets"><br />
<!-- @@@Category@@@ is a paramter to this report --><br />
<set name="CategoryName" sql="select CategoryName from Categories where CategoryId=@@@Category@@@" /><br />
<set name="SomeOtherVariable" sql="EXEC SomeStoredProcedure @@@Category@@@, @@@SomeOherInputParamter@@" /><br />
</div>
</p>
<h2>Accessing your database</h2>
<p>Web.config contains an appsettings section, like this:<br />
<appSettings><br />
<add key="DBType" value="ODBC"/><br />
<add key="MSSQLConnStr" value="Data Source=DRYDEN-D6A544AB\SQLEXPRESS;Initial Catalog=NorthWind;User Id=sa;Password=pwd;"/><br />
<add key="ODBCConnStr" value="DSN=NorthwindODBC;Uid=sa;Pwd=pwd;"/><br />
</appSettings><br />
Choose DBType of "ODBC" or "MSSQL" and set the appropriate connection string.<br />
<br />
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:<br />
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en
</p>
<p>Adding native support for other database types:<br />
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.</p>
<h2>Security</h2>
<p>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.<br />
You may want to use cookies, session variables and login info to determine if the user is allowed to run the specified report.<br />
Returns true if the report can run otherwise false.<br />
<br />
Tip: If you name your reports as follows: Report$Department$Role$ReportName_Language.html<br />
Then you can use cookies and session variables to determine if the logged in user should be able to run the specified report.</p>
<h2>Language</h2>
If you suffix the report name with a two character language code, eg:<br />
Reports/ODBC$Sales$Manager$Sales_by_Customer_by_Specific_Category_en.html<br />
Reports/ODBC$Sales$Manager$Sales_by_Customer_by_Specific_Category_fr.html<br />
then the report parameters and column titles can be tailored to a specific language.
<h2>Charting</h2>
Charts are built with the freely downloadable MSChart.exe and MSChart_VisualStudioAddOn.exe.<br />
See this article to get you started with charting:<br />
http://www.codeproject.com/KB/webforms/ASP_NET_Chart_controls.aspx.<br />
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.<p>
</p>
<h2>Thumbnailing</h2>
<p>
In the example provided above, the second chart was in an iframe and when you click on it, it expands in another window.<br />
The expansion works because the charting div in the iframe contains an onclick handler<br />
(see ODBC$Sales$Manager$Sales_by_Customer_Thumbnail_en.html)<br />
which actually opens up a new window with a different report<br />
(see ODBC$Sales$Manager$Sales_by_Customer_en.html).
</p>
<h2>Conclusion</h2>
I hope you enjoy using this reporting tool.<br />
If you have any suggestions for improvement, please contact me.<br />
If you make any changes (eg: 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!).
</body></html>
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.