Partner TechTip: Empower Users to Create and Run Real-Time Queries
Create the query in the Windows app, and run it in the browser.
by Duncan Kenzie
Published: April 12, 2006
Is your IT department overworked and understaffed? Do you have a
backlog of reporting requests from users? Do those users want a flexiblerepresentation of information, including drill-down tables and charts or graphs,
shown in Web browsers? And what about the need to easily export this information
to intelligent spreadsheets, complete with formulas and formatting, not just
comma-delimited flat files?
Plus, in order for this information to be
useful, it must be up-to-date and processed in real time, not over a possibly
out-of-date history file. A user's needs might be simple, like producing a list
of contacts from a company directory database, or complex, like calculating the
top 10 customers based on sales of items costing more than $5,000 over the last
six months. Either way, you know the data is there; all you have to do is give
the users a tool to let them get the information they want, without the
assistance of someone in IT.
This is where BCD's Clover shines. Until now, query
solutions came in two basic flavors: 1) complex, do-everything products that
require degrees in advanced compiler theory or 2) limited, green-screen-based
quick and dirty utilities with limited functionality. In contrast, Clover
combines the best of both worlds because you can empower users to create their
own simple or complex queries or allow developers to incorporate its
functionality along with WebSmart to create
sophisticated enterprise-quality Web applications that extend or integrate
seamlessly with Clover queries.
Clover is a query tool for both users
and power-developers, so it combines the benefits of an intuitive, simple-to-use
design toolset along with powerful Web application design facilities. For
example, a user can create a professional-looking report with level breaks,
subtotals, and totals without knowing SQL or writing a single line of code. You
design your reports and queries using Clover's windows-based application. It
produces real-time pages in browsers that are driven by live data on your
system. The results are up-to-the-second accurate reports that can easily be
shown graphically in charts and graphs or exported to Excel spreadsheets.
Creating a query with Clover is easy:
- Open the Clover design tool from your PC and connect to the iSeries with your user profile and password.
- Select a template. Clover's templates are "intelligent," meaning that embedded within them are the instructions to tell the user what to do. The template also determines the general look and feel of the Web page.
- Select the file or files to query, and link them together. For example, for sales details by customer, you might link the shipped order details file to a customer master file by customer number. Clover constructs working SQL that you can run and test as you go so you can see previews of the selected data prior to running the final product. Here's a sample screenshot of the SQL data preview feature in the IDE, after linking three related files:
Figure 1: Clover allows you to preview the data. (Click images to enlarge.)
- Select the fields to appear on the report. For numeric fields, simply tick a checkbox to cause Clover to produce subtotals for that field.
- Select the order to sort the data . When you do this, you can also tick a checkbox next to each sort field to indicate whether it should be a level-break field. If so, subtotals will show on the report each time this value changes.
That's it! At this point, you can launch the report with one
click! The results will appear in your browser. From here, you can step back
through the wizard process to refine your report design, or you can delve into
Clover's advanced functionality by opening up all the tools that are available
to power developers in WebSmart.
Clover also includes templates and
functionality to produce high-resolution charts and graphs, gauges, and Key
Performance Indicators (KPIs) to represent data. Here's an example of a Clover
application that shows a summary bar graph, a pie chart, and a drill-down into
the detailed information:
Figure 2: Clover shows data in a variety of graphs and charts.
You can run the query shown in this image on BCD's live server by
clicking here.
Clover will not only address your users' query and reporting backlogs,
but also, combined with WebSmart, the #1 award-winning iSeries Web development
tool, give your IT staff the tools they need to create powerful, full-featured
Web applications—the best of both worlds in a single development
environment.
Duncan Kenzie is President and CTO of BCD Technical Support, the development and support group for WebSmart>a popular iSeries Web development tool, and Nexus, a portal product specifically designed for iSeries, i5, and AS/400 servers. Duncan has 29 years of experience on the midrange systems platform creating software for both green-screen and native Web environments.