Retrieving data from RPGLE programs into Crystal Reports

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.
RSchnabelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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.
Gary PattersonVP 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RSchnabelAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.