Querying & Reporting: Using Cizer to Customize Reports

Flexibility is the key

Cizer, a decision-support server by MB Consulting, Inc. (www.cizer.com), is a publishing platform for queries, reports, and OLAP cubes. Any pure HTML browser can read Cizer's output, and you can customize its output for Netscape and Internet Explorer (IE). Cizer includes the Crystal Reports run-time engine (so you'll encounter no licensing issues other than the Cizer and database licenses). Report creators need Crystal Report Writer to create new reports; however, end users don't need Crystal Reports to run and view the reports from Cizer. Netscape and IE browsers can use the Crystal Reports client software to automatically display and print reports locally from the browser.

Let's take a walk through Cizer, and you'll see how it works. Cizer runs on a Windows NT Server. It requires NT Server 4.0, Internet Information Server (IIS) 4.0, NT Service Pack 4 + (SP4) and Active Server Pages (ASP). The install operation is painless; it takes approximately 15 to 20 minutes and requires one server reboot.

After the install routine completes, you configure Cizer by selecting a database for its configuration information. The program prompts you to perform this task when you log on after rebooting the server.

Access, SQL Server, or Oracle can host Cizer; SQL Server is the recommended database and provides the best performance. I used SQL Server 7.0 and Cizer 2.11 for all my testing, and it installed cleanly using the setup defaults.

When you install Cizer, it creates its own virtual directory. The default name of this directory is Cizer. After the installation and configuration are complete, you can access the Cizer server by navigating to this virtual directory: http://servername/Cizer/ default.htm.

Cizer implements a user/ group-based security system. Cizer security is designed to manage reports and provide security for them. When users navigate to the Cizer server, they actually log on to the Cizer security system. Only then can they access the publishing interface and reports to run. For instance, if I log on to Cizer as a user, it presents an interface like the one in Screen 1. The items I can publish appear in the tree in the left pane. To see the details of any item, I can click the + sign to expand that item, and then select the detailed item.

Here is where the user fun starts. To execute a report, the user first clicks the report name in the left pane; the report description displays in the right pane. If the report has parameters, the user enters the parameters, as you see in Screen 2, before executing the report. You can use various controls (lists, text boxes, and calendar) to display the parameters. To enter multiple parameters, the user enters or selects the data (e.g., a date) for the current parameter, then clicks Next. After selecting the parameters, the user clicks Run to execute the report. Clicking Schedule lets the user specify a later time for the report to execute. Screen 3 (page 52) shows the report in the Crystal Reports 3 Viewer in IE 4.0.

Cizer can generate reports from data warehouses. If the user selects an OLAP cube in the left pane, as you see in Screen 4, the Parameters option displays in the right pane. The user then follows the cues on the Parameters page to select the data for the report and clicks Run. That's it. The information appears in an HTML viewer that lets the user drill down, and manipulate and print the data. The viewer also supports the generation of graphics.

Creating Custom Output

In Cizer, user accounts control the configuration of reports and security. Only members of the Admin group can add or modify reports and other outputs.

Cizer contains a list of data sources it uses. You can find the current list by clicking the Admin tab, then expanding the Requests folder. Next, click Data Source. You can add a data source by clicking Add New Data Source in the top left of the right pane. Follow the steps for the connection you enter. If you are using an Access database, do not specify a user name and password. If you are using SQL Server or Oracle, these entries are required.

Now, let's say a user needs a simple report from a database. One option is to fire up Crystal Reports, build the report, test it, then upload it to Cizer. But you don't need Crystal Reports. Cizer lets you create a simple query that automatically builds the page. The Queries folder contains a list of the current queries. To add a new query, expand the Queries folder, then click Add New Query in the top of the right pane. Next, enter a name for the query, then enter the SQL statement to retrieve the data. Next, select the data source for the query. At this point, you can test the query by clicking the Test button beside the SQL field. Also, you can change the security for the query by selecting the group of users that can execute the query from the Group list. Click the Add button to add the new query to Cizer.

This new query will show up in the folder you selected on the Requests tab. Users can execute the report by selecting it, then clicking Run. The query will send the SQL statement to the server, then render the output as an HTML table in a separate browser window. A Print link in the output window lets the user print the report directly.

Cizer is designed to scale well. It supports running report engines on multiple processors and even distributing the reports across servers. You can use the maintenance interface to control how many report processes are dedicated to each processor.

The Information folder on the Admin tab provides a set of options for viewing the current system status. The View Job Queue option lets you view the current publishing jobs. You can set this option to automatically refresh to continually monitor the status of the server. The output includes information on both the report and the user making the request.

Lots of Options

With Cizer, you can create SQL queries using any software, and create MS-OLAP cubes with SQL Server 7.0. In addition to these options, Cizer can send output in a variety of formats. You can publish in Adobe Acrobat format to create portable documents. You can also publish in various Microsoft Office formats. Users can save reports in Office format when using the Crystal Reports viewer, or they may request a report, then receive it later via email.

The folks at MB are great about answering questions and supporting the product, so jump right in. Cizer's set of features and its scalability make it suitable as a publishing tool for intranet and Internet sites.