AS400 file data not visible in Crystal Reports

[NoseDive]

Hi,

I'm running Crystal Reports 10 and AS/400 (OS400 v5r1) and am using Client Access ODBC to report on AS/400 DB2 files.

I have no problems with most files but in some cases I can see and connect to a file in database manager, but not see any data when I insert the fields from that file into my report. (Yes, I have checked that there is data in the DB2 file). Any help/advice would be much appreciated - thanks.

 

[NoseDive]

Hmmm... interesting.

I found out why no data is appearing in the report.

My ODBC data source is pointing at AS/400 data and the filename exists in several libraries. Despite the fact that I am pointing to a file in a specified library, via Crystal's database expert, the report is actually picking up another instance of the file (which contains no data) in another AS/400 library. Possibly using a library list - I don't know - but I assumed that if I selected the file from the correct library in Crystal's database expert then that's the version Crystal should be using - any ideas?

 

[fredp1]

Check you ODBC connection.

With client Access express. I normally have the librray name in 3 places... Twice under the General Tab for SQL Default Library and Library List(although library list can be blank)
and under under the Packages tab for Package Library.
Also do not forget to change the commit mode to commit immediate in the General, Advanced button.
Also Check the convert binary data to CCISD 65535 in the translation tabe.
Also under general, connection options,Default User id I nomally have as none; Signon Dialog prompting is never; and Security do not use SSL.



Cheers
Fred

 

[NoseDive]

Thanks for the advice Fred. If I set up the ODBC as you suggest it is fine. The problem is that we are trying to set up a data source to use as a general reporting data source over our live AS/400 libraries (about 10 of them)so we have set up a default library list on a Client Access Express ODBC data source. However, Crystal seems to ignore this library list sequence and sequences the libraries, in Database Expert, alphabetically. When I select a file/table from a library at the bottom of the list, Crystal ignores the library name altogether and takes the first file it finds in this (alphabetical) 'library list' that Crystal displays.

It seems to me that there should be some sort of database file override in Crystal to force the report to look at the selected version of the file rather than whichever one the application feels like.

 

[fredp1]

Thats a nuisance.
Whats probably happening is, in the location for the table in Crystal, it has hard coded the 'first' library name. I would expect it to be a top down sort order, as per the library list.
AS you may now know, you can do Set Location under the database menu to specifiy a library.
I wasn't aware that you could have a specific versions for files on the as/400.

Cheers
Fred