New post
AllAccepted answers
Alexander Koblov · Sep 9, 2019

I'm not sure for how much this is efficient, but you can use XSLT to do the transformation.

Class CMT.XmlToCsv [ Abstract ]
{

ClassMethod transform(
    infile As %String,
    outfile As %String) As %Status
{
    Set tXSL=##class(%Dictionary.CompiledXData).%OpenId(..%ClassName(1)_"||XmlToCsv").Data
    Set tSC=##class(%XML.XSLT.CompiledStyleSheet).CreateFromStream(tXSL,.tCompiledStyleSheet)
    If $$$ISERR(tSC) Quit tSC
    quit ##class(%XML.XSLT.Transformer).TransformFileWithCompiledXSL(infile,tCompiledStyleSheet,outfile)
}

XData XmlToCsv
{
<?xml version="1.0"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" />
    <xsl:variable name="separator" select="','" />
    <xsl:variable name="newline" select="'&#10;'" />

    <xsl:template match="/">
        <xsl:text>Col1,Col2,Col3</xsl:text>
        <xsl:value-of select="$newline" />
        <xsl:for-each select="//Details">
            <xsl:value-of select="Col1" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="Col2" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="Col3" />
            <xsl:value-of select="$newline" />
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>
}

}

And then call it from terminal:

set p=##class(CMT.XmlToCsv).transform("c:\temp\input.xml","c:\temp\output.txt") 
zw p

I took XSLT from https://stackoverflow.com/a/46176699/82675

Alexander Koblov · Sep 6, 2019

Return statement is

do %sqlcontext.AddResultSet(rs)

When you call this stored procedure it returns resultset

For example:

call CMT.ExternalUsersSearch('%in%',,,'Title')

If you call this stored procedure via ODBC / JDBC you need to add ReturnResultsets to the method definition:
https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?K...

Alexander Koblov · Sep 6, 2019

CASE statement expects expression after THEN. DESC or ASC are not expressions. That's why you are getting syntax error.

So you need to supply some expression, ordering by which would mean reverse ordering by FirstName.

I don't know how to do this.

I would do sorting on the client or use dynamic SQL to create the query. As below for example.

Important! Nowhere I concatenate parameters of the stored procedure with the query to avoid SQL injections. Only SortingField is concatenated after checking that it has approved value.

ClassMethod Search(
    Name As %String = "",
    SSN As %String = "",
    Title As %String = "",
    SortingField As %String = "",
    StartIndex As %String = "") As %Integer [ SqlName = ExternalUsersSearch, SqlProc ]
{
 set query = "select Name, Title, SSN from Sample.Employee WHERE 1=1 "
 kill args

 if Name'="" {
     set args($I(args)) = Name
     set query = query _ "AND Name like ? "
 }

 if SSN'="" {
     set args($I(args)) = SSN
     set query = query _ "AND SSN like ? "
 }

 if Title'="" {
     set args($I(args)) = Title
     set query = query _ "AND Title like ? "
 }

 set AllowedFieldsToOrderBy = $LB("Name", "SSN", "Title")
 if $ListFind(AllowedFieldsToOrderBy, SortingField) {
    set query = query _ " ORDER BY " _ SortingField

    if StartIndex = 1 {
        set query = query _ " DESC"
    } else {
        set query = query _ " ASC"
    }
 }

 set rs = ##class(%SQL.Statement).%ExecDirect(,query,args...)

 #dim %sqlcontext As %SQLProcContext
 if rs.%SQLCODE >=0 {
     do %sqlcontext.AddResultSet(rs)
 } else { // pass errors to the caller
     set %sqlcontext.%SQLCODE = rs.%SQLCODE
     set %sqlcontext.%Message = rs.%Message
 }
 quit 1
}
Alexander Koblov · Sep 6, 2019

Not possible to do this in Query. You need to use dynamic SQL.

Symbol ":" is used to indicate host variables. Host variables are treated as expressions, not as identifiers.

During query compilation host variables are replaced with placeholders.

Consider query:

SELECT FirstName, MiddleName, LastName, Email, UserType 
FROM DB.ExternalUsers
WHERE FirstName like :objSearch.FirstName
ORDER BY :objSearch.SortingField

This query is compiled as:

SELECT FirstName, MiddleName, LastName, Email, UserType 
FROM DB.ExternalUsers
WHERE FirstName like ?
ORDER BY ?

Then during runtime you supply values as follows:
objSearch.FirstName = 'A%'
objSearch.SortingField = 'FirstName'

And query is executed as follows:

SELECT FirstName, MiddleName, LastName, Email, UserType 
FROM DB.ExternalUsers
WHERE FirstName like 'A%'
ORDER BY 'FirstName'

Notice 'FirstName' is in quotes in ORDER BY. So you sort by literal string. That is doing nothing.

What you can do is to use expression like:

 Order by CASE :objSearch.SortingField
          WHEN 'FirstName' THEN FirstName
          WHEN 'MiddleName' THEN MiddleName
          ...
          END

Although such generic queries makes SQL Query Analyzer unable to reason what plan is better to use for this query.

Alexander Koblov · Aug 30, 2019

Name the file login.csp

And inside it check for Error:ErrorCode request parameter:

<html><head><title>Login</title></head>
<body>
My login page:</br>
<form method='post'>
Name: <input name="CacheUserName"/><br/>
Password: <input type='password' name="CachePassword"/><br/>
<input type='submit'/>
</form>

<server>
Set tMsg = $Get(%request.Data("Error:ErrorCode",1))
    If ((tMsg'="")&&($SYSTEM.Status.GetErrorCodes(tMsg)'[$$$ERRORCODE($$$RequireAuthentication))) {
        &html<<center>>
        write "Auth failed!"
        &html<</center>>
    }
</server>
</body>
</html>
Alexander Koblov · Aug 30, 2019

You can use $system.OBJ.Export to export LUT document to file:

ENSDEMO>w $system.OBJ.Export("AlertTable.LUT","c:\temp\qq.lut")

Exporting to XML started on 08/30/2019 12:03:14
Exporting type : AlertTable.LUT
Export finished successfully.
1
ENSDEMO>w $system.OBJ.Load("c:\temp\qq.lut")

Load started on 08/30/2019 12:03:26
Loading file c:\temp\qq.lut as xml
Imported document: AlertTable.LUT
Load finished successfully.
1
Alexander Koblov · Aug 27, 2019

Hi Oliver.

It's hard to say what's wrong with your report, as example that you provided is not standalone -- it requires table AETMON.AvailabilityLog.

At least I think, you should change

Set var(tCount,0) = rs.%Get("availabilityStatus")
Set var(tCount,1) = rs.%Get("CreatedTime")

to

Set var(0, tCount) = rs.%Get("availabilityStatus")
Set var(1, tCount) = rs.%Get("CreatedTime")

You have seriesCount="1". That is amount of lines to show on chart.
Then you have seriesNames="Name1,Name2". These are names of lines. So either here you should have one name, or seriesCount should be 2.

You have seriesSize="2". This is amount of points to draw from each serie. If you indeed need only 2 points, then that's OK. However, if you remove this attribute then all points are plotted.

Generally, please see class reference for %ZEN.Report.Display.COSChart.cchart. It explains many attributes.

Below please find small sample that works. Ignore ReportDefinition. It's just there to make report run.

I hope it'll give you some hints.

Class community.ClineReport Extends %ZEN.Report.reportPage
{

Parameter DEFAULTMODE As STRING = "html";

/// 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="test" sql="select 1 q">
   <group name="q" breakOnField="q"/>
  </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="test">
    <document width="8.5in"
            height="11in"
            marginLeft="1.25in"
            marginRight="1.25in"
            marginTop="1.0in"
            marginBottom="1.0in" />
    <body>
        <clineChart
                ongetData="getChartData"
                title="Availability"
                height="400px"
                width="400px"
                currYAxis="0"
                labelsVisible="true"
                lineStyle="red"
                markersVisible="true"
                plotStyle="stroke-width: 1px;"
                seriesCount="2"
                seriesNames="Name1,Name2"
                seriesYAxes="0"
                ongetLabelX="getAxisTime"
                >
            <yAxis minValue="0"
                    maxValue="100" />
        </clineChart>
    </body>
</report>
}

Method getChartData(
    ByRef data,
    chartObject)
{
    for i=1:1:10 {
        set data(0,i-1)=i * 2
        set data(1,i-1)=i * 3
    }
}

Method getAxisTime(
    val,
    yseries)
{
 quit $zdt($H-100 + val)
}

}