Developing a Reporting Solution with



Developing a Reporting Solution with

IBM DB2 Express-C

and

By

Anil Mahadev

DB2 DBA and IntelliVIEW User

(Level 300 Session)

Introduction

Today, in any business environment, getting rapid access to ready-to-act information has become a crucial element for success. Making timely and business sensitive information readily available to decision makers is critical for right decision-making. Among the many reporting solutions available in the market, it is important for companies to zero-in on a solution that fits their business environment without having to stretch IT budgets further.

If you are from a DB2 Express-C shop and have been looking to develop reports from your application, it might be worthwhile to read on about this amazing solution that will cater to all your reporting needs.

Say hello to IntelliVIEW Designer and IntelliVIEW Report Analyzer from Synaptris - the leading reporting solution for DB2 Express-C!

IntelliVIEW has been in the market for quite some time now. An advanced reporting solution with on-demand analysis capabilities, IntelliVIEW stands out among the many enterprise-reporting solutions available today.

This article will showcase how easy it is to build a DB2 Reporting Solution using DB2 Express-C and IntelliVIEW from Synaptris.

Here is a brief introduction about IntelliVIEW:

IntelliVIEW () is the next generation reporting solution designed for users of traditional reports. While it retains the familiar structure and usability of reports, it provides users with the flexibility to manipulate, extend and, if required analyze the information in their reports without requiring support from IT and with little or no training. IntelliVIEW achieves this within the deployment footprint of traditional reporting solutions, in terms of lower cost, ease of deployment and minimal user training.

IntelliVIEW for IBM DB2 Express-C is a freely licensed reporting and analysis solution. (The freely licensed IntelliVIEW for IBM DB2 Express-C was on a limited offer from Synaptris. Today, users can enjoy the same capabilities for IBM DB2 Express-C with IntelliVIEW Designer & Report Analyzer.)

 

IntelliVIEW empowers business users with self-serve reporting and analysis and creates new ways of leveraging DB2 Express-C data. The drag and drop report creation interface, point-and-click data analysis capability and easy report distribution mechanism makes IntelliVIEW a compelling solution for all database users.

The product has 2 components - IntelliVIEW Designer that is used to easily & quickly create feature rich reports, and IntelliVIEW Report Analyzer that enables on-demand & advanced data analysis for business users.  Developers can use IntelliVIEW Designer to create / design reports.  These reports can then be used by business users who can easily customize data visualization or analyze their information using the IntelliVIEW Report Analyzer.

DB2 System Requirements

512MB to 1GB RAM

40GB Hard-drive with at least 20GB free space

Microsoft Windows 2000 or above

DB2 Express-C free edition from the IBM website



IntelliVIEW System Requirements

512MB RAM

100MB hard disk space for the executable program, help files and samples

Microsoft Windows 2000 or above

DB2 Express-C Database Server Version 8 or above

Free trial-download of IntelliVIEW from the Synaptris website



Now that we have our hardware and software in place, let us learn how to build our first report using IntelliVIEW Designer with DB2 Express-C.

To launch the tool ( click on Start( All Programs( IntelliVIEW Designer 3.7( IntelliVIEW Designer as shown in the figure below.

[pic]

Figure 1.1

The main window is now launched as shown below.

[pic]

Figure 1.2

To create a new report within IntelliVIEW, Click on File( New( Standard Report.

[pic]

Figure 1.3

You will now be taken to the main window of the application as shown in the figure below.

[pic]

Figure 1.4

This tutorial will focus on creating a simple report using any of the DB2 Sample Database tables, or using a custom table.

Users can create standard static reports, interactive analysis reports or complex advanced reports with a blend of static and interactive elements.

The task at hand is to create a report that displays Employee Details from the Employee Table. You can re-use the DB2 Sample database’s EMPLOYEE Table for your purpose. I am using my custom Employee table for this tutorial.

Let’s get started!

1) For any report type, we first need to define the bands appropriately. Bands are used for logical object grouping. In Standard Reports, you place bands on the report and then place data into the band.

To create a band within IntelliVIEW, follow the following steps. From the toolbox click on the Insert Band button. Now insert the following bands from the list:

Report Title: This contains the Title of our Report

Page Header: This contains the Header of the Page

Master Data: This contains the body of the report, which will be assigned to a SQL Query, needed to generate the report

Footer: This contains the Footer of the page

The finished bands will look as shown in the figure below.

[pic]

Figure 1.5

2) Now we need to add content into the respective bands within the report.

In order to give a title to the Report Title band, drag and drop a Text object onto the band as shown in the figure below.

[pic]

Figure 1.6

3) Next add similar text objects to the header and footer bands.

4) Now for the Master Data section, we will need to add our SQL query.

For this we will need to create our connection to our DB2 Database. For the purpose of this tutorial we will create a DSN for the DB2 Data Source. Since this is an advanced level tutorial, I will not delve into the details of configuring a Data Source.

Configure a System Data Source with the name DB2_IntelliVIEW. Establish an ODBC connection to your DB2 Database and connect using your DB2 Credentials.

Finally test the connection and follow these steps to configure the SQL Query.

5) Click on the ODBC Connection button from the toolbox. An icon appears below the report document, symbolizing a data source – Database 1. Now we will need to configure Database 1 to the respective ODBC Data Source.

6) Once the database is connected, we will need to create a query for our report, to do this, click on the SQL button on the toolbox. Right click on the Query icon and choose Edit or hit F5.

7) Now in the query window, enter the following query

SELECT

ANIL.EMP.EMPID, ANIL.EMP.ENAME, ANIL.EMP.EMAIL, ANIL.EMP.DEPTID

FROM

ANIL.EMP

This is also shown in the figure below.

[pic]

Figure 1.7

After you view the results in the results pane, click on the OK button to exit the query editor.

Now that our query is ready, we just need to bind it to our Master Data Band.

To do this, simply right click on the Master Data Band( click on Edit and in the dialog, choose Query 1. Done! Your Master data is now bound to the database.

Congratulations on making it this far!

Now for the real good stuff (!

We can now add the required fields to our report. To do this, click on the menu View( Toolbars( Data Tree. This is shown in the figure below.

[pic]

Figure 1.8

Now drag and drop the fields under the master data band and place them as shown in the figure below.

[pic]

Figure 1.9

Now that you have placed all the requisite fields, click on the Save button and the Preview Report button located next to the print button.

The final result should appear as below.

[pic]

Figure 1.10

Your first report!

See how easy it is to create your own DB2 Express-C Report using IntelliVIEW Designer!

There are many more interesting and useful features of IntelliVIEW to be explored. Learn more about the interactive features of IntelliVIEW reports in my articles to follow in the coming months.

Please send your feedback to anilm001@ with the subject line‘DB2 Express-C Reporting Solutions with IntelliVIEW Designer’.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download