Extracting Employee Data with NatQuery for Excel | End-User Guide

1. An End-User Perspective On Using NatQuery Building an End-User Query www.natworks-inc.com T. 802 485 6112

2. In this example NatQuery is being run against ADABAS on Linux in a PC network environment. The following screens may differ slightly when used in your environment however the processing will be similar. NatQuery is a workstation-based tool with a Graphical User Interface (GUI) that allows an End-User to easily extract data from an ADABAS / Natural environment, and then integrate this data into common workstation tools. Shown above is the NatQuery “desktop”.

3. For the purpose of this walk-through, let’s assume that a user wants data on a select group of employees who have the last name of “SMITH” and who live in; “BREMEN”, DERBY” or “BARCELONA”. The user would like this extracted data in an Microsoft Excel spreadsheet.

4. To begin the process of creating this query extract; on the open NatQuery desktop a user would click on the New Query icon. This will result in the presentation of a Select File window.

5. The Select File window presents all files that have been made available to the user by the Administrator. These files can be either ADABAS files or server based sequential files.

6. To select the Employees file for data extraction, the user would click on it, and would then click on the Select button or alternatively, double click on the File name. Either action would invoke the Select Fields from Employees file window, as seen in the next slide.

7. The Select Fields from Employees window displays two “panes”; the left pane will list all fields that are available for selection and the right pane will show fields already selected. Through the Select Fields from Employees window, the user will indicate which fields that they wish to have extracted from ADABAS.

8. To select a field for extraction, the user can simply double-click the field of interest in the left pane, and NatQuery will add this field into the right pane. Alternatively, this user is about to add the field City to their list using the Add button.To assist the user in locating a specific field, the user may invoke the Search function to quickly locate the field of interest.

9. Predict is a Data Dictionary provided by Software AG. If available, Predict can be used to answer the users questions on what a given field is or represents. Right-clicking on the field of interest in the Select Fields from Employees window invokes the Predict Information for Field X.

10. Once all of the required fields are selected, the user would then click the OK button, this will invoke the Select Files window.

11. In displaying the Select File window for the second time, the user can incorporate fields from other Files into the extract. NatQuery will automatically reduced the list of available files to only those that can be logically accessed (linked) from the Employees file. Available End user Files and their respective relationships are governed by the Administrator.

12. The user does not need to incorporate any other Files to get the necessary data. They will now click the Continue button.

13. The Selection Logic window allows the user to specify the selection criteria which will be applied in the query. In this example the Select Record From option will default to the EMPLOYEES File. In Selection Mode the user should click the Apply Selection Logic button because they wish to retrieve specific data.

14. The user can now enter the first selection logic statement; this is done by clicking the “NAME” field, clicking the “Equals” operator, then clicking the Constant button; this will invoke a window entitled Define Constant, as seen on the next screen.

15. With the Define Constant window displayed, the user can now enter the value of “SMITH” (the Constant variable). Once entered the user would then click the OK button to return to the Selection Logic window.

16. Having supplied the query with the EMPLOYEE name “SMITH” the user would then click the AND button in order to add the rest of the needed selection logic. By default the Select Records From shows Employees and the Selection Mode is set at Apply Selection Mode. The user can immediately use the Selection Logic to Apply frame to complete the query which was to find specific Cities.

17. The second Selection Logic statement is accomplish by first selecting the CITY field, then clicking on the Equals operator, and then clicking the List button. This invokes the Define List Variable window to allow the entry of necessary OR logic; use the Cites of “DERBY”, BREMEN”, or “BARCELONA”.

18. The User has now built a query that reads – “Find all Employees named SMITH who live in DERBY, BREMEN, or BARCELONA.” Since this completes the Selection Logic behind the original query, the user can now click the OK button to close the Selection Logic window.

19. The query is now ready to “Send to Server.” In most cases this is an automated FTP over TCP/IP process that puts the request into a batch process on the server.

20. For all new query’s, Clicking the “Send to Server” icon invokes the Query Description window. Here the user can change the program name, modify the default short description, and provide a unique long description. This will aid in referencing the query at a later date. The Favorite checkbox is intended for queries that are used often or hold some significance for the user.

21. The Send To Server Options window allows the user to designate the “target” (Extract Type) for the data to be extracted too. In our example the user has selected Download into Excel. Note that this query now has a name. Most interactive windows will have default settings that have been designated by the Administrator. In limited instances these can be overridden or modified by the user.

22. Column Headers The user is now allowed to review and Edit the default “Column Headers” that will be used in Excel. When the user is satisfied with the column names, the user would click the OK button. Our user wishes to change the Name field.

23. The user starts by clicking on the Field name to highlight it and then clicking on the Edit button. In the Output Field Information window the user can alter the name and the format, in some cases they can also change the Length.

24. With the column headers specified, the query is now ready to be sent to the remote server, and the user is prompted for confirmation to do this. Note that in the Query Selected Fields window File01 (EMPLOYEES) NAME (LAST_NAME) looks unchanged.

25. Here as a level of security, the user is asked to supply their unique FTP password.

26. This message tells the user they may immediately execute their query or have it executed manually at a later time.

27. Here the user is being alerted that their query has been sent to the server for execution. The user will find their data results in Active Request slot 1 of the Check Server window.

28. Clicking the Check Server icon will result in the display of the Check Server window. This window will provide information on the extraction process and any results that might be available.

29. Here we see the Check Server window, with this window initially displaying information about any Query extracts that have been sent to the server. This display currently shows the query just submitted as having a status of “PENDING”. To see if this status has changed, the user would click the Check Server for Update button.

30. Clicking the Check Server for Update button will tell the user if any requests that were previously sent have been processed. Here we see that the request which previously was showing as “PENDING” now shows as “DONE”. The Retrieve Request Output and Clear Selected Request buttons are now enabled. Hovering the mouse pointer over a slots’ results will display additional information concerning the extracted data.

31. Subsequent to clicking the Retrieve Request Output button, NatQuery will now prompt the user for a directory and file name into which the extracted data should be downloaded. A default “Output” directory and file name is automatically provided, which the user can change if they desire. Usually, the user only needs to click the Save button to initiate the download.

32. NatQuery is recapping the download information: The name of the query, that it was successfully downloaded, and were the user can find it on their PC.

33. With the initial download of data completed, the user is now being reminded that their original intent was to download data into an Microsoft Excel™ spreadsheet.

34. By Clicking the Yes button, NatQuery will automatically invoke Excel. The user will then be presented with the requested data. Note that the Query Selected Fields including the Edited Field “Name” are the column headers.

35. The user is free to return to NatQuery where they will find the Check Server window no longer displays the queries status and they may close this window.

36. The user is returned to the NatQuery desktop where their open query is still displayed. This new query can now be saved, modified, or closed. If the query is not saved it will be deleted completely. If it is saved the user will be able to access it at a later date by using the Open Query icon. To close this query the user will click the Close Query icon.

37. Being a new query, clicking the Close Query icon will prompt NatQuery to ask if this query should first be saved. If this query is waiting to be processed it can still be found on the Check Server window if the user choose the No option but it will not show up in the users list of saved queries unless they click the Yes button.

38. This walk through shows the ease with which a non-programmer / user, can access and retrieve ADABAS data. The NatQuery tool offers much more functionality then we can showcase here. We encourage you to open a dialog with NatWorks or your IT solutions provider and begin a Trial at your site today!

39. Where do you need Your ADABAS Data today? URL: www.natworks-inc.comE-Mail: info@natworks-inc.com454 South Main Street Northfield, VT 05663 T. 802 485-6112