Retrieving data from RPGLE programs into Crystal Reports

RSchnabel
RSchnabel used Ask the Experts™
on
I have been using Crystal Reports to report data from DB2 tables on an iseries for a long time, both using the database wizard and writing my own SQL commands. What I would now like to do is to call a utility program on the Power 9 and retrieve the output and incorporate it into a Select statement in CR to then display in a report. I will need to pass a number of values including both strings and numbers. My first thought was that maybe there is a way crystal can use a stored procedure syntax to call an rpgle program, but I have no idea how this will work, and I have not been able to find an example or two. Can you point me in the right direction? One of my goals is to utilize programs for calculating shipping & tax charges that already exist on the power 9 box by passing in customer numbers and shipping terms and methods, and order info.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
In my opinion you could use  parameterized crystal reports as described here https://www.kb.blackbaud.com/articles/Article/54043 or create view(s) in DB2 and use them as for example as described here https://stackoverflow.com/questions/18793569/crystal-reports-can-it-use-a-stored-procedure
Also found the way to call a stored procedure but as I don't have access to CR+DB2 at the moment I can't test it although the steps should be like go to database expert, select your connection and click on add command then in there you need to create parameters the parameters needed for your stored procedure call which would be like:

{CALL 'schema_name'.'sp_name'({?delay_time})}
where 'schema_name'.'sp_name' is the stored procedure and and 'delay_time' is the parameter.
VP Technology / Senior Consultant
Commented:
How does the program you want to call return results?  Spooled file?  5250 display?  Parameters?  If the program passes parameters, then you can wrap it as an external stored procedure and call it from CR.

Article explaining how to call a SP from CR:

https://flylib.com/books/en/4.229.1.100/1/

Here is a good IBM article on creating external stored procs based on an RPG program:

http://ibmsystemsmag.com/ibmi/developer/rpg/rpg_stored_procedures/

Other useful info from IBM knowledge-base:

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzaik/rzaikspex.htm

Author

Commented:
Thank you both for your help.  Gary your links were very helpful as they not only told you what you can do, but also how you do it. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial