Why Use SQR?

For those of you new to developing with SQR, you may have heard it described in many ways. Some people say it’s just a report authoring tool. Others claim it’s a great ETL tool. Some people liken it to COBOL, while others say it has shades of RPG. Whatever you’ve heard chances are there is some truth to the statement. SQR truly is an anomaly within programming languages—a mosaic of various language constructs and concepts uniquely blended to form SQR (Structured Query Reportwriter), now re-branded as Brio.Report by Brio Software.

The reasons to use SQR fall into three areas:

  1. Power & Performance
  2. Flexibility
  3. Robust Navigation to Desired Data

Power & Performance

A One to Many Relationship Beyond Compare

With SQR the ability to make one pass of the RDBMS data and produce multiple homogeneous or heterogeneous output files is easy. This reduces the load on your RDBMS and network.  

Homogeneous – Picture the typical requirement to execute a report for all divisions within your company and create a break for each division with sub-totals. Pretty standard fare. Now picture the desire to access the same data only once and create a separate output file for each division. This desire readily becomes a reality via substituting SQR’s NEW-REPORT command for the existing break logic.

Heterogeneous – Want to provide your users with HTML and PDF output of the same report without having to execute the report twice? No problem. With SQR, the initial output file created is called SPF. This is shorthand for "SQR Portable Format". Via a simple SQR command line option of –PRINTER:XX or a quick check of a check box coupled with a program called SQRP, the SPF file is easily converted to any of the output file types supported by SQR (HTML, PDF, CSV, BQD, Postscript, HP Laserjet, ascii text).

Reporting with Ginkgo Biloba

It’s always a good idea to leverage the power of your memory. This holds true with reporting tools as well. Since SQR is designed to execute on a server, it can take advantage of the power and memory offered by said device. For example, within SQR the ability to create arrays and load-lookup data structures is possible. These objects allow you to query data and store said data in memory for subsequent access and manipulation. A typical example would be querying data from the RDBMS without using an order by clause and reading the data queried into an array. This approach reduces the query time against the RDBMS because the sorting will be performed later on the array. Since all IO performed on this data is in memory, the processing efficiency is dramatic.

Efficiency, Efficiency, Efficiency

Ever try and sell a house? Any realtor will tell you, location, location, location is the key to getting interested buyers. Similarly, in reporting, efficiency, efficiency, efficiency is the key to fulfilling user’s needs. Sure, the fact that the report looks good and contains the data the user desires is important; but if that report takes several minutes to execute the user is sure to lose interest and become frustrated. Therefore, it is always important to use a reporting tool that offers several methods of designing efficient reports. A few of these methods will be addressed now.

Dynamic SQL – Within SQR the ability to programmatically create and issue any part of the query (column name, from clause, where clause, order by clause) at run time is possible. This facilitates issuing an efficient select statement. For example, based upon requirements it may be possible to create where clauses free from "OR" conditions that would normally contain them. "Or" conditions as we know suppress the indices that exist on the column in question and hence slow down queries. 

Ability to Force Hints – Within SQR the ability to force the RDBMS to use an index that is normally not used (based upon the rules applied by the RDBMS engine) is possible. This feature combined with implementation of this index may enhance query performance. 

Buffering Fetched Rows – For SQR programs accessing Oracle or Sybase RDBMSs, the ability to request the database to fetch a specific number of rows at one time is possible. This decreases the number of buffer gets against the RDBMS engine and increases query performance. This feature is easily implemented via issuing the –Bnn command on the SQR command line. 

Bursting – Within SQR the ability to break down a given HTML file into several smaller files thus decreasing browser retrieval time is possible. By simply issuing a –BURST:Pnn on the SQR command line, the original single HTML that would have been created is separated into X number of physical files each containing nn pages. 

DDL & DML – Within SQR the user executing the report not only has the ability to perform DML; but can perform any DDL that he has been given the privilege to perform. What this provides is the potential for temporary database objects owned by the user executing the report to be created for the duration of the report. These objects could store a smaller set of data or perhaps aggregated data and be subsequently repetitively accessed and manipulated. These "temporary database objects" off load taxing queries from being performed against actual RDBMS production objects.

Flexibility

Pick A Layout, Any Layout

From simple tabular reports to cross-tabular reports to master-detail reports, SQR provides the features and constructs required to furnish your customers with the data they need. Furthermore, SQR’s ability to easily alter font styles and sizes allows it to be used to print data on preformatted government forms. In addition to report layout, the ability to customize your reports and make them aesthetically pleasing via embedding graphs, charts, bitmaps, logos, bar codes, and other images is very easy.

Would a Rose by any other Name Smell as Sweet?

The argument could be made that all RDBMS’s are essentially the same and offer virtually the same functionality. However, talk to any DBA and bias for a particular vendor’s database engine becomes apparent. Hence, most organizations will standardize on a given vendor’s RDBMS. However, in today’s economy characterized by mergers, acquisitions, and consolidations, the need to retrieve data from disparate data sources built with different RDBMS vendors is becoming more and more frequent. So, what’s an IT shop to do if it needs to gather data from many different data sources simultaneously? Answer – standardize on SQR. With SQR the ability to simultaneously access and process data from multiple homogeneous (two Informix instances, for example) or heterogeneous (Oracle and DB2, for example) RDBMS’s on the same or different devices is possible.

Robust Navigation to Desired Data

What page is that on?

Ever have a user ask you this question? It stands to reason that voluminous reports consisting of hundreds or thousands of pages lead users to get lost in the data. With the ever increasing demand on people to quickly retrieve data and make split second decisions on that data comes the need to allow user’s the ability to create voluminous reports with easy and quick navigation to specific areas of interest. Within SQR exist a variety of features that provide for the aforementioned functionality.

Table of Contents – Within SQR a table of contents, just like the ones found in any book, can be created for a report. When rendered as HTML, this table of contents allows the user to quickly advance to a given page within the report via clicking on a corresponding entry in the table of contents. This feature allows a multitude of users to access a single output file and quickly navigate to areas of specific interest. 

Expand & Collapse – Within SQR design approaches can be implemented that allow HTML output to possess "hidden" sections of data that can be brought to the forefront or returned to the background via a mouse click. This expand and collapse feature is particularly useful when wanting to break down aggregate data into its corresponding components. 

Master-Detail/Drill Down Reporting – Within SQR the ability to have the HTML output of one SQR program link to and execute another SQR program can be easily achieved. This design and approach is usually implemented to allow users who desire to view the data at a more granular level to do so. For example, a given SQR report may report on the total sales volume for a given region of the country (i.e. North $300.00, South $650.00, etc.). This is the master report. Perhaps some users may want to view what cities comprised a given region’s sale volume. In comes the detail report. Via placing a link on the region or dollar amount and performing a mouse click, another SQR report could execute that would show the cities that comprised the given region’s sales volume (i.e. Minneapolis $150.00 and Detroit $150.00).