Review of QueryCalc
by
Shawn M. Gordon
President
S.M.Gordon & Associates
Introduction
I have to think that QueryCalc is one of the most unique products on the HP 3000 today. It is basically an end-user report writer, but it uses a spreadsheet metaphor (123, Excel, Quattro, etc.) on the HP 3000 to do incredibly high speed reporting. This combines the comfortable interface with hi-tech retrieval mechanisms for a very robust product. I have been bugging AICS (the makers of QueryCalc) to let me do a review for almost two years, but they wanted to wait until they had put some new features in. Well the features are basically done (there are in the process of adding more options), and they have finally let me have a copy to review.
QueryCalc has been around for quit some time, but they don’t advertise a lot, and their customers tend to be among the smaller and/or non-technical shops. Their users like the spreadsheet metaphor since it is similar to what they are use to working with. This doesn’t mean that QueryCalc isn’t suited for the fast paced highly technical shop, it is an extremely robust product that allows very sophisticated data reporting.
I reviewed the system on my HP 3000 series 37 running MPE V 1P with 2 meg of RAM. I used both their demo data base and my own data to test, the screen samples will be with their sample data for consistency with any evaluation you might do on your own.
Features
Ok, here is the basic overview of what QueryCalc is. QueryCals is a 26-page, 3-dimensional spreadsheet where every cell may access up to 10 databases simultaneously. A database may be any type of IMAGE database, an MPE file that has been described in QueryCalc, or a KSAM file that has been described in QueryCalc. Each page of the spreadsheet is composed of 26 columns by 90 rows (2340 cells) for a total of 60,840 cells. This, coupled with a very English like syntax make QueryCalc appropriate for most of your reporting needs. With the description out of the way let’s get on with the review.
QueryCalc is a language-like program constructed of ‘primitives’. There are only four types of cells in QueryCalc, numeric equations, text labels, text equatioins, query questions, and only one command line. Everything in QueryCalc is built around these primitives, so once you are familiar with basic functions it is no great leap to go to much more complex functions. QueryCalc is read-only, so there is absolutely no danger involved with using it, the worst you can do is loose a report that you have written. It has such a light touch on the data base you can even use it during a backup.
Take a look at figure 1, this is a sample QueryCalc report after data has been typed in. I will now go through the dialog necessary to generate what you see in the example;
1. Type @OPENDB QCDEMO
2. Move cursor to cell B5, type: “Category
3. Move to C5, type: “1983
4. Move to D5, type: “1984
5. Move to B6, type: ‘——————————-
6. Move to B7, type: “501
7. Move to B8, type: “502
8. Move to B9, type: “503
9. Move to C7, type: @sum of amount when category is <[>b7] and date ib 830000,840000!
(You will be prompted for a data set name at this point, we will choose the set INVOICES)
10. Move to D7, type: @sum of amount when category is <[>b7] and date ib 840000,850000!
(You will be prompted for a data set name at this point, we will choose the set INVOICES)
11. Without moving, type: /rep c7:d7<;>c8:c9
12. Move to C10, type: ‘————————–
13. Move to C11, type: sum(c7:c9)!
14. Without moving, type: /rep c11<;>d11
15. Type !!a
This will give you a report comparing total amounts in various categories across different years, and on my 37 it took less than 5 seconds to run. Now let’s cover some of the keywords and syntax that was used. The double quote ” in front of a value indicates that this is text and should be left justified. The single quote ‘ indicates text that is to be right justified. The up carrot ^ indicates text that is to be centered (not in this example).
The ! as a command or ! and a letter will recalc that page. The ! is a recalculate command, it doesn’t issue the query questions. This is similar to how Lotus 123 works, but using a double exclamation point (!!) as in step 15 causes all of page ‘a’ to be recalculated, and all query questions to be executed. This gives you an opportunity to take advantage of the sophisticated syntax checking in QueryCalc with no real impact on the CPU. For programmers this is like doing compiles to check your syntax. You are also allowed very sophisticated options for recalculating portions of a spreadsheet.
The important thing to learn here is that equations do not automatically calculate on entry. A cell equation that does not end with ! is only syntax checked. A cell that has not been calculated, but only syntax checked with be marked as “<
What is interesting to not is that standard functions such as MIN, MAX, AVG, DEV, etc. are all done for you automatically when you execute a query question at no additional cost. This means you could then reference one of these functions inside of a cell and the answer would be immediate with no overhead. A very nice added bonus.
Steps 9 and 10 in our example are where the real meat is located. We are using an @function, in this case ‘sum’, which will total the value of ‘amount’ when the value of ‘catagory’ is equal to the value in cell ‘b7’ and the date is between 830000 and 840000 (giving us all of 1983). Step 10 does the same work, but it is for the year 1984. As you can see, the syntax is fairly english like, and is also similar to that found in PC style spreadsheets. The neat trick here is the ability to reference another cell as a search item. This cell could have it’s own query or calculation that would result in a value that would then be referenced back into the other query. This is where some of the real power of QueryCalc starts to come out.
The ‘/’ always prefixes a command that you want QueryCalc to perform against spreadsheet. The /rep commands allow us to do some rather sophisticated cell duplication. In the case of ‘/rep c7:d7<;>c8:c9′ we are replicating the rectangle c7 thru d7 to the range of cells c8 thru c9. It will keep replicating the source range until the destination range list is exhausted. This is one of the more sophisticated replication parameters. Step 14 will simply replicate cell c11 to cell d11. What is really powerful here is that the replication process also automatically modify the cell reference of ‘b7’ in step 9 and 10 to be first ‘b8’ and ‘b9’. You can of course keep QueryCalc from modifying the cell references too if you wish.
Here is a listing of the code that is basically generated behind the scenes for the spreadsheet. I only include it as a matter of interest, you don’t have to actually type it in like this, it is all done for you in QueryCalc.
Equations for Page A (column-wise order)
____________________
(Ac7): @Using qcdemo.invoices, sum of amount when category is <[>b7] and
date ib 830000,840000
(Ac8): @Using qcdemo.invoices, sum of amount when category is <[>B8] and
date ib 830000,840000
(Ac9): @Using qcdemo.invoices, sum of amount when category is <[>B9] and
date ib 830000,840000
(Ac11): SUM(C7:C9)
(Ad7): @Using qcdemo.invoices, sum of amount when category is <[>b7] and
date ib 840000,850000
(Ad8): @Using qcdemo.invoices, sum of amount when category is <[>B8] and
date ib 840000,850000
(Ad9): @Using qcdemo.invoices, sum of amount when category is <[>B9] and
date ib 840000,850000
(Ad11): SUM(D7:D9)
There is also a slew of functions that can be used to manipulate and use dates and times. They can be used in equations and also formatted for output. What is really interesting is that they can handle getting the date in all sorts of formats, and can change the format to just about any format you are likely to use. For example, QueryCalc can interpret dates in any of the following formats without you having to specify what the format is;
19630825 August 25, 1963
8/25/63 AUG-25/63
630825 25 Aug 1963
There are probably even some more it can handle, but that was a quick list. If you are doing some sort of date calculation, all you need to do is pass the equation to the particular date function and away you go. This is very handy if you have ever had to do any date math routines on your own.
One of the reports that I wanted to do involved reading a master data set looking for a value that was in the middle of a key (QueryCalc allows wildcards anywhere in a string, and processes it very quickly). For each master record that qualified I wanted to read the detail chain and find the entry that had the most current date in it. Although this required formulating my query in a couple of steps, it did only require one simple command on the detail chain to find records that had a max value for date. This sort of thing isn’t all that fun or easy to do in anything else I have ever used, but it was a snap in QueryCalc.
This type of report is known as a ‘Detail List Report’ in QueryCalc. This is where you need to work on data in other than a summary type fashion. This kind of changes the way the spreadsheet looks, see figure 2 for an example of this screen. There is a lot of power in this type of reporting, and with just a minimum of work it is available to you. I am not going to get into any real detail about this, but this is the option that I used to generate my report based on dates that I mentioned earlier. Just know that the feature is there when you look at the product.
Figure 3 shows an example of output that is just basically a beefed up version of Figure 1. This enhanced report was generated very quickly by just copying cells around and modifying the query questions to handle the different years. Figure 4 is pretty amazing, this comes from a real QueryCalc customer, and was built by the CFO of the company who has almost no computer experience other than doing some PC work. He does know his data however, and knew how it should look. He built this report, as well as a few other supporting reports, in about half a day, all on his own. I spoke with the gentleman for a little bit, and I was amazed at what he was able to accomplish so easily. If someone gave me that report to write in COBOL I would probably try to find some reason not to do it.
The feature that AICS has been working so hard at adding is PostScript output for graphics on reports. For those of you unfamiliar with PostScript, it is a very elegant solution to printed graphics. It is fast, and small, and very versatile. QueryCalc has a new set of commands now that let you describe graphic output, this means you can now generate pie, bar, line, etc.. charts to accompany or replace the text report. Full color is also supported, if you look at figure 5, hopefully it was reprinted in the magazine in color. This is an example of output on a Seiko $2,500 color printer. The report is generated with almost no overhead or impact to the HP since the PostScript text output is small and easily generated. The burden is placed on the printer, and it’s speed is going to be dependent on the type of printer it is. Normally the graphic has to be built on the HP and then the huge output is downloaded to the printer, a very slow and high overhead operation.
I know from experience what a hassle it is to create a report, export it to a PC spreadsheet or data base program, generate a graph and then print the graph. Usually the quality isn’t very good, and the time involved no matter how much I automate it, seems to be excessive. These QueryCalc reports once defined can be quickly and easily run any time you want, always giving you high quality output. The neat thing about PostScript is it will always print as well as the printer allows without any changes on your part. This is really the most exciting thing that has been done to QueryCalc, and I think most users will get tremendous use out of it. The cost of a PostScript printer is down around $1,000, with color being available for not much more.
There are a series of financial functions to quickly and easily calculate things such as cashflow, net present value, investment rate of return, and depreciation. There is also an entire macro language that is very BASIC like. This let’s you create logic modules that can be executed in session or in batch. There are also commands to do graphics on screen like drawing boxes, or for generating PostScript graphic output.
Let’s talk a little about what kind of logic QueryCalc can handle, how it deal’s with wildcards, and some of the tricks you can use for superfast retrievals. First let’s go over ‘Search Sets’, this is a function that let’s you define records as ‘sets’ on the basis of common attributes. That may sound a little confusing at first, but here is some example logic that should help explain the reason and the concept behind ‘Search Sets’.
First we generate the Search Set;
@@USING EMPLOYEES, STORE IN !A SOCSECNUM WHEN ZIP IS 88047!
This will scan the EMPLOYEES data set and retrieve all the social security numbers for records that have a zip code of 88047. The ‘store in !a’ means we are going to save these entries in search set ‘A’. There can be 26 search sets at any given time from A to Z. Now we can use the result of the search set in another query;
@@USING PAYRECORD, SUM OF GROSS WHEN SOCSECNUM IS !A AND DATE IB, 840101,841231!
This will scan through the PAYRECORD data set totalling GROSS for all the social security numbers that qualified in the previous USING statement (we saved them in !A and reference them in the test statement), for all records with a date in calendar 1984. So now you have the gross wages paid to people in this zip code for 1984. We could then calculate the average number of regular hours each of these people worked using a different dataset, but the same search set.
@@USING LABOR, AVG OF REGULAR WHEN SOCSECNUM=!A!
As you can see this gives you a way to make dynamic selection criteria. What is really nice is after the initial creation of the search set, all subsequent searches using it will be exceptionally fast. You can even use this tool to generate subsets of data as in the following;
@@USING EMPLOYEES, STORE IN !A IDNUMBER WHEN TERMDATE<<999999 AND STARTDATE<;>=920601
@@USING EMPLOYEES, STORE IN !B IDNUMBER WHEN SEX IS M AND IDNUMBER=!A
@@USING EMPLOYEES STORE IN !C IDNUMBER WHEN IDNUMBER IS !B AND RIGHT-HANDED IS N
You now have three different sets of data that can be used for various functions. How about if you want to look at a subset of information in a field as a search item?
@@using part-detail, sum of qty-on-hand when part-key(6,8) is 450
That statement will find all the records whose part key is 450 in bytes 6, 7 and 8. A generic search can be done in the following format using a wildcard character.
@@using employee-id, store in !a socsecnum when socsecnum=58@
You can also use boolean logic to create a union of sets, a subtract of sets, the intersection of sets (common values), and exclusion of sets. When creating search sets duplicates are always removed. So what we have found out is that we can create a ‘Search Set’, a subset of a search set, perform generic searchs for data, do partial-key searches, and perform algebraic functions on sets
I would be remiss if I didn’t mention a little free product that they include, Equater. This has been floating around in the HP community for years. I know Robelle and Adager have copies in their software accounts. This program is a four function calculator, it includes Financial, Statistical, Scientific, and Programmer modes. You can do pretty much any math that you can think of with this baby. There is a function inside there that does measurment conversions. This goes way beyond simple metric conversions, there were functions for magnetic fields, luminesence, mass, power, velocity, time, etc. There are all sorts of universal match and chemical tables included. It’s really a nice little environment, I hate to admit how much time I spent messing around with the conversion tables.
I could go on forever, but I think I have given you a decent idea of what is available in QueryCalc. This is by no means exhaustive, but is meant to give you a good overview of the product. Hopefully I have done it justice.
Usability (also installation)
Installation is done a little bit differently, you restore a couple of files into PUB.SYS, but instead of modifying and streaming a job, you run a program that will build the install job and then stream it. Installation went smoothly and took about 5 minutes. I want to thank AICS for going to the extra trouble of generating the old 9144 cartridge tape for me. They didn’t have one anymore (hardly anyone does) and because they wanted to get me the sample data base as well, they went the extra mile.
As soon as you warm to the spreadsheet style interface and learn the basic commands you will be zipping right along. It is very simple to start pulling together nice reports very quickly after you get past the short learning curve. You could probably start creating reports by just looking at the help, but the product does have a syntax and command structure so their is no way to just sit down and do a report without at least spending 10 minutes with the demo guide.
Reliability
AICS strikes me as some of the last true perfectionists left. This isn’t meant as a slam against anyone else, but I think a lot of us have started to lower our standards of software quality and testing, “let the users find the bugs” has started to become a bit more prevalent in recent years in many industries. I didn’t run into this mentality with AICS, their software is so robust and solid that I was truly impressed with the time and effort that must go into their product. I don’t think you will ever have a reliability problem with QueryCalc, I certainly didn’t.
Performance
Performance in QueryCalc is nothing short of stunning. They are doing all sorts of interesting techno tricks, like building dynamic indexes on the fly, MR NOBUF reads of data sets, and some other things I am not aware of. The net result is blazing performance with moderate CPU impact.
What is truly amazing is the output they can generate with PostScript. You can get some truly beautiful output with almost zero overhead. PostScript is very low overhead to begin with, and it has been wonderfully incorporated into QueryCalc. There are going to be some very neat tricks put into QueryCalc in the near future by taking advantage of PostScript.
Supportability (including Doc)
Support from AICS is top notch, the company originally started as a service bureau and this service philosophy has continued and extended to their current business. Not only are they good with answering your questions, but they seem to know pretty much everything there is to know about the HP in general. The support philosophy at AICS is that you should never be confused for more than 10 minutes. In other words, call as often as you need or want. They offer on-site training, but they don’t recommend that you take it, they would rather that you called their 800 number and ask as many questions as you want. Amazing.
During the course of this review AICS added a Fax Request System. This means that you can call a number, request a document, and it will be faxed back to you within minutes. I used it to get a copy of their index, and it worked perfectly. What was interesting is that the index is actually generated in QueryCalc and faxed right off the HP 3000. You can get all sorts of information about QueryCalc, the QueryCalc newsletter, or other general information about HP’s and such. I have used Fax Back from Novelle, and some other companies to get information, and I think it is a nice emerging technology, it’s much faster than waiting for the mail.
The documentation is impressive, you will receive a little tutorial guide that will get you started with QueryCalc basics. You will also receive a reference guide that is about 60 pages of indexed commands, this is also available as on-line help in the product, but in a sort of hyper-text format. For example, if you look for help on ‘WRITE’ you will get the help for ‘PRINT’ since it is the closest analog. You will also get an ‘Applications Guide’, which starts out with explaining the HP and data bases in general, and then moving on to a description of Spreadsheets. This serves as the building blocks to understanding and creating your first reports. I enjoyed reading this manual just for it’s detail and elegance. At the end of chapter 6 you even get some User Exercises like you would in a text book.
I spoke with some of their users to get an idea of how they felt about the product, and everyone I talked to was extremely enthusiastic. Most of them weren’t even data processing people, they were people like CFO’s, and accountants, people who really understand their data and how they wanted to see it.
Summary
As much as I like QueryCalc, I do have a copy of things that I would like to see be done differently, but they are sort of trivial. The program that loads databases into the QueryCalc dictionary requires that you enter a fully qualified file name, it would be nice if you could use wildcards since most people put all their data bases in one or two groups. I had over 30 data bases at one place I worked, and in that scenario a wild card approach would be very convenient, you can however put the load process into a job stream, so you could redirect LISTF to generate a list. To be fair, I am the first person to ask for this.
The second thing that I would like to see different is the use of the the arrow and page keys to traverse the spreadsheet instead of using the function keys. Using the cursor control keys would make the product more like PC spreadsheets, not to mention that it is easier to grab the cursor control keys than match up function keys. I understand the technical reasons behind not doing it, and they have to do with how the HP terminal deals with these things, but it can’t keep me from wishing.
Although you do need to get used to the syntax and commands and some of the tricks in QueryCalc, the payoff is tremendous. The performance, the versatility, everything is just amazing. I can’t stress enough how much I was impressed by the support offered by AICS, it is truly the most generous and helpful I have ever received. I just hope that I was able to cover the product enough for you to get a true idea of what it is and how it works.
I have reviewed a number of report writers over the years, and whether you like one or not is pretty subjective. QueryCalc is different, it is much more powerful and sophisticated in some areas than any other report writer I have seen, in other ways it’s not as intuitive as some of the emerging software available. I do recommend it highly for it’s simplicity and performance, usually you trade off performance for speed of development with report writers, this is not the case with QueryCalc, it is going to execute faster than most of your compilied 3GL code will, and be much much faster to write. The more I used this product the more impressed I was by it’s versatility and ability This is a definite ‘must see’.
At-a-Glance box
QueryCalc version 3.0
AICS Research Inc.
P.O.Box 4691
University Park, New Mexico 8803
Phone 505-524-9800 or 800-AICS-INC
FAX 505-526-4700.
Fax Request Line 505-526-4800
Call, write or FAX for information or a demo. Price ranges from 3,400 to 17,000 depending on CPU, additional copies range from 1,700 to 5,670 depending on CPU. The first year support is included, support is then 10% of purchase price per year after that. On-site training is also available for $1,800 for two days.