Multi-lingual (Capable) Light Weight Report Writer with Drilldown

• Download source - 24.06 KB

Sample Input

Sample Results

Introduction

This article describes a very light weight (less than 20K of ASP.NET code) report writer that actually gives you more powerful options than SQL Server Reporting Services.
Caveat: Panty-waist managers better stop reading now.
You'll need to be able to write Stored Procedures and yikes, know some CSS.
Still with me?

The Report writer uses reflection (always wanted to use that term with regards to SQL server) to acquire the SP's parameters and then builds a form to present these to the user.
Hold on! SQL server doesn't have reflection!
Oh yes it does! It's called sp_HelpText!

EXEC sp_HelpText <SP_NAME>

This will give you the source code for the SP!

It is then a simple matter to determine the parameter names, types and default values.

• If a parameter is a date, then a read-only text box is presented with a calendar button next to it. Click the button to choose a date.
• If a parameter is a float or money, then you can only enter the following characters "-.0123456789".
• If a parameter is an integer type, then you can only enter the following characters "-0123456789".

You can create a dropdown list of choices by appending a comment on the parameter definition line, for example:

@MyParam int = 0   -- enum: ddlMyParamList

This will create a dropdown list by executing the SP ddlMyParamList and highlighting the item whose option value is "0".
The SP ddlMyParamList must return a table with two columns.
The first column is the "value=" portion of an option item and the second column is the text to display.
You do not need to HtmlEncode() the column values as the Report Writer does this for you.
ddlMyParamList could have taken parameters, e.g.:

@MyParam int = 0   -- enum: ddlMyParamList 1,'abc'

Here is a sample:

CREATE PROCEDURE [dbo].[ddlProgramList]
as
Select 0 as Ident, 'All' as Program
union
Select CardProgramIdent, Name as Program from CardProgram order by Program

Before asking the user for input, the report writer looks to see if it can resolve a parameter from (in this order):

• Cookie value
• Session variable
• Form value
• QueryString value

So for example, if the SP takes the "@MyParam" parameter then "MyParam" will be checked for in the cookie, session, Form and QueryString lists.
Only unresolved parameters are presented to the user.

Note: Resolving parameters with user input (Form and QueryString) is probably not advisable.
I usually comment those out in a production environment and you should too.
In the zip file attached to this project, they are commented out for safety.

Now the SP is executed and the output is ready to be formatted.

SP Source Code

CREATE PROCEDURE [dbo].[admin_en_ShowCustomersByProgram]
@StartDate datetime = '2007/01/01',
@EndDate datetime = '2008/12/31',
@Program int = 0	-- enum: ddlProgramList
-- title: CardHolders by program
-- subtitle: @StartDate - @EndDate
-- headerrow: <tr style="background-color: aqua;font-weight:bold"><td></td>
-- 	<td>First Name</td><td>Last Name</td><td style="text-align:right;">Balance</td>
--	<td>Status</td><td>Enrolled</td></tr>
-- oddrow:    <tr style="background-color: lightsalmon"><td>row.</td><td></td>
--	<td></td><td style="text-align:right;"></td><td></td></tr>
-- evenrow:   <tr style="background-color: lightblue"><td>row.</td>  <td></td>
--	<td></td><td style="text-align:right;"></td><td></td></tr>
-- footerrow: <tr style="background-color: lightgreen"><td>Totals</td>
--	<td formula="count"></td><td></td>
--	<td style="text-align:right;" formula="sum"></td><td></td></tr>
AS
if(@Program = 0)
BEGIN
	select FirstName,
 dbo.HtmlOpen(LastName, 'ThisProfile.aspx?CustomerIdent='+convert(varchar(20),_
	CustomerIdent),600,400) as LastName,
 convert(varchar(20),MMFBalance,1) as Balance,
CASE WHEN MMFBalance < 20 THEN '<span style=''color:red''>'+_
	dbo.HtmlEncode('<Low>')+'</span>' WHEN MMFBalance>= 20 THEN _
	'<span style=''color:green''>'+dbo.HtmlEncode('<Ok>')+'</span>' END as Status,
convert(char(10),[when],120) as Enrolled
from Customer where  [when] >= @StartDate and [when] < _
	dateadd(day, 1, @EndDate) order by [when]
END
else
BEGIN
	select FirstName,
 dbo.HtmlOpen(LastName, 'ThisProfile.aspx?CustomerIdent='+convert(varchar(20),_
	CustomerIdent),600,400) as LastName,
convert(varchar(20),MMFBalance,1) as Balance,
CASE WHEN MMFBalance < 20 THEN '<span style=''color:red''>'+dbo.HtmlEncode('<Low>')+_
	'</span>' WHEN MMFBalance>= 20 THEN '<span style=''color:green''>'+_
	dbo.HtmlEncode('<Ok>')+'</span>' END as Status,
convert(char(10),[when],120) as Enrolled
from Customer where [when] >= @StartDate and [when] < dateadd(day, 1, @EndDate) _
	order by [when]
END

The SP contains special comments which help the Report Writer format the output.

• title: Name of Report
• subtitle: Subtitle of report
• headerrow: The format for the header row (see SP source code for format)
• oddrow: The format for the odd rows (see SP source code for format)
• evenrow: The format for the even rows (see SP source code for format)
• footerrow: The format for the footer row (see SP source code for format)

All formatter items must appear before the AS
If the subtitle contains SP parameters (eg: @Start_Date, @End_Date), then they are replaced with their actual values.

Each of the row definitions is of the format:

<tr style="some style info"><td style="some style info"></td>
	<td style="some style info"></td> ... 

The content from the database is HtmlEncoded() and placed inside the TD element, starting at column 2, column 1 is reserved for the row number, but you must account for the row number in your row template.
Column 1 is special and usually contains "row." or "row)". The text "row" is replaced with the row number.
If you want to generate your own HTML for a column (as you will see in our example), the column data must start with "<span" or "<a ".
This is how you do special effects and drilldowns.
The footer can contain a formula attribute with a value of count, sum or avg (see above example).

Drilldown and Other Tricks

In the above example, look at the line:

dbo.HtmlOpen(LastName, 'ThisProfile.aspx?ID='+convert(varchar(20),ID),600,400) _
   as LastName,

The user defined function dbo.HtmlOpen() generates the following HTML code:

<a href="#" onclick="return(OpenWindow('ThisProfile.aspx?ID=1',600,400));">
	SomeLastName</a>

And since the result column starts with "<a ", it is not automatically HtmlEncoded() and therefore, the code passes to the page and you get an ANCHOR.
The first argument is the value to display and is automatically HtmlEncoded() for you.
The second argument is the URL to open. It is generated by concatenating the ID of the current row to the ASPX page that will be used to show the profile.
The third/fourth arguments are the width/height of the new window.
OpenWindow() is a helper function built in to the Report Writer.

In the above example, look at the line:

CASE WHEN MMFBalance < 20 THEN '<span style=''color:red''>'+dbo.HtmlEncode('<Low>')+_
	'</span>' WHEN MMFBalance >= 20 THEN '<span style=''color:green''>'+_
	dbo.HtmlEncode('<Ok>')+'</span>' END as Status,

This will generate either:

<span style='color:green'><Ok></span>

or

<span style='color:red'><Low></span>

We had to force the content to be displayed to be HtmlEncode()'d because the column data started with "<span" and if we didn't <Low> or <Ok> would have been interpreted as an HTML tag and would not have displayed.

There is one last user defined function which is not in the above example, but which is described here:

HtmlAnchor(DiplayText, Url)

It is used similar to HtmlOpen() and it opens a new default window with all the usual decorations.

Some Handy User Defined Functions

CREATE FUNCTION [dbo].[HtmlEncode]
(
	@Text nvarchar(1500)
)
RETURNS varchar(1500)
AS
BEGIN
	DECLARE @Result nvarchar(1500);
	SET @Result = @Text;
	SET @Result = REPLACE(@Result, '&', '&');
	SET @Result = REPLACE(@Result, '<', '&lt;');
	-- add any of your own other escapes here
	RETURN @Result
END

CREATE FUNCTION [dbo].[HtmlOpen]
(
	@DisplayText nvarchar(500),
	@Url nvarchar(500),
	@Width int,
	@Height int
)
RETURNS varchar(1000)
AS
BEGIN
	DECLARE @Result nvarchar(1000);
	DECLARE @dt varchar(700);
	SET @dt=dbo.HtmlEncode(@DisplayText);
	DECLARE @q char(1); SET @q= '''';
	SET @Result = '<a href="#" onclick="return(OpenWindow_
		('+@q+@Url+@q+','+convert(varchar(20),@Width)+','+_
		convert(varchar(20),@Height)+'));">'+@dt+'</a>';
	RETURN (@Result);

END

CREATE FUNCTION [dbo].[HtmlAnchor]
(
	@DisplayText nvarchar(500),
	@Url nvarchar(500)
)
RETURNS varchar(1000)
AS
BEGIN
	DECLARE @Result nvarchar(1000);
	DECLARE @dt varchar(700);
	SET @dt=dbo.HtmlEncode(@DisplayText);
	SET @Result = '<a href="'+@Url+'" target="_blank">'+@dt+'</a>';
	RETURN (@Result);

END

Report Writer Output

The Report Writer can output either HTML or Excel format.

About the Calendar

It was written by Tan Ling Wee on 2^nd December, 2001.
I have just made a few modifications to accommodate internationalization.

Internationalization

All text for both the Report Writer and the Calendar control get their text from <asp:localize> blocks, so translation to your native language should be simple.

Report Security

Since this application could execute any arbitrary SP, you'll want to restrict which ones it can execute.
I start all report procedures as follows: <role>_<lang>_<ProcName>.

For example: admin_en_ShowCustomersByProgram. It is then a simple matter in Page_Load() to ensure that the logged in user has the right permissions to view the report based on his/her role and the beginning part of the SP name.

Background

I have coded SQL Server Reporting Services reports in the past and just thought it was overkill.
This is a whole lot simpler and you'll be writing your first report in 5 minutes (plus you get free drilldown).

Using the Code

Unzip the source code provided into the root of your ASP.NET project.
You call the Report Writer like this:

<a href="Reporter.aspx?sp=NAME_OF_YOUR_STORED_PROC">This proc </a>

You'll probably be writing new SPs to use the Report Writer, so make sure your parameter names are meaningful because they are displayed to the user.
@Start_Date and @End_Date are better names than @sd and @ed.
The Report Writer replaces '_' with a space so @Start_Date is displayed as Start Date.
If you need to execute existing SPs, then build a wrapper SP that has friendlier parameter names and then just invoke the underlying SP.

I live in Canada and work in English, but 25% of our population speak and work in French.
So when I write an SP, I use English parameter names and then build a wrapper SP with French parameter names which just call the underlying English parameterized SP.
So the French speakers see parameters and titles in French and the English speakers see English.
Everyone is happy, and I really only have one SP to maintain.

Here is an example of the same report in French, I created a wrapper around the English version so that I could output French variable names and titles.

CREATE PROCEDURE [dbo].[admin_fr_ShowCustomersByProgram]
@Date_de_début   datetime = '2007/01/01',
@Date_de_fin datetime = '2008/12/31',
@Program int = 0  -- enum: ddlProgramList
-- title: CardHolders par program
-- subtitle: @StartDate - @EndDate
-- headerrow: <tr style="background-color: aqua;font-weight:bold"><td></td>
--	<td>Prenom</td><td>Nom de famille</td>
--	<td style="text-align:right;">Balance</td>
--	<td>État</td><td>Inscrits</td></tr>
-- oddrow:    <tr style="background-color:   lightsalmon"><td>row.</td><td></td>
--	<td></td><td style="text-align:right;"></td><td></td></tr>
-- evenrow:   <tr style="background-color:  lightblue"><td>row.</td><td></td>
--	<td></td><td style="text-align:right;"></td><td></td></tr>
-- footerrow: <tr style="background-color: lightgreen"><td>Totaux</td>
--	<td formula="count"></td><td></td>
--	<td style="text-align:right;" formula="sum"></td><td></td></tr>
AS
EXEC admin_en_ShowCustomersByProgram @Date_de_début, @Date_de_fin, @Program

Note that SQL Server allows accented characters in the parameter names, so use them liberally!

Here are the screens in French:

Points of Interest

This Report Writer has been a personal/professional project which has evolved over the years, and now I think it is polished enough to share. If you have any enhancements / bug fixes that you wish to make or suggest, by all means do so. I'd appreciate your updates so that I can incorporate them and share them with the other CodeProject members.