Crystal Reports and AS/400

[Former Member]

Using Crystal Reports including Enterprise with AS/400 data, can you discuss any problems or issues which occurs in your experience, any tips

for writing reports, and for Enterprise any special needs or issues that crop up when dealing with the AS/400.

What are the best ways that you access the AS/400 data? What major issues have you encountered and how did you resolve.

Thank you kindly,

Paul

[Former Member · Accepted solution]

AS/400 does not support multiple parameters, I'm using query to tables, not stored procedure.

[Former Member · Accepted solution]

Stone, Can you explain that? What do you mean by Multiple Parameters? Of course, querying the table is a better way. I was thinking that on the AS/400 you can use Logical views for a quicker response. One of the issues I see with SQL Server and Oracle is how long it takes to read the data.

[Former Member · Accepted solution]

Paul, I can't create view on AS/400, I only have read only account allow me to query tables. The Multiple Parameters means in a query, I can have more than one parameter, for example:

select *  from Item_Master where itemcode >= '{?itemCode}' and dateCreate >= '{?dateCreate}'' and createBy = '{?createBy}'

In this query I have three parameters: ?itemCode, ?dateCreate and ?createBy. By this query won't work with AS/400.

The driver we use to retrieve data from AS/400 comes with "IBM iSeries Access for Windows V5R3", I don't know the name excatly, but in my connection string it's Provider=IBMDA400. The performance was not bad.

[Former Member · Accepted solution]

Stone, you mean the query with more than 1 parameters, won't work in Crystal Reports query to AS/400 data? But it would work on SQL Server?

Because you can do this query on the AS/400 itself. So that's a limitation to using AS/400 with CR, although most people would be able to create the view they need on the AS/400.

THis has to do with staging of data for quicker retrieve. Alot depends on the need for the data.

In one place I worked the data was always up to the previous day, so we could stage the data prior to business day. But if the need is for absolute current data, that is where you have the problem.

in SQL Server, to make that happen would have meant to run the staging job which took 10-15 minutes, Would the user wait this long?

So only other way is the direct query to actualy tables but if you have the Logical view on the 400 you are fine.

[former_member183750]

Hello Paul

To add to Brian's pointers - just an FYI. If you use the search string 'as/400 crystal' (search box in top right corner), you'll get a number of hits. From KBAs, to blogs to discussions and more.

- Ludek

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[former_member292966]

Hi Paul,

I haven't had problems with reporting from an AS/400 database before.  Any complicated queries I preferred using a view or Stored Procedure instead of querying the tables directly.  Sometimes Crystal doesn't take the most direct path to get the data, even if you link tables properly. 

The one thing I will say is most AS/400 shops would prefer reporting not happen against the AS/400 directly because reports can add quite a bit of processing overhead as well as locking. 

Setting up a data warehouse and running reports from the data warehouse makes most DBAs happy because the data warehouse can be refreshed during off-peak hours.  This will improve the performance of your reports and give you the freedom the work with the data without involving RPG programmers. 

Good luck,

Brian