Is your IT department overworked and understaffed? Do you have a backlog of reporting requests from users? Do those users want a flexible representation 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:

1. Open the Clover design tool from your PC and connect to the iSeries with your user profile and password.
2. 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.
3. 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.)
   

4. Select the fields to appear on the report. For numeric fields, simply tick a checkbox to cause Clover to produce subtotals for that field.
5. 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.

Check out BCD's other offerings in the MC Showcase Buyer's Guide.

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.

Duncan Kenzie is President and CTO of ExcelSystems Software Development Inc. Duncan’s company is responsible for product development and technical support for BCD International Inc.’s (www.bcdsoftware.com) product line. Duncan has a BA in English Literature from the University of Waterloo, Ontario, and enjoys writing on a variety of subjects. Duncan is a frequent public speaker and has been writing articles about the midrange platform since 1985. He has also been involved in producing programmer productivity and business intelligence tools for 25 years and is the technical lead on products such as WebSmart, WebSmart PHP, and Nexus, leading System i Web tools.  Duncan still enjoys programming, and studies in leadership and self-improvement. He can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..