A Caché of Tips : Zen Reports Output and Data Options

[APZ]

This is the 3rd of the Zen Reports-related posts in the Caché of Tips
series. Zen Reports provides the ability to define the data and layout
of a report. Using a Zen Report, you can create a PDF file, a web
page, or even an Excel spreadsheet. The Zen Report tool is included in
Caché and can be used as replacement to other report generators such
as Crystal Reports.

In the first Zen Reports Tip, we created the "ReportDefinition" block
of a Zen Report class. This is the part which collects (and
aggregates) your data from the database and structures it as XML.

In the second Zen Reports Tip, we created the "ReportDisplay" block of
a Zen Report class. This is the part lays out your content on the
page. We set up for PDF output of the report.

In this final Tip, we'll go over some odds and ends -- other things
you can do with a Zen Report class.


HTML OUTPUT

The report that we set up in last weeks' Tip can be used to generate
HTML output as well as the PDF. (See
https://sites.google.com/site/intersystemsmv/home/a-cache-of-tips/pdffromazenreport
for the file.)

There are two ways to do this.

1. Override class parameter DEFAULTMODE, which was "pdf", to be
"html". Save & re-compile the report. The next time that you run the
report (such as by clicking View > Web Page from Studio), the output
will be HTML.

2. Specify a different output mode at run-time.

If you're looking at the report in a web browser, take a note of the
URL. It will be in the form:

http://server:port/csp/namespace/package.class.cls

To change the output mode, add the URI parameter "$MODE" and set it
equal to "html"

http://localhost:57772/csp/mvfile/MyApp.ZenRpt.Attorney2PDF.cls?$MODE=html

Now reload the page to re-run the report.



RUN A REPORT PROGRAMMATICALLY

There is another way to run a report, and that is by using a method of
the report class. This technique takes the browser out of the picture
--- it's just you, the report class, and the command line.

; "MyApp.ZenRpt.Attorney2PDF"->GenerateToFile("c:\temp\mvfile.pdf",
2)

The second argument to GenerateToFile is the output mode, as a numeric
code. The most useful mode-codes to know are: 0 for "xml", 1 for
"html", and 2 for "pdf".

Unlike the browser-based technique which falls back on the DEFAULTMODE
is $MODE is not specified, you always need to specify the mode-code
when running a Zen Report programmatically.



EXCEL OUTPUT

To configure the report for Excel output, we use the same techniques
as we did to get HTML output : overriding the DEFAULTMODE or
specifying $MODE in the URI. The value to use is “xlsx” (Excel’s
native file-format) or “excel” (an older format that creates larger
files). If running the report programmatically, the mode-code to use
is 10 (or 7).

The ReportDisplay XData block controls the layout for HTML and PDF
output. The Excel output of a Zen Report does not have any fancy
layout requirements, so it is driven directly by the ReportDefinition.
That being the case, there are a few additional constraints on the
structure of the ReportDefinition (and thus the resulting intermediate
XML).

I find that it is easier to understand these constraints by
considering the XML structure, and working backwards to the acceptable
ReportDefinition.


The structure of the intermediate XML, generically speaking, is as
follows:
<root>
<row>
<column>value</column>
etc
</row>
</root>

There can optionally be some summary cells after the last row of data.
The cells can either contain a literal values, or can apply an
aggregate function to the column of data above.

In order to build this structure, the ReportDefinition will look like
this:
<report …>
<group …>
<element … />
etc
</group>
</report>

(There can be <attribute> building blocks, too, but the output from
them is ignored.)

What’s important to notice here is that we can’t nest the <group>
building-blocks. We could use more than one, if they are one-after-the-
other, but we can’t nest them.


Where things get interesting with Excel output, is the use of
<aggregate>s. As I mentioned in the first Tip about Zen Reports, the
<aggregate> building-block performs aggregation over grouped data.

When we generate Excel output, the <aggregate> building-blocks create
summary cells at the bottom of the columns of the spreadsheet. Each
<aggregate> in the ReportDefinition creates a summary cell in the
spreadsheet, and these are allocated sequentially, starting with the
first column. In order to make this happen, though, we need to set the
class parameter AGGREGATETAG to some non-blank value (eg. "agg").

If we want the summary cell to apply a function, set the <aggregate>
attribute “excelFormula” to the name of that Excel function. For
example:

<aggregate name="Attorney_AvgPctVarExcel" type="AVG"
field="PercentVarience"
excelFormula="AVERAGE"/>

For summary-cells that apply a function, it is important to have them
line up under the correct column. To line them up, we can use blank
“placeholder” aggregates to fill up the columns that don’t otherwise
need summary cells

<aggregate name="column3" type="PLACEHOLDER" />

The exact order of the <aggregate> building blocks isn't relevant when
using ReportDefinition for PDF or HTML output, since they pick out
values by name, not relative position. Having placeholder aggregates
in the mix won't interfere with those other types of output.


The class “Attorney2ExcelSingleSheet” has a full example of a Zen
Report that will make good Excel output. The structure of the
ReportDefinition is somewhat similar to Attorney1, but with only one
nested <group>. Because we had to eliminate the <group> that breaks
on Attorney, we cannot do any per-attorney aggregate calculations. The
aggregate that averages the Percent Varience value is entered twice:
once as Excel formula, and a second time as a value calculated by the
Zen Report.

The class is available for download on the Google site page :
https://sites.google.com/site/intersystemsmv/home/a-cache-of-tips/zenreportsoutputanddataoptions


Starting in 2012.1, we have the option of having multiple sheets in
the Excel spreadsheet. To do that, the ReportDefinition will look like
this:
<report …>
<group …>
<group …>
<element … />
etc
</group>
</group>
</report>

Again, the nesting of the <group> building blocks is crucial. We must
have exactly two <group>s, one nested within the other. (We could have
some additional <group> building blocks as siblings, but we can’t nest
deeper – nor can we forego a level of depth.)

If we’re using <aggregate> building blocks, they go outside the inner
<group> – but inside the outer <group>. Output from any <attribute>
building blocks is still ignored.


The class parameter EXCELMULTISHEET must be set to 1, in order to make
multi-sheet Excel output. When making multi-sheet output, the outer
<group> needs to define a unique name for each sheet. The simplest way
to do this is to add two attributes on the building-block ---
excelSheetName and breakOnExpression :

<group name="Attorney" breakOnField="AttyName"
breakOnExpression="%val"
excelSheetName="!%val">


Conveniently, the ReportDefinition from our example-class Attorney1 is
already very close to this multi-sheet structure. The only changes we
need to make are:

1) Set class parameters EXCELMULTISHEET and AGGREGATETAG (required)
2) Add excelSheetName on the outer group (required)
3) Mark the numeric <element>s with isExcelNumber (optional)
4) Add placeholder aggregates, to make the aggregates line up with the
right columns, and specify excelFormula if we wish to use functions
5) Eliminate the one aggregate which was outside of the outer <group>
-- it would appear as an empty sheet if we kept it.

The class Attorney3ExcelMultiSheet has these changes already made. It
generates an Excel spreadsheet when run in Caché 2012.1 or higher. The
class is available on the Google site page :
https://sites.google.com/site/intersystemsmv/home/a-cache-of-tips/zenreportsoutputanddataoptions



SQL DATA

In our previous examples, the ReportDefinition XData block used a
custom Class Query as the source of its result set.

<report xmlns="http://www.intersystems.com/zen/report/definition"
name="myReport" queryClass="MVFILE.AttyMonthly"
queryName="MonthlyProductivity" >

You could replace that class-query with an SQL statement :

<report xmlns="http://www.intersystems.com/zen/report/definition"
name="myReport"
sql="SELECT * FROM MVFILE.ATTORNEY" >

(For the previous Tip on the topic of SQL, see
https://sites.google.com/site/intersystemsmv/home/a-cache-of-tips/sql
)



A powerful aspect of using SQL in a Zen Report is fact that you can
combine multiple SQL statements in one report. Each <group> building-
block can define a new SQL statement. By passing in values from the
outer result-set, you can drill down with multiple subqueries.

To pass in a value, use the <parameter> building block.

The <parameter> tag goes inside the dependent query. Use the "field"
attribute of the <parameter> to select values from the outer query.
These values from the <parameter> get fed into any "?" placeholders
which you put in the dependent SQL query. For example:

<report xmlns="http://www.intersystems.com/zen/report/definition"
name="myReport"
sql="SELECT ItemId FROM MVFILE.ATTORNEY" >

<group name="Attorney"
sql="select * from MVFILE.ATTORNEY_MONTHLYACTIVITY where ATTORNEY
= ?" >
<parameter field="ItemId"/>

You could also chain together data from Class Queries in the same
fashion, but in practice it's common to combine the sub-query logic
inside the custom class query itself.

A complete example of a Zen Report that uses SQL queries and
subqueries is available on the Google site page :
https://sites.google.com/site/intersystemsmv/home/a-cache-of-tips/zenreportsoutputanddataoptions

The class is called "MyApp.ZenRpt.Attorney3SQL"



OTHER SOURCES OF DATA

The ReportDefintition block of a Zen Report creates the intermediate
XML, which the ReportDisplay relies on. If you already have a source
of XML, you can use that directly and skip the ReportDefinition. To do
this, use the class parameter DATASOURCE.

For instance, if you'd like to set up a new Zen Report that uses the
exact same data as the MyApp.ZenRpt.Attorney1 class, set the value
like so :

Parameter DATASOURCE="MyApp.ZenRpt.Attorney1.cls";

This works because the DEFAULTMODE for that Attorney1 report is
already XML. To use a report which has a DEFAULTMODE other than XML,
we apply the $MODE URI parameter to change the output to XML:

Parameter DATASOURCE="MyApp.ZenRpt.Attorney2PDF.cls?$MODE=xml";



There are even a few more techniques for using existing intermediate
XML. The documentation covers the full variety of techniques which can
be used. For an overview, see :

http://turbo/csp/docbook/DocBook.UI.Page.cls?KEY=GRPT_report_definition



Ariela Zonderman
SQA Engineer, InterSystems

[APZ]

A correction to that last URL for the documentation :
http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=GRPT_report_definition

Ariela

[matt...@gmail.com]

Hi Ariela,

Even if this post is several years old, I wanted to thank you. Zen Reports is not well documented (given its complexity).

Intersystems’ only examples of excelSheetName use it as follows:

<group name='SalesRep' breakOnField='SalesRep' excelSheetName='!..GetName()'>

and then including a method:

Method GetName() { quit %val("SalesRep") }

However, I could not get the scoping correct to get SalesRep.

 

As you indicate, manually creating the attribute is a far easier method:

<group name='SalesRep' breakOnField='SalesRep' excelSheetName='!..GetName()'>

<attribute name="excelSheetName" field=" SalesRep" />

</group>

Thanks,

Matt