August Newsletter

More Info on Accessing Mainframe Data from PC Reporting Products

Last month, we discussed how you could use the UltraQuest Cloud Server to directly access mainframe data from familiar PC reporting tools, such as IBM Cognos and SAP Crystal Reports. We described how you can easily directly access VSAM, QSAM, DB2, IMS, and NOMAD data without the need for an extract and download.

Since then, we have written a white paper that provides even more information about this beneficial product offering. You may view this whitepaper by clicking on this link.

This month's puzzler asks you to imagine a magical function that allows you to sort parallel arrays together.

Suppose you have a table of information about candidates for office, including their name, CANDIDATE, and a set of monthly parallel arrays: MONEY_RAISED, FACEBOOK_LIKES, and POLL_PERCENT. A simple report of the table (master) shows the following information about how the candidate has done so far this year:

                        Unsorted Output                         
                                                                
CANDIDATE     Month   MONEY RAISED  FACEBOOK LIKES  POLL PERCENT
------------  ------  ------------  --------------  ------------
Bugs Bunny    Jan 15      $100,000             N/A             1
              Feb 15      $500,000             700             8
              Mar 15       $50,000           2,000            15
              Apr 15    $2,000,000          15,000            33
              May 15   $44,000,000         123,000            17
              Jun 15   $23,000,000       2,400,000            12
              Jul 15   $33,000,000       7,000,000            18
Ethan Hunt    Jan 15            $0             N/A             0
              Feb 15       $44,000               0            11
              Mar 15    $3,500,000           2,000             4
              Apr 15      $900,000           4,300            18
              May 15   $18,200,000          11,000            13
              Jun 15       $89,000          83,000            21
              Jul 15   $13,100,000         187,000            17
Peter Parker  Jan 15       $44,000             N/A             7
              Feb 15      $190,000             900            29
              Mar 15    $2,100,000           2,500            38
              Apr 15       $88,000           7,700            26
              May 15    $4,000,000          44,000            29
              Jun 15       $90,000         120,000            20
              Jul 15    $3,100,000         750,000            28


But suppose I want the rows in this report sorted by the amount of money the candidate raised and, to see its effect on their number of Facebook likes and, most importantly, their ratings in the polls. Using the above data, I want my report to look like the following:

                           Sorted Output                        
                                                                
CANDIDATE     Month   MONEY RAISED  FACEBOOK LIKES  POLL PERCENT
------------  ------  ------------  --------------  ------------
Bugs Bunny    Jan 15       $50,000           2,000            15
              Feb 15      $100,000             N/A             1
              Mar 15      $500,000             700             8
              Apr 15    $2,000,000          15,000            33
              May 15   $23,000,000       2,400,000            12
              Jun 15   $33,000,000       7,000,000            18
              Jul 15   $44,000,000         123,000            17
Ethan Hunt    Jan 15            $0             N/A             0
              Feb 15       $44,000               0            11
              Mar 15       $89,000          83,000            21
              Apr 15      $900,000           4,300            18
              May 15    $3,500,000           2,000             4
              Jun 15   $13,100,000         187,000            17
              Jul 15   $18,200,000          11,000            13
Peter Parker  Jan 15       $44,000             N/A             7
              Feb 15       $88,000           7,700            26
              Mar 15       $90,000         120,000            20
              Apr 15      $190,000             900            29
              May 15    $2,100,000           2,500            38
              Jun 15    $3,100,000         750,000            28
              Jul 15    $4,000,000          44,000            29


How could I accomplish such a miraculous task? See below for the answer!

This new video gives a short overview of UltraQuest Reporter 6.0. If you've never seen Reporter before, this is a great way to see what it can do for you! Click here to watch now.

This month's puzzler asked you what magical method would allow you to sort parallel arrays together. The answer: the amazing TAGSORT function! The TAGSORT function returns an array that contains the indices of the source array, sorted in ascending (or descending) order of its data.

In the example above, I define a new array column (item) called INDICES whose expression is: tagsort(MONEY_RAISED). INDICES thus contains the indices of MONEY_RAISED in order of how much money was raised. I then define new arrays for each of the original arrays that extract their data in INDICES order; for example, the new define for MONEY_RAISED has the expression: MONEY_RAISED(INDICES).

The TAGSORT function is available both in UltraQuest and in NOMAD. It is described in Chapter 5 in the UltraQuest and NOMAD Reference Manual and under Array Functions in UltraQuest Reporter's online help.