10 Jan

Cyberquery Report Writer

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:

list
cust:cust_name
cust:address

This is the equivalent of

SELECT cust_name, address
FROM cust

You can also do summary reports using the sum command.

sum/xls
oe_line:ext_price

by
oe_line:sku

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.

21 thoughts on “Cyberquery Report Writer

  1. Hi Erik,

    Your post was very helpful to start us with understanding basics about cyberquery.we couldn’t find anything in internet other than your post. Thanks a lot.

    can you reply how to find distinct values from a table column in cyberquery. Let us say, i have a table cnote and a field cont_stat

    following query diesn’t give me distinct values. are there any way to find distinct value?
    —————–
    where cont_stat not one of ‘S’, ‘?’, ‘C’, ‘R’, ‘V’

    list/domain=’cnote’/nodefaults/noformfeeds/pagelength=0/nocommas
    cnote:cont_stat/noduplicates
    —————–

    • Hi Hari,

      It’s been 2 years since I’ve used the product but I’ll give it a shot.

      From what I remember, /noduplicates led to much swearing, aggravation and it didn’t work. I do have another way to try the code, which I doubt will work, and one other way of doing it, that will work, but it’s schlocky. Try the following:

      /list/domain=”cnote”/noduplicates

      I think that will run. If you can’t /noduplicates at the list statement, it would still be worth a try at the column statement. I doubt the domain will matter but I would try it.

      Since that probably won’t work, try the following:

      sum/domain=”cnote”
      1

      by
      cont_stat

      If you can live with that, which is a rotten, awful, cheesy workaround, it will give you a list of distinct values. If you need to join on them you could create a hold file (/hold), assuming your version of CQ lets you, and then use that file to run a join against it.

      I don’t have access to the product anymore, or the manuals, so this is all I’ve got. I even tried to look up a couple of places that were tied into our old ERP vendor (who did our CQ support) and they’re gone too.

      Good luck.

  2. Hi Erik,

    I am using one macro file which contains one .eq file and one hold file. When i run the macro file only the records from .eq file gets generated and the records from hold file remains blank.

    But when i run the hold file before macro and then run the macro then the result are proper.

    • Hi Rups,

      I seem to remember doing what you describe. In fact, I can remember building several hold files followed by a regular .eq report. Something like,

      holdfile1.eq
      holdfile2.eq
      report.eq

      Macro files are, more or less, just a regular shell script.

      However, you can schedule jobs to run simultaneously. For example,

      http://stackoverflow.com/questions/4445846/bash-script-order-of-execution

      And, outside of Unix/Linux your mileage may greatly vary.

      If that doesn’t help then you could just run them separately. I kind of preferred to build my hold files and do the reporting separately. It felt cleaner to me.

  3. Hello, Erik! Your web site has been very helpful!

    Can you help me with this Outer Join? I need a report of all inactive items with no sales.

    I’ve copied the query below. AUTHORIZED_PPAL is the dominant table, containing PN (Principal/Supplier number) and IN (Item Number). As there is no relationship between AUTHORIZED_PPAL and ORDER_HEADER, I’ve used the DEFINE statement to establish a relationship. ORDER_HEADER has the PN. ORDER_DETAIL has both IN and PN. So I want to find inactive Principals/Suppliers in AUTHORIZED_PPAL where the item does not exist at all in ORDER_DETAIL. In the query below, I’ve listed three specific items for which I know there is one order. I would not expect to see them in the resulting report, but they do appear.

    Here’s the query:
    define file ord = access order_header, set order_header:pn = authorized_ppal:pn, exact

    define file ordd = access order_detail, set order_detail:or = ord:or,
    order_detail:in = authorized_ppal:in, exact
    where not(authorized_ppal:in = (+) ordd:in)
    and item:item_status = “I”
    and val(authorized_ppal:in) <> 07980124613
    and val(authorized_ppal:in) one of 0468710105, 0468712105, 0468712206

    list/domain=”authorized_ppal”
    authorized_ppal:in/duplicates pn/duplicates item_status/duplicates
    item:item_status/duplicates description/duplicates brand/duplicates
    ord:or
    (+)
    ordd:or in line_num pn
    sorted by authorized_ppal:in pn

    Thank you! I look forward to hearing from you.

    • Hi Deb,

      I’m really, really rusty with Cyberquery at this point. I don’t even have a working installation to play with, or a manual, and joins, well, they never worked the way I wanted them to if I wanted a Left or right join.

      You can view the SQL although I don’t remember the command which might help you see what it’s doing. It’ll be ugly but sometimes Cyberquery gets creative on it’s own and gets smarter than you want it to be. I also think you can skip the order_header table if I’m reading your description correctly. That might help when viewing the SQL and getting Cyberquery to behave.

      I’ll look at again later this weekend.

  4. Cyberquery is a horrible horrible piece of software. I can make it do anything I want it to, and am extremely experienced in its use, but the hoops you have to jump through to get it to do simple things SQL can handle with great ease is phenomenal. Not to mention the fact the product is riddled with bugs, and the fixes applied by the vendor a flaky workarounds at best, plus the vendor make use of the work you do by going in the back end and spying on what you’ve done, and reproducing your logic for sale to other businesses. Their usage reports they receive monthly from every customer, plus the logs they keep on their servers allow them to see everything a company does with the software. Not recommended for anyone sorry.

  5. Hi Erik,

    This website is helpful for Learning,
    Could you please share any Documents related to CQ Development of Extracts,reports,sending Mail functionality etc.

    Awaiting your positive feedback.

    Thanks in advance,
    Divyaa

  6. Does anyone know if there’s a way in CQ to list the line number in the report.

    example

    1. Customer A
    2. Customer B
    3. Customer C

    thanks…..

    • I haven’t used CQ in more than 3 years at this point but I can say that somewhere along the way I did see a way to add numbers to reports. I think it was from Cyberscience but I no longer have that documentation and when I saw it, it might have been more than 5 years ago.

    • Great to see this discussion. Here is one answer to the question posed, but I imagine you’ve worked it out by now. If you are using Cyberquery, you likely have access to its Support, they could answer it in a heartbeat. 800.851.2363 support@us.cyberscience.com

      list/domain=”Customer”
      str(itemnumber[customer],”zz9. “) + customer_no + ” “+name
      sorted by
      customer_no

      Cyberquery 7.7 is an amazing product. I’ve worked with 8 BI tools, as well as SQL over the past several years and found Enterprise Cyberquery to be hands down the best product you can use. It’s a full spectrum product that has something for every level of user.

      Like any software, you do need to learn how to use it. Cyberquery employs very different paradigms that are designed to make retrieval speed very, very fast (up to 2 million records per second). For this reason, people may think the software is hard to use. Training cures that problem. Once you understand the “why’s” behind the design, you’ll see the benefits I’m talking about.

      A couple of nuances most people aren’t aware of that make this tool truly elegant:
      1. The use of hold files, QDB’s, and data sets enable non-IT users to overcome constraints imposed by their database.
      2. The use of dynamic code generation enables you to overcome constraints imposed by the software itself. How cool is that?

      Training classes are available on-site or at the Denver office. If you go to the Denver office, classes are pretty inexpensive ($500 per day). The beginning and intermediate classes are 1 day each, encapsulated in “Report Writing for Enterprise Cyberquery”. There are 3 Advanced Report Writing modules as well that span 3 days.

      Not sure if you are an expert? Here’s a quick skills inventory: If you know what the following terms & tasks mean and, more importantly, how to utilize the functionality they represent, you are an expert:

      1. QDB’s (Query Databases)
      2. Data Sets (this is not a result set)
      3. Dynamic code generation
      4. Include files
      5. Compose
      6. Bullet Charts
      7. Sparklines
      8. One-to-One, Reverse, Approximate
      9. Box and End Box
      10. Todaysdate – Weekday(Todaysdate) + 2
      11. Using cURL to call a RESTful API from a Cyberquery command line
      12. Slider
      13. Deferred division
      14. Creating dashboard-style reporting solutions with unlimited drilldown capabilities
      15. Collapse switch

      Enjoy!

  7. I have been using Cyberquery for many years. Wonderful product.
    The interest rate field comes from the database as text. So the output to an excel spreadsheet is text. Is there a way to convert the text in Cyberquery to numeric so it displays as numeric in excel?
    thanks

    • there is a function call var that acts as a CAST (in SQL) to convert the field from a string to a number. NULL or blanks are set equal to zero.
      yes use the VAL function
      so…
      list/xls
      somestring
      val(somestring)
      val(somestring)/decimalplaces=2/commas –or /mask=”zz9.9″ , etc

  8. We’re very new to Cyberquery and have tens of questions. I’m wondering if anyone here is an expert in cyberquery who can assist us answering some of the questions.
    We need to know if there are any ways for automating the process of exporting a specific report and further integrating it into a CRM software, let’s say once a week.

    • You do this pretty much the same way you would do it anywhere else. You can set up CQ to run in a batch file and have it write the output to a delimited file. From there, you use whatever database tools you have to import it into the other application / database. We did it in UNIX and the data was imported back into MySQL.

      II don’t remember the specific commands, although I remember some conjuring to get it to a file, but I also don’t recollect it being terribly hard either. I could probably figure it out if I had to, although In my current world, the likelihood of touching CQ is somewhere less than zero I think.

Leave a Reply

Your email address will not be published. Required fields are marked *