Finding your Libraries and Tables with Crystal Reports

Q: Howard, I just got Seagate Software’s Crystal Reports and I am having trouble getting to the files I want.  When I bring up a list of AS/400 files in the Data Explorer, I only see physical files in QGPL.  How can I get any library or file when creating a report with Crystal?

 Ah, the ever present debate, “How does Crystal find Libraries and Files?” There are a number of techniques you can use in Crystal Reports to identify a file for a report, but before I get into those let me share how Crystal determines what files to display as it’s default list when you log into the AS/400.  When you want to add a table or data source to a Crystal Report, you are presented with the Data Explorer, which presents a list of Data Sources that you can use to create a report. I typically connect to the AS/400 via an ODBC connection, so I open the ODBC folder and see a list of all of my ODBC data sources configured on my machine.  I pick an AS/400 data source, (that I have created using the ODBC Data Sources control panel applet in the windows Control Panel), and Crystal opens the data source and queries the AS/400 for physical files. What Crystal is doing behind the scenes is calling to SQLTables, an ODBC function that returns a list of Qualifiers, Owners and Tables. Translated into AS/400 speak, this is a list of AS/400 System Names, Libraries and Physical files.

Now, what you get in this list will depend on a number of things, the first of which is how you set up your ODBC Data Source.  With the Client Access Express ODBC driver, you are allowed to specify a list of libraries to search in the second tab of the control panel applet in a field called default library list.  If you put a list of libraries in this field, you will get a list of objects in those libraries in Crystal.  However, if you leave the default library list field blank, Client Access will default the entry to QGPL and only objects in QGPL will be displayed in the crystal Data Explorer.

The second thing that controls the list is the settings of Crystals Database Options page.  This page can be accessed from the File->Options menu and then select the tab Database Options.  The frame titled Explorer Options contains the fields ”Table name LIKE:” and “Owner LIKE:”.  You can use the entries in these fields to restrict or change the list of tables available in the explorer.  For example, putting the string HD1100PD in the Owner LIKE field would cause Crystal to only list objects from the library HD1100PD in the explorer.  Furthermore, placing the string ABC% in the Table name LIKE field causes Crystal to only like objects that start with the letters ABC.  Note that the percent character is a wildcard character in ODBC.

Also, there are checkboxes available on this options screen that control how the list is sorted, what types of objects to show on the list, (procedures, views, etc), and advanced options on how to treat field and file data.  See the Crystal documentation for more information on these options and how they effect processing of reports.

I should point out that this options screen is also directly available from the Database Explorer by pressing the “Options…” button.  I find this feature very handy since I have customers that keep a large number of physical files in one library.  If I set the library in the ODBC data source, Crystal can take up to 45 seconds to display the list of available objects when I open that ODBC data source to create a report.  To get around this slowness, I create a data source with no default library list just to use for creating Crystal reports. When I want to create a report, I select that appropriate data source for the target 400, (one of those data sources without a library list), and then select the option panel from the Database Explorer.  I enter the name of pattern to match in the Table name LIKE field and I enter the target library in the Owner field. This limits the list to only objects I am interested in picking and since the list is small, Crystal returns the list in less than a second.  I double click the object I desire, then if I need other objects I re-select the “Options…” button and specify another pattern to match.  This lets me identify the AS/400 tables I want quickly, without having to scroll through a list of 1,000 tables.  Finally, it also lets me select any library on the AS/400, (using the Owner LIKE field), rather than just libraries that my data source is configured to see.