I thought I’d write about the report writer that almost no one has heard of: Cyberquery. In fact, its so low on the radar that searching on indeed.com yielded just 13 results (5 and 2 of which were duplicates) meaning that there were a total of 8 advertised jobs in the entire United States. In fact, a quick Google search shows that no one has ever just written about it, including it’s manufacturer. So, not only am I part of an “elite” group, writing is close to an internet first.
Cyberquery is a tool developed by a company called Cyberscience. It’s basic function is as a report writer where it does all the normal things like create HTML, XLS, TXT or PDF based reports. These reports can be automated to run, the results emailed to specified users and you can, somewhat, format them (this isn’t a strong point of the product but it’ll do 95% of what the average report needs). It has 2 approaches to report design which are a text based approach using the Cyberquery language and a GUI based tool, which frankly, I almost never used.
A basic Cyberquery would look something like the following:
This is the equivalent of
SELECT cust_name, address
You can also do summary reports using the sum command.
This would produce a summary report showing the total extended amounts of all invoices by SKU for all time and produce it in Excel (/xls) tells it to do this.
One of the interesting, and sometimes painful, things that Cyberquery does is auto-join tables based on the indexes in each table. For instance, it will auto-join between an invoice header table and an invoice detail table using what it determines as a logic choice. You can, sometimes, change that join just by changing the order of the results (list/sum clause) around. It’s kind of an interesting art but it works well enough. You can also override this behavior and force a join between two tables with a DEFINE statement. Interestingly, it used the old [+] syntax to specify a left or right join.
All of this, however, is pretty normal stuff. In my opinion what made CQ powerful was it’s ability to generate hold files and run macro files.
A hold file is a flat file and it is very, very fast. For instance, pulling sales data out of our system could be brutally, painfully slow. I once had to get will call data out of the system and it took me 2 hours for each run. With a hold file, you would run it one time to capture the data, then daily to capture that day’s worth of transactions. That really isn’t new, in the sense that this is done in most data warehouses, but the flat files really are faster than what you get going directly at a database.
Macro files are exactly what they sound like: a series of commands executed by CQ. With a macro file you could, literally, build a CSV file on disk, name it, change the name, and copy it somewhere. They allow you to work at the command line. Or, you could build multiple hold files, in sequence, and combine them in a final report. I once chained 10 hold files together to produce inventory data (I was combining sales and inventory data and needed to do each calculation separately in CQ before merging them back together).
The one real weakness I saw with the product is that I couldn’t write SQL with it. It has a tool to show you the SQL underlying a CQ, but, it often produces nonsense. You’ll pretty much have to turn off your SQL mind when you use CQ but, to be honest, writing CQ becomes second nature after a few weeks.
Overall, I found the product decent and reasonably powerful to work with. The biggest problem with the product is probably that almost no one is using it. When you compare it to virtually any other reporting tool, especially from a career perspective, you’d be much better off using, well, any other tool. But, when you compare it to the other report writers out there, in the right hands, it can hold it’s own.