Zen Reports and% XML.Writer to generate Excel reports in Caché

As it is known, MS Excel of the latest versions supports the description of the document structure in xml format. This circumstance allows you to create reports in Excel using the generation of xml-files. There are several ways to create xml in Caché DBMS. In this article, we will consider two, perhaps the most convenient, ways of efficient programmatic report generation in MS Excel: using Zen Reports and using the% XML.Writer class.

As an example of the MS Excel report, the printed form of the curriculum is taken from the curriculum management system, which has already been written about here , so we proceed directly to the formulation of the problem and how to solve it. It is required to receive the report of the curriculum in MS Excel format, which should consist of the schedule of the educational process (title page) and the content of the curriculum (a list of all disciplines, their characteristics and calculated parameters). A fragment of the curriculum report is presented in the figure, the final report can be viewed here .

The general scheme of the report

In Cache, there are several ways to manually produce reports (here the possibilities of semi-automatic assembly based on DeepSee will not be considered). The most convenient method is implemented in ZEN and includes a set of tools that provides a complete cycle of the process of generating reports in XHTML and PDF. A description of this process can be found in the documentation . Nevertheless, this method can be used only partially to solve our problem.
Consider a general reporting mechanism in MS Excel format using both ZEN technology and other Caché features (see figure below).

This scheme of forming an xls document includes three stages: 1) data from the database is converted using the Zen Reports technology or the standard Caché technology into an xml file (input xml); 2) using the XSL transformation mechanism (eXtensible Stylesheet Language), modifies a previously prepared report template in xml format; 3) an Excel document (xls) is generated by filling in the xml report template, extended with XSL inserts, with data from the input xml.

The structure of the input xml file

Since xml is used as a data source for our report, the xml-file structure should be as convenient as possible for passing the steps described above and, ultimately, for generating a report. There are no additional special restrictions on the structure of the xml file.
Based on the specifics of our task and the structure of the database, the root element of the input xml is “Curriculum”, which contains all the information about the curriculum and includes the following elements:

• Curriculum name
• The sum of the forms of control for the entire curriculum: examinations; credits; course projects; coursework
• The sum of hours in all disciplines of the curriculum: just with the exam; total for SES (state educational standard); classroom hours; CSR (independent work on a course project or work); hours of independent work
• The sum of hours for each discipline for each semester of the curriculum: hours at a lecture; laboratory hours; practical training hours; clock on the DAC
• Amount of credits () for the whole curriculum

Also in the "Curriculum" contains cycles "Cicl", each of which consists of its own elements. Similarly, the remaining branches of the xml-file are described down to the disciplines and their characteristics.

Formation of initial xml

Consider two ways to get the source xml file: using the% XML.Writer class and using the Zen Reports mechanism.
')

Generate source xml using% XML.Writer

The xml structure described above can be obtained through the XML.Writer class, which allows you to:

1. Create root element
   do fWriter . RootElement ( "root element name" )
   do fWriter . EndRootElement ()
2. Create item
   do fWriter . Element ( "element name" )
   do fWriter . Write ( element value )
   do fWriter . EndElement ()
   
3. Create attribute
   do fWriter . WriteAttribute ( "attribute name" , "attribute value" )

In addition, XML.Writer has a method that allows you to extract all the data from the object passed to it.
Writer . RootObject ( "object name" )

In the task of generating a curriculum report, the RootObject method did not fit, because class of discipline has a link to itself, and the work of this method was not correct. In this regard, all elements of the output xml file were created manually. For this purpose, the sp.Report.spExcelWriter class was created, including the genWriterData method (iDSelectCur As% Integer) to generate an xml file, to which the id of the selected curriculum is passed. Using this method, using SQL queries, data is retrieved from the database, and inserted at the right place. After that, the output xml file is generated using another method OutputToFile ("path \ filename.xml").

Generation of the initial xml using the Zen Reports mechanism

Zen Reports is a high-level mechanism for extracting data from the Caché database and converting it into xml, which imposes certain limitations, which will be discussed below. This method involves the creation of a class Zen-report through Caché-studio, inherited from% ZEN.Report.reportPage, in which you need to fill out the XData ReportDefinition block. For more information about the rules for forming the XData ReportDefinition block and data sampling using an SQL query for an XML representation, please read the documentation .

Zen Report proposes the use of its own syntax to describe the data structure for the generated xml - this imposes some restrictions on the output xml format. As a result, the structure of the resulting xml file is slightly different from that described above: the Cicls and Blocks nodes, which contain the Cicl and Block subnodes, are added to the generated xml file.
We show some features of the output related data.

Example 1. Passing the ID of the selected curriculum to sql query item.
< report sql = "SELECT * FROM sp.cCurriculum WHERE ID =?" >

Next, the parameter with the value of the variable ..idCurr
< parameter expression = '..idCurr' />

The variable is a property of the ZenReport class and when calling the report generation method, the idCurr value takes the value of the current id's parameter passed to the method.

Example 2. Passing a parameter dependent on the result of executing an SQL query, for example, loop-block relationship:
< group sql = "SELECT * FROM sp.cCicl WHERE Curriculum =?" >
< parameter expression = '..idCurr' />
< group sql = "SELECT * FROM sp.cBlock WHERE Cicl =?" breakOnField = "ID" >
< parameter field = "ID" />
</ group >
</ group >

Here the “ID” is transmitted using the attribute breakOnField = “ID”.
We will show the grouping for the "Cycles".
< group name = "Cicls" sql = "SELECT * FROM sp.cCicl WHERE Curriculum =?" >
< parameter expression = '..idCurr' />
< group name = "Cicl" >
< attribute name = "CiclName" field = "Name" />
< attribute name = "CodeOfCicl" field = "CodeOfCicl" />
...
</ group >
...
</ group >

Blocks are grouped similarly.
The modified format of the generated XML file now has the following form.

 <?xml version="1.0" encoding='utf-8'?> … <Cicls> <Cicl> <Blocks> <Block> <Disciplines>…</Disciplines> </ Block> </ Blocks> </Cicl> … <Cicl> <Blocks> <Block> <Disciplines>…</Disciplines> </ Block> </ Blocks> </Cicl> … </Cicls> 

The loop call will also change during XSL transformations (the general way of applying XSL transformations is described below):

  <xsl:for-each select = ”./Cicls/Cicl”><xsl:for-each> 

We formulate some rules that should be considered when designing the structure of the output.

1. The Name field will be taken from Table1:
   < report sql = "SELECT Name FROM Table1" >
   < element name = "A" field = "Name" />
2. The Name field will give an error:
   < report sql = "SELECT Name FROM Table1" >
   < attribute name = "A" field = "Name" />
3. The Name field is derived from Table2:
   < report sql = "SELECT Name FROM Table1" >
   < group name = "Name" sql = "SELECT Name FROM Table2 WHERE ..." >
   < element name = "A" field = "Name" />
4. The Name field is derived from Table1:
   < report sql = "SELECT Name FROM Table1" >
   < group name = "Name" sql = "SELECT Name FROM Table2 WHERE ..." >
   < attribute name = "A" field = "Name" />

Creating an Excel Template

Before performing the XSL transformation, you need to create an Excel document template into which the data from the xml will be inserted. The procedure for creating an Excel template consists of three steps.
Step number 1. Excel creates the appearance of the report.
Step number 2. The template is saved in the xml table format.

In the above fragment, you can see that the list of styles is first created, which is then used to format the cells. For example:

  <Style ss:ID="s374"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/> <Font ss:FontName="Arial Cyr" x:CharSet="204" x:Family="Swiss" ss:Bold="1" ss:Italic="1"/> <Protection/> </Style> 

The following cell references this style:

 <Cell ss:MergeAcross="66" ss:StyleID="s374"><Data ss:Type="String">     </Data></Cell> 

The “Worksheet” element creates sheets in an Excel workbook, for example:

 <Worksheet ss:Name=""> . . . </Worksheet> 

The “Table” element creates a table. The table consists of the rows “Row”, and the rows in turn from the cells “Cell”.
Step 3. Through any text editor, changes are made to the xml structure by removing unnecessary attributes. In our case, the attributes are deleted: ss: ExpandedColumnCount = "67"; ss: ExpandedRowCount = "45"; x: FullColumns = "1"; x: FullRows = “1”, since the curriculum has an arbitrary number of disciplines, and if the “Table” element retains these attributes, an error will occur when generating an Excel document due to a mismatch in the number of rows and columns. It is also advisable to remove the ss: Height attribute from <Rowss: AutoFitHeight = "0" ss: Height = "13.5">, since if the line is very long and the cell says "carry by words", then there will be no word wrapping in generated Excel document.

XSL Transformation

To use the standard transformation method (in the class% XML.XSLT.Transformer) xml data in the xls format, you need to prepare a special xml block with embedded XSL constructs. In our case, the Excel template taken in the previous paragraph was taken as the basis for XSL. This template needs to be improved using the following XSL constructs:

1.  <xsl:for-each select = ""> </xsl:for-each> 

2.  <xsl:value-of select = ""/> 

The <xsl: for-each select = ""> </ xsl: for-each> construct is used to select each xml element of a given set. The <xsl: value-of select = "" /> construct allows to display the values ​​of the selected node. Below is a simple example of inserting XSL into an Excel template:

 <Table> <xsl:for-each select="Curriculum"> <xsl:for-each select="./Cicl"> <Row> <Cell> <Data ss:Type="String"><xsl:value-of select="./CiclName"/></Data> </Cell> </Row> <xsl:for-each select="./Block"> <Row> <Cell> <Data ss:Type="String"><xsl:value-of select="./BlocName"/></Data> </Cell> </Row> <xsl:for-each select="./Disciplines/Discipline"> <Row> <Cell> <Data ss:Type="String"><xsl:value-of select="./DiscName"/></Data> </Cell> </Row> </xsl:for-each> </xsl:for-each> </xsl:for-each> </xsl:for-each> </Table> 

In the given example, it is shown that in the Excel table, in a nested cycle, all elements of “Cicl” are addressed, then in each cycle (an enlarged group of disciplines) to all elements of “Block”, then in each block to / Disciplines / Discipline elements, and after This displays the information corresponding to the specified field <xsl: value-ofselect = "./ DiscName" />, i.e. names of disciplines.
After the XSL elements have been inserted at the right places in the template, you can begin the report generation process. To do this, you can create a special method in a certain class that will transform data from the xml format to xls using the prepared Excel template that can be placed in the XData block of the same class (in the example below, the XData block is called "xsl"). An example of this method is shown below.

ClassMethod generateReportStadyPlan ( outFileName As% String ) As% Status
{
set xslStream = ## class ( % Dictionary.CompiledXData ). % OpenId (.. % ClassName (1) _ "|| xsl" ). Data
set xmlStream = ## class ( % FileBinaryStream ). % New ()
set xmlStream . Filename = "Path to the xml file"

set outStream = ## class ( % FileCharacterStream ). % New ()
set out stream . TranslateTable = "UTF8"
set out stream . Filename = outFileName

set sc = ## class ( % XML.XSLT.Transformer ). TransformStream ( xmlStream , xslStream ,. OutStream )

if $$$ ISERR ( sc ) quit sc

quit out stream . % Save ()
}

Xdata xsl
{
< xsl: stylesheet version = "1.0" xmlns: xsl = " www.w3.org/1999/XSL/Transform"
xmlns = "urn: schemas-microsoft-com: office: spreads"
xmlns: o = "urn: schemas-microsoft-com: office: office"
xmlns: x = "urn: schemas-microsoft-com: office: excel"
xmlns: ss = "urn: schemas-microsoft-com: office: spreads"
< xsl: template match = "/" >
< xsl: processing-instruction name = "mso-application" >
< xsl: text > progid = "Excel.Sheet" </ xsl: text >
</ xsl: processing-instruction >

<! -— Insert Excel Template ->
</ xsl: template >
</ xsl: stylesheet >
}

Comparing Zen Reports and% XML.Writer

Mechanism	Benefits	disadvantages
Zen Reports	1. Eliminates unnecessary routine work. 2. The description of the structure is more concise, there is no unnecessary clutter. 3. Ease of perception	The structure of the output xml is less controlled, you have to follow certain rules
% XML.Writer	You can create absolutely any xml structure.	Great complexity of the description of the structure

Based on the specifics of the MAC architecture of LAM , in which java-projections are created for Caché classes, you can add to the additional benefits of% XML.Writer the possibility of projecting the class sp.Report.spExcelWriter, which generates a report. On the contrary, in Zen Reports it is impossible to get a projection of a report class inherited from% ZEN.Report.reportPage due to the fact that its methods work with streams.
Thus, the use of XML.Writer is advisable in the case of stringent requirements for the structure of the output xml file, and the use of the Zen Reports mechanism is recommended when creating complex reports, which first require a clear description and a reduction in labor intensity.