Views Optimized for Crystal Reports

Resources

Setting up views optimized for Crystal Reports

Q: Howard, I have a few power users that have Crystal Reports and would like to allow them to create reports from our AS/400 data. However, Crystal only displays the system names for the field in our physical files and the users do not know what these fields represent. Is there any way to make Crystal display the field descriptions?

Short answer, no. However, there is a technique that I use that does not require a lot of work on your part and will allow the end users easy access to physical files with ugly legacy names. If also gives you the opportunity to pre-join your physical files to keep the user from having to specify, (and possibly screw up), the join criteria when reporting from more than one file. What is this magical formula for simplifying user access to your data: views.

Views are like logical files, except they take no space on your physical media. If you list files in a library, views show up as if they are actually present. However, views are really just an SQL statement stored in your system catalogue tables. When a user attempts to access the data in a view, the AS/400 executes the SQL statement and returns the results to the users. The user never knows that he is not accessing a regular physical file.

The nice thing about views is that you can create virtual columns and rename existing columns. To look at how this might benefit your users, lets take a look at Howard’s pretend inventory system and then build an end user view so our uses can easily create custom price list reports. Conceptually, the system has an item file and a file containing price classes and discount percentages. We want to create a view where the data is joined, the discount is calculated and a discount percentage field is added. So, lets create the Potemkin system!

This system has an item master file called AFPM01, (a nice, confusing legacy type name!). This item file contains fields for the item description, unit of measure and default price. Here is SQL for creating the file:

 CREATE TABLE SQLBOOK.AFPM01 (
	AFID01	CHAR(10) NOT NULL,
	AFUM01	CHAR(2) NOT NULL,
	AFDE01	CHAR(30) NOT NULL,
	AFPR01	DECIMAL(10,2) NOT NULL)

Now lets add some records:

INSERT INTO SQLBOOK.AFPM01 VALUES (‘PART1’,’EA’,’The First Part’,10.25)
INSERT INTO SQLBOOK.AFPM01 VALUES (‘OTHER’,’FT’,’Other Part’,99.50)

Next, my legacy inventory system has a file of prices with a price class, description and discount percentage. Here is the SQL for the physical file:

CREATE TABLE SQLBOOK.AGPM01 (
	AGID01	CHAR(10) NOT NULL,
	AGPC01	CHAR(5) NOT NULL,
	AGDP01	DECIMAL(5,2) NOT NULL)

Now, lets again add a few records to reflect discounts for price classes

INSERT INTO SQLBOOK.AGPM01 VALUES (‘PART1’,’T1’,12.5)
INSERT INTO SQLBOOK.AGPM01 VALUES (‘PART1’,’T2’,10.1)
INSERT INTO SQLBOOK.AGPM01 VALUES (‘PART1’,’T3’,8.0)
INSERT INTO SQLBOOK.AGPM01 VALUES (‘OTHER’,’T1’,22.8)
INSERT INTO SQLBOOK.AGPM01 VALUES (‘OTHER’,’T2’,16.4)
INSERT INTO SQLBOOK.AGPM01 VALUES (‘OTHER’,’T3’,12.7)

The above data is meant to reflect pricing for classes T1, T2 and T3 for the parts PART1 and OTHER identified in the AFPM01 table. If we wanted to list parts and their discounts, we need to join the tables together. The following SQL statement produces a list of parts and their respective discounts:

SELECT AFID01, AFUM01, AFDE01, 
		AFPR01, AGPC01, AGDP01
	FROM SQLBOOK.AFPM01 INNER JOIN SQLBOOK.AGPM01 
		ON (AFID01=AGID01)

The above select would yield results like the following:

AFID01	AFUM01	AFDE01	AFPR01	AGPC01	AGDP01
PART1	EA	The First Part	10.25	T1	12.5
PART1	EA	The First Part	10.25	T2	10.1
PART1	EA	The First Part	10.25	T3	8
OTHER	FT	Other Part	99.5	T1	22.8
OTHER	FT	Other Part	99.5	T2	16.4
OTHER	FT	Other Part	99.5	T3	12.7

Now, that’s all well and good, but our filed names leave a lot to be desired in the name of end user readability. So, let make a view with the following statement:

CREATE VIEW SQLBOOK.VPRICE_SHEET
	(ITEM_NUMBER, UNIT, DESCRIPTION,
 		PRICE, PRICE_CLASS, 
		DISCOUNT_PCT, DISCOUNTED_PRICE) 
	AS SELECT AFID01,AFUM01 , AFDE01 , 
		AFPR01, AGPC01, AGDP01,
            	DECIMAL(AFPR01-(AFPR01*(AGDP01*.01)),10,2)
	FROM SQLBOOK.AFPM01 INNER JOIN SQLBOOK.AGPM01 
		ON (AFID01=AGID01)

What this does is create a view in the SQLBOOK library called VPRICE_SHEET. The view has columns named ITEM_NUMBER, UNIT, DESCRIPTION, PRICE, PRICE_CLASS, DISCOUNT_PCT and DISCOUNTED_PRICE. (Note that I added the DISCOUNTED_PRICE, which is a virtual column created by subtracting the discount from the price.) This nice thing about this view is that someone using Crystal reports will see the table name VPRICE_SHEET in the Crystal Database Explorer. When they open the table, they will see the long field names rather than the short obfuscated system names of the fields. In addition, the users will not have to deal with joining the tables themselves, as we have pre-joined the tables in the view.

When companies hire me to help make their data accessible, I encourage creation of a library where I will create all of these end user views and other representations. In this manner, when configuring access for tools like Crystal Reports or SQLThing, you can default the library view, (via the ODBC data source), to point at the end user library. Then, the users of these tools will see their initial list of views and not be bothered, or tempted, by other physical files.

Since views do not really contain any data, (they are like a non-keyed logical), there is no real maintenance penalty for keeping a view on your AS/400. I routinely create multiple views of similar data with subtle differences like some field left out of one or different set or type of calculated fields. So feel free to offer as many views over the physical files on your system as you like.

Bio: Howard F. Arner, Jr. is Vice President of Client Server Development, Inc. in Jacksonville, Florida and the author of iSeries and AS/400 SQL at Work. You can purchase a copy of his book from www.sqlthing.com