Saving and Exporting Data with Dynamic Queries
Data Requirement
This example requires installation of either the IBM DB2 UDB, Oracle, MS-SQL
or Sybase version of QCC 2003 database.
- The sample data can be found in the installation CD under sampledata/qcc/acs.
Follow the instruction in the readme.txt
file in the version of QCC 2003 you want to install.
- Define this data source to DB2 Alphablox with the data source name
set to qcc2003-rdb.
Reusing the Example
To reuse this example for your own application, there are three variables in
the file.jsp page that you will
need to modify based on your application. These are used to specify where these
JSP files are in relation to your application root, where the static report
should be saved on the server, and where the referenced stylesheets are.
Techniques Demonstrated
This example demonstrates a variety of functionality available in Relational
Reporting. It demonstrates:
- how to use the ReportBlox API to dynamically set the query by reusing
the objects in the session without refreshing the whole page when
the report is set to interactive mode
- how to use TextBlox to add "drill to detail" type of functionality
to your application
- how to get to a data row and get the cell values
- how to save a static report (a snapshot of the data), how to save/load a
bookmark (preserving the format and layout yet reflecting up-to-the-minute
data), and how to export data to Excel
 |
While this example demonstrates these various capabilities,
in order to keep it simple for the purpose of learning, there are
areas where the demonstrated functionality could be further enhanced.
For example:
- The file saving feature can be modified to prompt users for
a file name. Currently it always saves the report as qcc.html
in the savedreports/
subdirectory.
- The static HTML file saved preserves the added HTML code and,
if there are JavaScript or links, they do not work properly when
the file is loaded.
These can be further enhanced by doing additional processing before
saving/exporting the report data. The excel.jsp
and textBloxUtils.jsp
(for the export to Excel option) demonstrate how to remove the extra
HTML code for a cleaner export. |
|
Dynamically Setting Queries
This is done by using the following techniques:
- Use the bloxName property
to reuse the ReportBlox object in the session.
- Use an iframe as the target when an HTML form is submitted (and another
JSP page is called to set a different query) so the entire calling page is
not refresh. When the form is submitted, the reportName
is sent along with the week selection.
- execQuery.jsp is called when
the form is submitted. It extracts the reportName
and form post parameters from the HTTP request object.
- The called page (execQuery.jsp)
reuses the ReportBlox and SQLDataBlox objects in the session to set a new
query:
BloxContext context = BloxContextFactory.getBloxContext(request, response);
ReportBlox reportBlox = (ReportBlox)context.getBlox( reportName );
SQLDataBlox dataBlox = (SQLDataBlox)context.getBlox( reportName + "_data" );
dataBlox.setQuery( query );
dataBlox.execute();
- In the calling page (qccHighlight.jsp),
we use the global refreshReport()
JavaScript function to refresh the ReportBlox.
<script>
function refresh( reportName ) {
refreshReport( reportName );
}
</script>