Terminal Emulation & Transformation Community
  • Home
  • Blogs
  • FORUM
  • About
  • Contact
  • Resources

Building Reports in Excel using IBM PCOMM Automation API

4/13/2018

4 Comments

 
Picture
Introduction
​
IBM Personal Communications provides an emulator interface to communicate with IBM Mainframe/AS400/VT sessions. This is used for reading & updating host data and interfacing host with other applications. Performing manual tasks on emulator screen increases in transaction and becomes repetitive, this over a long term would have issues:
​

-          Inefficient
-          Redundant and hence error prone
-          Time consuming and thus expensive


Consider a business scenario where bank employees, on a regular basis, build reports on their customer’s financial information by querying their credit limit, balance due etc. This report building operation involves below set of activities:

a.     Execute macros on the emulator screen to perform certain tasks.
b.     Query database to read customers’ credit and balance amount.
c.     Generate the final report by exporting query results to a text or CSV file.

Performing manual operations to generate such reports is inefficient and prone to errors. This is where automation, in the form of API’s can help.

Exploring IBM PCOMM Automation API
In this post we will look at a sample application that uses PCOMM Automation API’s in a Microsoft Excel sheet. This application can start multiple PCOMM sessions or connect to the existing ones. On a selected session it can execute PCOMM macros (.mac) and extract information from the emulator screen to generate a user report.

This application is divided into two sections:
A.      Session Manager:
                a.       Use ‘Connect Session’ dropdown list to start a new emulator session or connect                             to an existing one.
                b.       Use ‘Stop Session’ to gracefully terminate any active session which was started                             using this application.
B.      Generate Report:
                a.       Use ‘Select Session’ to connect to a session started using above steps.
                b.       Select macro (.mac) from ‘Table Definition’ list which will log into the Host                                        session  and move to the table definition page.
                c.        Select macro (.mac) from ‘Query Window’ list to move to table query page.
                d.        Click on ‘Generate report’ to collect Customers information from the Emulator                                  page.
Session Manager
​
A.      Starting PCOMM Session: The PCOMM session can be launched by couple of methods:
    a.         Using PCSAPI, ‘pcsStartSession’:
Picture
   b.         Using HACL API, ‘autECLConnMgr.StartConnection’:
Picture
Once the session is started, use PCOMM HACL API to connect to it:
Picture
B.     Stopping PCOMM Session: The session started above can be stopped using either of the              methods below:
         a.      Using PCSAPI, ‘pcsStopSession’
:
Picture
         b.      Using HACL API, ‘autECLConnMgr.StopConnection’:
Picture
C.      PCOMM Session Management: In this application, user can select any session to generate          the report. To manage these sessions, we should associate each session with its PCOMM            connection objects. To do so, use Dictionary objects to store session name as a key and                session objects as its value.

In an array store the newly created autECLPS and autECLOIA objects. As shown below, add session name (A-Z, a-z) as a Key to the dictionary and the array as its value:

Picture
Once user selects a session to generate a report, get the PCOMM Session objects from the dictionary ‘SessionObjsDict’ by providing ‘sessionName’ as a key (described in section below).

​Generate Report
A.      In this section, user generates a report by running macros on the Host and performs                    query  on the host table using Excel User Form. Once user selects a session under ‘Select              Session’, application retrieves the session objects from the ‘SessionObjsDict’ dictionary:
Picture
B.      To execute any macro (.mac) file in the Host presentation space, use autECLPS API                      ‘StartMacro’:
Picture
C.      User Form ‘Generate Report’ displays the column list of a table which has been read from            a table inside the Emulator screen. In this User Form, user can select the column which                they want to view in the final report and can add filters on the columns ‘Balance Due’                  (BALDUE) or ‘Credit Due’ (CDTDUE).

By using PCOMM HACL API’s, the data is read from the Emulator presentation space and is stored in the Excel sheet.
Picture
For more information on PCOMM Automation API, please refer to PCOMM documentation.

Below is link to a video that shows a demo of this application.
https://www.youtube.com/watch?v=i30L2x-8Q5E&feature=youtu.be​

Contact:
Please email zServices@hcl.com  or HCL-HI-LabServices@hcl.com to get more information on Services offerings.

Badal Burathoki
Technical Architect, Lab Services, IBM HACP & HATS
badal.b@hcl.com
4 Comments
Vatsala R
5/10/2018 12:06:30 pm

A very useful blog that describes a powerful feature in IBM Personal Communications

Reply
selladurai
12/1/2018 03:41:28 am

Hi,
Your articles are very very good and helpful . I have a doubt. In my project we are using Host Access
software to access mainframe 3270...something HLLAPI ..

I would like to automate it using vba macro. but i dont know how to find the object name..(set createobject("<<Thisname>>"))

please help how to find this name ..

While search in internet other softwares are there like pcom,extras etc..

Reply
Best Linux & Windows Mail Server – SurgeMail link
6/24/2020 01:40:51 am

The software issues and making the wonderful server topics always, the software methods and mailing info as well. We all are using the site for interesting techniques with us.

Reply
Sravya
8/18/2020 05:00:18 am

Please provide me the excel, it’s so useful and important.
Thank you

Reply



Leave a Reply.

    Archives

    May 2020
    October 2019
    July 2019
    June 2019
    May 2019
    March 2019
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    December 2017
    October 2017
    September 2017

    Categories

    All
    HACP
    HACPEE
    HATS
    HOD
    PCOMM

    RSS Feed

Proudly powered by Weebly
  • Home
  • Blogs
  • FORUM
  • About
  • Contact
  • Resources