Adobe Campaign Classic v7 & Campaign v8

rvnth · 6/18/24

Hi there,

I'm currently working on creating a report that includes counts for various scenarios where data is incomplete or conditions are not met. I've managed to generate a report, but I'm aiming for a format similar to the screenshot below for the final version.

var fquery = xtk.queryDef.create
(
<queryDef schema = {vars.targetSchema} operation = "select" >
<select>
     <node expr="Substring(@segmentCode,0,Charindex(@segmentCode,'_'))" label="Segment"  alias="@Segment"/>
           <node expr="Sum(Iif(Right(@segmentCode, Length(@segmentCode) - Charindex(@segmentCode,'_')) = 'RECIPIENT_EMAIL', 1, 0))" label="RECIPIENT_EMAIL" alias="@RECIPIENT_EMAIL"/>
    <node expr="Sum(Iif(Right(@segmentCode, Length(@segmentCode) - Charindex(@segmentCode,'_')) = 'IS_DELETED', 1, 0))" label="IS_DELETED_RECIPIENT" alias="@IS_DELETED_RECIPIENT"/>
    <node expr="Sum(Iif(Right(@segmentCode, Length(@segmentCode) - Charindex(@segmentCode,'_')) = 'IS_EMAIL_OPT_IN', 1, 0))" label="IS_EMAIL_OPT_IN" alias="@IS_EMAIL_OPT_IN"/>
        <node expr="Sum(Iif(Right(@segmentCode, Length(@segmentCode) - Charindex(@segmentCode,'_')) = 'IS_PRIMARY_EMAIL_INVALID', 1, 0))" label="IS_PRIMARY_EMAIL_INVALID" alias="@IS_PRIMARY_EMAIL_INVALID"/>
            
</select>
<groupBy>
     <node expr="@segmentCode"/>
  </groupBy>
</queryDef>

);

var itemsXMLF = fquery.ExecuteQuery(); // DOMElement
var itemsXMLFStr2 =  itemsXMLF.toString();
vars.itemsXMLFString2 = itemsXMLF.toXMLString();
logInfo("itemsXMLF = " + itemsXMLF.toString());
var itemsXMLF2 = DOMDocument.fromXMLString(vars.itemsXMLFString2); 
 logInfo(itemsXMLF2); 
var seg = itemsXMLF2.root.getElements();
logInfo(seg); 
var uniqueSegments = seg.filter(function(value, index, self) {
    return self.indexOf(value) === index;
    });
logInfo(uniqueSegments); 
//logInfo("itemsXMLF = " + itemsXMLF.toString());

For each record the column segment code will have value scen3_IS_EMAIL_OPTIN or Scen4_IS_DELETE or Scen3_IS_EMAIL_OPTIN

Thank you in advance