Dynamic Web Reporting
Tags : Dynamic, Web, Reporting, Download, files, Report, construction engine, sample projects
The sample project files can be downloaded here.
This article describes about how to build dynamic web report. I have composed this article based on one of my work experiences. A complete dynamic report where columns can be chosen to be displayed over the report as well as option for filters to be applied on each of the column. Let us have an insight on how to deal with it.
During one of my experience in web development I was assigned on a task to complete 7 reports in 4 days. A short summary of the requirement was:
1. Each report must be capable of catering to over 200 fields
2. Based on column type each field will have filters that can be applied (date filters, numeric filters, alphanumeric filters)
3. Filters provided by the user must be validated
4. User can choose a list of columns to be displayed in the report
In a typical approach to this requirement of composing SQL statements, and designing pages displaying over 200 fields for the user selection, and writing loops to check if the field is chosen to be displayed and applying filters if a value is specified, client side validation for all the filter fields would, practically be impossible especially for the case where you have field count in 100s.
If column A is chosen then display A in the report, if column B the display it…until your 200th column. You would probably end up writing few thousand lines of IF loop just to display the columns over the report and imagine the code for filters that can be applied for each of the columns!
Let’s get technical
Well how did I complete 7 reports in 4 days?
1 Days to think.
2½ Days to implement the framework.
½ Days to complete the reports.
The best approach in this situation was to write a common module, let us call it a Report Construction Engine, which will manage both request capture page where the required fields are placed in a report. There is a Class or a XML file that will have the definition of the report and let us call this the Report View Definition.
Let us have a simple graphical view of how the entire process works.
The Report Construction Engine accepts a Report View Definition which contains:
1. What are the columns to display in the user request form
2. What type of filter is bound to this column
3. What is the data type of the column
4. Is the column selected by default
5. What is the display name of the column
6. What is the mapping name in the table
Based on these criteria the user form is dynamically created. The Report Construction Engine creates the necessary filters which are defined with the column, based on the data type of each column. A client side validation script is tagged to validate each of the fields. Freebies like date picker and default 3 sort options are provided along with the report.
In the sample application provided a class is written to define the view for the Order Report which we are working on. Let us look into a part of the code. Internally the Report Construction Engine loads the XML into a dataset, so let us create simple column definition, and add it to the dataset.
newRow = dt.NewRow '------------------------------
‘The column of a table we are dealing with
0 newRow(ReportConfig.CLM_FieldID) = “OrderId”
‘Display Name of the column on the report
newRow(ReportConfig.CLM_FieldName) = “Order ID”
‘Type of filter to be applied
newRow(ReportConfig.CLM_Criteria) = ReportConfig.CRITERIA_FROM_TO
‘Default Selected Column, Source can be configured to read a list from xml
newRow(ReportConfig.CLM_Source) = ReportConfig.SOURCE_NONE
‘Default Selected Column set it to 1 if you want it to be selected
newRow(ReportConfig.CLM_DisplayField) = 0
‘Client side script validation and the sql parameter type is based on this
newRow(ReportConfig.CLM_FieldType) = ReportConfig.TYPE_NUMBER
dt.Rows.Add(newRow) ‘——————————-
The query page in our example.
The resulting report.
Points of Interest
All data provided are constructed in a parameterized query, with proper defined data types, so the report is not prone to stuffs like URL hacks, and query breakage on apostrophe.
This structure works on a table. If you are required to work with multiple tables, a VIEW can be substituted.
A sample application is provided, which connects to NorthWind databases orders table, connection string can be modified in DbConnect.
No copy rights, take it, modify it, use it! 
The sample project files can be downloaded here.
