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:
- Power & Performance
- 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
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
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
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.
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).