Replies by Julie Bolinsky for InterSystems Developer Community

Julie Bolinsky · Aug 15, 2023

Not entirely sure what you are looking to do, but Zen reports does support the following

1. Use of the parameter DATASOURCE that can be used to provide the XML ReportDefinition component (file based) [ Parameter DATASOURCE = "vareport.xml"; ]

2. Use of the <CALL> tag to allow for direct insertion of XML into the ReportDefinition component (stream based) [ <call method="ProcessBreakdone"></call> ]

3. Use of the <INCLUDE> tag to allow for direct xdata insertion into the ReportDefinition component [ <include xdata="CustomRowElem" /> ]

Either of these can be used to create the XML of the ReportDefinition section, and then use those entries in your ReportDisplay section.

Thanks Julie

go to post

Julie Bolinsky · Oct 17, 2022

Hello,

Based on your code, it appears that you are using Zen Reports. In order for Zen Reports to address certain character displays, the appropriate font needs to be specified within the report as well as having the font be loaded into Adobe (e.g. Adobe Arabic).

thanks Julie

go to post

Julie Bolinsky · Oct 13, 2022

Hello,

Yes, excel does not look at your ReportDisplay xdata at all - it only looks at your ReportDefinition xdata. Any item defined in there is included within the excel output. The only way to control whether entries appear in your ReportDefinition based on mode would be to change to a call method so that you could have the flexibility to control what is in the stream.

Thanks Julie

go to post

Julie Bolinsky · Aug 16, 2022

Hello,

I'm not sure I understand exactly what you are looking for, but Zen Reports offers many ways to get the data into the XML format.

Within the Report Definition you can use sql (straight sql calls), queryclass (stored procedure call) or you can use call (to a method that returns the xml format that you want in stream).

sql="select ID,Title, Category->CategoryName as Category, Rating, Length From Cinema.Film"

queryClass='ZenRpt.StoredProc.TestXTab' queryName='GetRecords'

Where the method FindADMType is defined within the report class like this:

Method FindADMType(ByRef pParms) As %GlobalCharacterStream
{
set stream=##class(%GlobalCharacterStream).%New()
do stream.Write("<ADMType>")
set x=""
set x=$O(^||ztemp("ADMTYPE",x))
while (x'="") { set rec="<Type typename='"_x_"' />"
do stream.Write(rec)
set x=$O(^||ztemp("ADMTYPE",x))
}
do stream.Write("</ADMType>") K ^||ztemp("ADMTYPE")
quit stream
}

Thanks Julie

go to post

Julie Bolinsky · Apr 15, 2022

Hello,

The first thing I would do is remove excel from the picture (especially since comparing 2 clients). I would first change from excel to XML. That way you can confirm that 2nd client is actually returning data based off the parameters you are passing in. Once you've confirmed that the result set is actually returning data with the parameters you are passing - I would first remove your mixed attribute/element that you currently have in your report definition xdata block that could be causing some confusion. Next instead of going to mode=xlsx - I would initially try the earlier version of excel - mode=excel and see how that goes. I do know that some clients have received the corrupt message, but if you go ahead and ignore you'll see data but mostly that has been in Chrome and not IE.

go to post

Julie Bolinsky · Jan 19, 2022

Hello,

Displaying at the top of the spreadsheet gets a little tricky as the <include> or <call> entries would always be placed at the end of the report (it ignores the placement within the XDATA).

The only way that I have been able to "add" notes above the output (other than column headers) is to embed it in my returning data. So in my example I was doing a sql statement - I would need to modify it to be something like this:

  sql="select 'First Line' as ID, ' ' as Title, 'First Line Details' as Description
    union select ' ' as ID, ' ' as Title, ' ' as Description
    union select 'Col1' as ID, 'Col2' as Title, 'Col3' as Description
    union select ID,Title, Description from cinema.film"

Because I'm now passing text in fields that previously was declared a number (ID column) as well as passing the column headers, I need to also modify the definition of the FilmListing group like this

If you need to have things declared as excel numbers then you may want to think about having multi-sheets where the first one contains your additional text, and the second worksheet has your data.

Thanks Julie

go to post

Julie Bolinsky · Jan 19, 2022

Can you change the output mode to be XML and make sure that it makes well formed XML output? I ask this because I have experienced when a non-supportable XML character gets into my data set my output is garbage/messy.

I have included a very simple excel report below that should run in the SAMPLES database using the Cinema.Film table that is traditionally included in our products. Entries from the film table will be displayed when selecting xml, excel or xlsx whereas html or pdf will display a simple message saying excel output. This may be able to confirm that overall setup is correct, and the issue is with the data potentially. Please note that with recent browser security settings, you may find that the user is given a message that the file may be corrupt - if you ignore that message and produce onwards you will see the data (but again I've seen Chrome, Firefox, IE and Edge all function differently depending on my version/browser settings). Also in order to view excel successfully in the browser, you'd need to make sure that appropriate support is there as well (e.g. plugin, etc.)

/// Film report.
Class ZENReports.JulieTestExcel Extends %ZEN.Report.reportPage
{

/// Class name of application this report belongs to.
Parameter APPLICATION;

/// This is the optional XML namespace used for the report.
Parameter REPORTXMLNAMESPACE;

/// This is the default display mode for this report.
Parameter DEFAULTMODE = "xlsx";

/// Parameter DEFAULTMODE = "xml";
/// Parameter DEFAULTMODE = "pdf";
/// Parameter DEFAULTMODE = "html";
/// Parameter DEFAULTMODE = "excel";

/// Switches between element-oriented and attribute-oriented parsing of structure for Excel output.
Parameter EXCELMODE As STRING [ Constraint = ",element,attribute", Flags = ENUM ] = "element";

/// Used to tag aggregates in EXCEL mode
Parameter AGGREGATETAG As String;

/// This XML defines the logical contents of this report.
XData ReportDefinition [ XMLNamespace = "http://www.intersystems.com/zen/report/definition" ]
{
<report xmlns="http://www.intersystems.com/zen/report/definition"
name="report"
sql="select ID,Title, Description from cinema.film"> <group name="FilmListing" >
<element name="ExcelDisplayColumnName" field="ID" isExcelNumber="true" excelNumberFormat='#%' />
<element field="Title" />
<element field="Description" />
</group>

</report>
}

/// This XML defines the display for this report.
/// This is used to generate the XSLT stylesheets for both HTML and XSL-FO.
XData ReportDisplay [ XMLNamespace = "http://www.intersystems.com/zen/report/display" ]
{
<report xmlns="http://www.intersystems.com/zen/report/display"
name="report">
<body>
<p>This report should be used for EXCEL output.</p>
</body>
</report>
}

}

go to post

Julie Bolinsky · Jan 18, 2022

Hello,

Did you save it as an .xls extension or a .xlsx extension? It needs to be the .xlsx extension.

If this isn't a multi-sheet excel report, and the report output wouldn't exceed xls file size (~65,000 rows) - you may want to change your mode to excel - this will instead produce an xml file which should open without issues if given a .xls extension.

Any way you could show the error message that you are receiving?

Thanks Julie

go to post

Julie Bolinsky · Jan 18, 2022

Hello,

There is a difference in the handling of PDF versus excel reports - that specific report PageLayouts does a combination of attributes & elements within its various groups which excel doesn't handle very well. You would need to define your EXCELMODE (please read up on this as ELEMENT is the preferred option). Also because there are multiple groups you would need to make sure that EXCELMULTISHEET set as well. Technically you can change any report from one MODE to another but that doesn't mean that they will function perfectly -- traditionally reports will function best in the mode in which they were specifically designed for.

thanks Julie

go to post

Julie Bolinsky · Jan 18, 2022

Hello,

I see you selected a product version of Ensemble 2014.1 - which means your Zen reports supports a DEFAULTMODE= excel or xlsx --- where excel mode generates an xml file that can be viewed within excel, and xlsx mode generates excel worksheets (not supported on VMS).

If you are running this from the browser, then depending on the browser it may open directly within Excel or give a default file name to Save As. This allows you to name the file anything you'd like.

If you are using a Generate method then you can control the outputted file name assigned when you call the Generate method.

Thanks Julie

go to post

Julie Bolinsky · Oct 6, 2021

So there are a couple of ways to accomplish what you would like

(1) option is to use the runonce='true' property at the report level and then define each group underneath it individually with its own query option (stored procedure, sql or result set) BUT in order for each individual value to be returned, you would need another group level defined.

So it would look like this:

</group>
</group>

This produces XML output like this

<Sales>

</Sales>

</myReport>

BUT you have another option

(2) You could use the CALL method within the report to come up with your own XML structure

And then have the method defined in the report class

Method FindADMType(ByRef pParms) As %GlobalCharacterStream
{
set stream=##class(%GlobalCharacterStream).%New()
set x=""
set x=$O(^||ztemp("ADMTYPE",x))
while (x'="") { set rec="<Type typename='"_x_"' />"
do stream.Write(rec)
set x=$O(^||ztemp("ADMTYPE",x))
}
K ^||ztemp("ADMTYPE")
quit stream
}

So this would produce the following XML output

</myReport>

I hope this helps

Thanks Julie

go to post

Julie Bolinsky · Sep 15, 2021

Hello,

Since you are talking about the same structure but coming from different elements - you could create a composite that you can then pass different to.

So for example I have a specific layout for address in a report - I create a composite for it

Class Julie.Reports.Composite.AddressBlock Extends %ZEN.Report.Display.composite
{
Property AddressLine1 As %ZEN.Datatype.string;

Property AddressLine2 As %ZEN.Datatype.string;

Property Town As %ZEN.Datatype.string;

XData Display [ XMLNamespace = "http://www.intersystems.com/zen/report/display" ]
{
<composite xmlns="http://www.intersystems.com/zen/report/display"
xmlns:julie=http://www.intersystems.com/zen/report/display/JULIE>

<block ifxexpression="#(..AddressLine1)#">
<item field="#(..AddressLine1)#" too-long-text="wrap"/>
<br/>
</block>
<block ifxexpression="#(..AddressLine2)#">
<item field="#(..AddressLine2)#" too-long-text="wrap"/>
<br/>
</block>
<block ifxexpression="#(..Town)#">
<item field="#(..Town)#" too-long-text="wrap"/>
<br/>
</block>
</composite>
} }

Then in the actual report I would call the composite passing the report elements to the appropriate composite property:

<julie:AddressBlock width="12%" AddressLine1="AddressLine1" AddressLine2="AddressLine2" Town="AddressCity"/>

<julie:AddressBlock width="12%" AddressLine1="CustomerLine1" AddressLine2="CustomerLine2" Town="CustomerCity"/>

Thanks Julie

go to post

Julie Bolinsky · Sep 7, 2021

What fonts files do you have in your font directory? Is it recognizing any of those fonts? Have you tried to define individual one of those individual font and see if those get recognized?

go to post

Julie Bolinsky · Sep 3, 2021

The structure in the fop.xconf is defined as shown below - I'm not sure of the error you are getting, but you may need to add in the recursive=true to allow for any sub-folders to be viewed.

     <fonts>
        <!-- register all the fonts found in a directory -->
        <directory>C:\MyFonts1</directory>
        <!-- register all the fonts found in a directory and all of its sub directories (use with care) -->
        <directory recursive="true">C:\MyFonts2</directory>
        <!-- automatically detect operating system installed fonts -->
        <auto-detect/>
     </fonts>

go to post

Julie Bolinsky · Jul 22, 2021

Attribute/Element references can be used within the XData, and thereby be controlled by whatever querying you are doing or via a CALL method

XData XslFoXslt
{
<xsl:template name="RatingR" >
<xsl:param name="value"/>
<xsl:if test='$value="R"'>
<xsl:attribute name='color'><xsl:value-of select='/MyReport/colorvalue1'/></xsl:attribute>
<xsl:attribute name='font-weight'>bold</xsl:attribute>
</xsl:if>
</xsl:template>
}

Where my data looks like this

<report xmlns="http://www.intersystems.com/zen/report/definition"
name="MyReport" sql="select ID,Title, Category->CategoryName as Category, Rating, Length From Cinema.Film">
<element name="colorvalue1" expression='"red"' />
<group name="Film">
<attribute name="Title" field="Title"/>
<attribute name="Category" field="Category"/>
<attribute name="Rating" field="Rating"/>
<attribute name="Length" field="Length"/>
<attribute name='User' expression='..PrintedBy'/> </group> </report>

I could have a call method used

<report xmlns="http://www.intersystems.com/zen/report/definition"
name="MyReport" sql="select ID,Title, Category->CategoryName as Category, Rating, Length From Cinema.Film"> <call method="GetColorDets"/>
<group name="Film">
<attribute name="Title" field="Title"/>
<attribute name="Category" field="Category"/>
<attribute name="Rating" field="Rating"/>
<attribute name="Length" field="Length"/>
<attribute name='User' expression='..PrintedBy'/> </group> </report>

Method GetColorDets(ByRef pParms) As %GlobalCharacterStream
{
set stream=##class(%GlobalCharacterStream).%New()
do stream.Write("<colorvalue1>")
set rec="red"
do stream.Write(rec)
do stream.Write("</colorvalue1>") quit stream
}

go to post

Julie Bolinsky · Jul 22, 2021

You would need to do a Show Plan (button within Management Portal - SQL). This will show you the path that each query is taking, and allow you to troubleshoot what may be the issue.

go to post

Julie Bolinsky · Jul 21, 2021

The page number used is <fo:page-number/> which wouldn't be available in xslt so you wouldn't be able to do any xsl:when or xsl:if statements related to it. You could define multiple <section> and give each section its own header

go to post

Julie Bolinsky · Jul 21, 2021

There are two ways to use <pagemaster> and <masterreference> - (1) such that it encompasses your entire report or (2) the page details get reset for each entry in a group.

1. Have page 1 have one header/footer, and the rest of the report a different one

<document define document details> add styles</document>

<pageheader> define pageheader </pageheader>

<pagefooter> define pagefooter</pagefooter>
</masterreference>

<document define document details> add styles</document>

<pageheader> define pageheader </pageheader>

<pagefooter> define pagefooter</pagefooter>
</masterreference></pagemaster><body report body here>

2. For each group in my xml have a specific first page, and then the rest all be something else

In my XML definition I have a group Sales and inside that group I have another group called SalesRep - the SalesReps are uniquely identified via an attribute I have defined as name

<document define document details> add styles</document>

<pageheader> define pageheader </pageheader>

<pagefooter> define pagefooter</pagefooter>
</masterreference>

<document define document details> add styles</document>

<pageheader> define pageheader </pageheader>

<pagefooter> define pagefooter</pagefooter>
</masterreference>
</pagemaster>
<body genLastPageIdOn="@name">

Report details here

</group>
</body>
</section>

Hope this helps.

go to post

Julie Bolinsky · Jul 12, 2021

<img> can be used directly and does not need to be embedded in an <item>. So for example

I've used the direct reference to lotterylogo.jpg because I was running my report from within the /csp/samples but I could have done the full reference URL as shown below
<img contentHeight="120mm" contentWidth="140mm" src='http://localhost:57775/csp/samples/lotterylogo.jpg' />

go to post

Julie Bolinsky · Oct 15, 2020

Did you ever run the $LOG=1 option to see what additional error details there are? This would be the first step because it might not be a timeout issue but maybe an issue with the actual data itself (invalid xml characters for example). When running a zen report directly, the data retrieval and data display processes need to complete within the CSP gateway timeout setting. Sometimes this isn't possible, that's why Zen reports supports the split/merge option as well as having a number of Generate to file methods (because they are being written to a file, these methods are not impacted by the timeout).