Zen - Manipulate a result set for Zen report?

Question

Question

Michael Davidovich · Aug 16, 2022

Have a Zen report with a huge query that we are trying to break down and be more efficient.

Looks like Zen only wants to take a result set class as the data (or XML, but writing to a file to generate these reports is probably even more overhead).

Is there a way I can move through a result set object, delete rows I don't want based on some objectscript commands and the pass that to the report?

Or can I pass a dynamic object?

Thanks.

Product version: IRIS 2021.1

Discussion (3)2

Log in or sign up to continue

Michael Davidovich · Aug 17, 2022

@Julie Bolinsky and @Stephen Canzano

Thanks for your replies!

To back up and offer more clarity: the issue was that the WHERE clause of our query used a stored procedure to calculate a data element. This was causing the query to churn and for the Zen reports to timeout. We wanted to break the query apart so that it could select a subset of the data by date range first and then loop through that data and run the store proc only that data to filter it further. A subquery didn't end up being any more efficient here. Views and CREATE TABLE ... AS wouldn't work at scale. Since Zen wants a %Library.ResultSet object, I researched fetching each record and deleting data as needed OR creating a new %Library.ResultSet object and adding data to that, but there are no methods to support that.

The question was: can you give a Zen report a dynamic object as a data source?

Sounds like the answer is only %Library.ResultSet or XML file (or stream). The stream idea is ok, but it's a big report and it would have been a big lift to transform the %Library.ResultSet into XML.

The solution was 1) move the stored proc out of the WHERE clause and into the SELECT and 2) use the filter='%val(""DataElement"")=VALUE' in the report tag to evaluate the table alias created by the stored proc in the select to skip the rows we didn't want to generate PDFs for.

Happy to talk more about this as I'm sure I'll be touching some Zen stuff again.

0 0

score 0 · Answer 1 · 2022-08-16T17:31:07-04:00

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

score 0 · Answer 2 · 2022-08-17T08:12:21-04:00

I too am not entirely sure what your issue is but when I have particularly complex reports I leverage the ability to define in the ReportDefinition multiple <group> entities. Consider for example you have a data model that has

Batch
Claim
ClaimLine
ClaimLineAdjudication

while you could define a single entity <group> which calls a single query, in my case I prefer to use Class Queries and then leverage breakOnField and create multiple <groups>, I find it makes more sense to have something like this, albeit this is psuedo code and not 100% what is entered in a Report Definition.

<group name="Claim"

</group>

In this manner each level/group is responsible for doing one thing. If needed I've also added to the <group> tag a filter element. An actual example I have is this

fields="ClassName,LocalTimeChanged,RemoteTimeChanged,Description,LocalFkeys,RemoteFkeys,LocalIndices,RemoteIndices,LocalMethods,RemoteMethods,LocalProperties,RemoteProperties,LocalQueries,RemoteQueries,LocalParameters,RemoteParameters,LocalTriggers,RemoteTrigger" filter="..Filter(..FilterSpec)">

whereby I send to my method Filter the field values specified in the attribute fields as well as a FilterSpecification to test the filter. You don't have to do it this way, its just an example of using the fields attribute and the filter attribute which is any valid objectscript expression.