I'm pretty new to using the Reporting Language and am struggling with how to loop over records in tables and generate computed outputs.
I'm trying to code up a fex that will take hierarchically structured data and output it as a JSON to a file ( to be consumed by a D3 visualization)
Using the sample wf_retail_geography master file and the code at the bottom of the message I've succeeded in creating a json that looks like:

 
[
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"Abu Rudeis"},                                                                                                                                          
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"Abu Simbel"},                                                                                                                                          
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"Abu Suwayr"},                                                                                                                                          
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"Alexandria"},                                                                                                                                          
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"Assiut"},                                                                                                                                              
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"Aswan"},                                                                                                                                               
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"Beni Suef"},                                                                                                                                           
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"Bilbeis"},                                                                                                                                             
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"Cairo"},                                                                                                                                               
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"El Alamein"},                                                                                                                                          
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"El Arish"},                                                                                                                                            
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"El Cairo"},                                                                                                                                            
{"continent":"Africa", "COUNTRY_NAME":"Egypt","city":"El Gouna"},  ...

]
 

but what I need is something like:

[           
{
  "name": "Root",
  "children": [
    {
      "name": "Africa",
      "children": [
        {
          "name": "Egypt",
          "children": [
            {
              "name": "Abu Rudeis"            
            },
            {
              "name": "Abu Simbel"
            },
            {
              "name": "Abu Suwayr"
            }
            ...
             
...     
}
]
 

My Code so Far

 
SET MESSAGE = OFF
SET HOLDLIST = PRINTONLY

-* now start appending to the file
FILEDEF H2 DISK h2.txt (APPEND
-RUN
-WRITE H2 [

TABLE FILE ibisamp/dimensions/wf_retail_geography
SUM
    COMPUTE CNT/I11 = CNT + 1;
    COMPUTE OPTION/A200 = '{"continent":"' || CONTINENT_NAME || '", "COUNTRY_NAME":"' || COUNTRY_NAME|| '","city":"' || CITY_NAME|| '"}';
BY WF_RETAIL_GEOGRAPHY.WF_RETAIL_GEOGRAPHY.CONTINENT_NAME
BY WF_RETAIL_GEOGRAPHY.WF_RETAIL_GEOGRAPHY.COUNTRY_NAME
BY WF_RETAIL_GEOGRAPHY.WF_RETAIL_GEOGRAPHY.CITY_NAME
ON TABLE HOLD AS H1
END
-RUN
-SET &LAST_RECORD = &RECORDS;

-* Write JSON items
TABLE FILE H1
PRINT
    COMPUTE OPTION/A205 = IF CNT EQ &LAST_RECORD THEN OPTION ELSE OPTION || ',';
ON TABLE HOLD AS H2 FORMAT ALPHA
END
-RUN

-* Write Closing Bracket
-WRITE H2 ]


-* Output the text file using EDAGET
-MRNOEDIT EX EDAGET TXT,h2.txt,T

 

Thanks in advance for any assistance.

This message has been edited. Last edited by: <Emily McAllister>,

What is destination of the output? If the output is a response to an ajax request, then I would recommend using XSLT to transform the output to JSON. I've done this many times in the past to support the building of highchart objects, and I find it way simpler then trying to code looping in a WF request.

In the context of WebFOCUS, how does one use XSLT to transform the output to JSON? I am very interested since I often use Highcharts.

Thanks very much, and cheers.

Pop me an email and I will send you the entire doc and examples ... I don't seem to have your email anymore ....

The short version:
Url Paramters:

IBIF_report_xsl - name of the XSL you would like to apply to the result set IF the result set is XML (default location ibi\WebFOCUS81\ibi_html\javaassist\ibi\html\describe)
IBIF_report_xsl_location - location of XSL file if it is not in the default location

My preferred REPORT and XMLR output:

TABLE FILE GGSALES
HEADING 
"Revenue vs. Budget"
"A Subtitle"
"xAxis Title"
"yAxis Title"
SUM
 COMPUTE MDOLLARS/D12.2 = DOLLARS / 100000; AS 'Revenue'
 COMPUTE BDOLLARS/D12.2 = BUDDOLLARS / 100000; AS 'Budget'
BY REGION AS 'Region'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD FORMAT XMLR
ON TABLE SET PAGE NOPAGE
ON TABLE SET ASNAMES ON
ON TABLE SET LINES 99999
END  

URL for ajax request:

/ibi_apps/WFServlet?IBIF_ex=build_ggs_xml&IBIF_report_xsl=hc_basic_bar  

XSL for generating highchart basic bar:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" 
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  
	xmlns:fo="http://www.w3.org/1999/XSL/Format " 
	xmlns:urldecoder="xalan://java.net.URLDecoder" 
	exclude-result-prefixes="urldecoder">

	<xsl:output method="text" media-type="application/json" encoding="ISO-8859-1"/>

	<!-- Load the column titles and fieldnames into a variable for later use. -->
	<xsl:variable name="title" select="/fxf/report/table/thead/tr/td[@class='title']"/>
	<xsl:variable name="fieldname" select="/fxf/report/field_desc/field/@fieldname"/>
	<xsl:variable name="totalrows" select="count(/fxf/report/table/tbody/tr)"/>
	<xsl:variable name="heading" select="/fxf/report/table/thead/tr[@linenum='0']/td[@class='heading']"/>
	<xsl:variable name="subheading" select="/fxf/report/table/thead/tr[@linenum='1']/td[@class='heading']"/>
	<xsl:variable name="xtitle" select="/fxf/report/table/thead/tr[@linenum='2']/td[@class='heading']"/>
	<xsl:variable name="ytitle" select="/fxf/report/table/thead/tr[@linenum='3']/td[@class='heading']"/>

	<!-- main template  -->
	<xsl:template match="/fxf/report">
	{ 
		"chart": {
			"type": "bar" 
		},
		"title": { 
			"text": 
			<xsl:choose>
				<xsl:when test="$heading!=''">
					"<xsl:value-of select="$heading" />"
				</xsl:when>
				<xsl:otherwise>
					null
				</xsl:otherwise>
			</xsl:choose>
		},
		"subtitle": { 
			"text":
			<xsl:choose>
				<xsl:when test="$subheading!=''">
					"<xsl:value-of select="$subheading" />"
				</xsl:when>
				<xsl:otherwise>
					null
				</xsl:otherwise>
			</xsl:choose>
		},
		"xAxis": {
			"categories": [
				<xsl:apply-templates select="field_desc/field"/>
			],
			"title": {
				"text":
				<xsl:choose>
					<xsl:when test="$xtitle!=''">
						"<xsl:value-of select="$xtitle" />"
					</xsl:when>
					<xsl:otherwise>
						null
					</xsl:otherwise>
				</xsl:choose>
			}
		},
		"yAxis": {
			"title": {
				"text":
				<xsl:choose>
					<xsl:when test="$ytitle!=''">
						"<xsl:value-of select="$ytitle" />"
					</xsl:when>
					<xsl:otherwise>
						null
					</xsl:otherwise>
				</xsl:choose>
			}
		},		
		"series": [
			<xsl:apply-templates select="table/tbody/tr"/>
		]
	}
	</xsl:template>

	<!-- title builder - use the field to loop, but he title variable when generating the value -->
	<xsl:template match="field_desc/field">
		<xsl:variable name="titlecount" select="position()"/>
			<xsl:choose>
				<xsl:when test="position()=1"/>
				<xsl:when test="position()=last()">
					"<xsl:value-of select="$title[$titlecount]"/>"
				</xsl:when>
				<xsl:otherwise>
					"<xsl:value-of select="$title[$titlecount]"/>",
				</xsl:otherwise>
			</xsl:choose>
	</xsl:template>

	<!-- data builder w/ dynamic element names to match the fieldnames. -->
	<xsl:template match="table/tbody/tr">
			{
			<xsl:for-each select="td">
				<xsl:choose>
					<xsl:when test="position()=1">
						"name": "<xsl:value-of select="text()"/>",
					</xsl:when>
					<xsl:when test="position()=2">
						"data": [ <xsl:value-of select="text()"/>
					</xsl:when>
					<xsl:otherwise>
						<xsl:value-of select="text()"/>
					</xsl:otherwise>
				</xsl:choose>
				<xsl:choose>
					<xsl:when test="position()=1" />
					<xsl:when test="position() > 1 and position()!=last()">
						,
					</xsl:when>
					<xsl:when test="position()=last()">
						]
					</xsl:when>
				</xsl:choose>
			</xsl:for-each>
			}
			<xsl:choose>
				<xsl:when test="position() != $totalrows">,</xsl:when>
			</xsl:choose>
	</xsl:template>

</xsl:stylesheet>  

Report Response

<?xml version="1.0" encoding="ISO-8859-1"?>
<fxf version="2.0" data="report">
<report records="4317" lines="4" columns="3" rows="4">
<created by="Information Builders, Inc." focexec="build_ggs_xml"/>
<target format="" version="" type="" destination=""/>
<settings>
<setting name="cdn" value="off"/>
<setting name="grid" value="on"/>
<page units="inches" orientation="portrait" size="letter" leftmargin="0.250" rightmargin="0.250" topmargin="0.250" bottommargin="0.250"/>
</settings>
<color_desc>
<color colornum="0" textcolor="000000" backcolor="FEFFFF"></color>
</color_desc>
<style_desc>
<style fontnum="0" size="10" style="0" colornum="0" face="DEFAULT-PROPORTIONAL"></style>
</style_desc>
<field_desc>
<field fieldnum="0" focus_format="A11" fieldname="REGION" alias="E05" title="Region" description="Region code"></field>
<field fieldnum="1" focus_format="D12.2" fieldname="REVENUE"></field>
<field fieldnum="2" focus_format="D12.2" fieldname="BUDGET"></field>
</field_desc>
<column_desc>
<col colnum="h0" class="heading" leftmargin="0" width="11" titlealign="none"></col>
<col colnum="ch1" fieldnum="0" class="title" leftmargin="0" width="11" titlealign="none"></col>
<col colnum="ch2" fieldnum="1" class="title" leftmargin="13" width="14" titlealign="none"></col>
<col colnum="ch3" fieldnum="2" class="title" leftmargin="29" width="14" titlealign="none"></col>
<col colnum="ch4" fieldnum="0" class="titleunderline" leftmargin="0" width="11" titlealign="none"></col>
<col colnum="ch5" fieldnum="1" class="titleunderline" leftmargin="13" width="14" titlealign="none"></col>
<col colnum="ch6" fieldnum="2" class="titleunderline" leftmargin="29" width="14" titlealign="none"></col>
<col colnum="c7" fieldnum="0" class="data" leftmargin="0" width="11" titlealign="none"></col>
<col colnum="c8" fieldnum="1" class="data" leftmargin="13" width="14" titlealign="none"></col>
<col colnum="c9" fieldnum="2" class="data" leftmargin="29" width="14" titlealign="none"></col>
<col colnum="f10" class="footing" leftmargin="0" width="11" titlealign="none"></col>
</column_desc>
<table pagenum="1">
<thead>
<tr linenum="0" linetype="heading">
<td colnum="h0" class="heading" id="0" fontnum="0">Revenue vs. Budget</td>
</tr>
<tr linenum="1" linetype="heading">
<td colnum="ch1" class="title" id="1" fontnum="0">Region</td>
<td colnum="ch2" class="title" id="2" fontnum="0">Revenue</td>
<td colnum="ch3" class="title" id="3" fontnum="0">Budget</td>
</tr>
<tr linenum="2" linetype="heading">
<td colnum="ch4" class="titleunderline" id="4" fontnum="0">------</td>
<td colnum="ch5" class="titleunderline" id="5" fontnum="0">-------</td>
<td colnum="ch6" class="titleunderline" id="6" fontnum="0">------</td>
</tr>
</thead>
<tbody>
<tr linenum="3" linetype="data">
<td colnum="c7" class="data" id="7" fontnum="0">Midwest</td>
<td colnum="c8" class="data" id="8" rawvalue="114.01" fontnum="0">114.01</td>
<td colnum="c9" class="data" id="9" rawvalue="111.94" fontnum="0">111.94</td>
</tr>

And it goes on
  

URL response after WF applies the XSL:

{ 
	"chart": {
		"type": "bar" 
	}, 
	"title": { 
		"text": "Revenue vs. Budget" 
	}, 
	"subtitle": { 
		"text": "A Subtitle" 
	}, 
	"xAxis": { 
		"categories": 
			[ "Revenue", "Budget" ], 
		"title": { 
			"text": "xAxis Title"
		} 
	}, 
	"yAxis": { 
		"title": { 
			"text": "yAxis Title"
		} 
	},	
	"series": [ 
		{ 
			"name": "Midwest", 
			"data": [ 114.01 , 111.94 ] 
		} , 
		{ 
			"name": "Northeast", 
			"data": [ 113.92 , 115.77 ] 
		} , 
		{ 
			"name": "Southeast", 
			"data": [ 117.10 , 118.08 ] 
		} , 
		{ 
			"name": "West", 
			"data": [ 116.53 , 116.42 ] 
		} 
	] 
}

In this case, the whole thing is dynamic. Add more columns and the measures get generated.

Its a pity FRORMAT JSON doesn't cover this

dhagen, thanks for the detailed example. Regards.

Dhagen,Thanks for the great example. I'm digging into XSLT now, and it looks promising.

My output would be to a local file. A D3 visualization webapp will reside on the reporting server will be linked to the BI portal through the a url defined in webfocus. The idea is the D3 vis will be configured to use the local json file which would be generated on demand (if possible) or on a schedule my running a fex. Does this sound like a reasonable approach? Or is their a better way?

The method I'm showing is for generating complex JSON documents on the fly and to be directly consumed by a web front end using an AJAX call of some sort. It WILL NOT create JSON files to be saved on the reporting server. You can create complex JSON files to be saved on the reporting server using the MODIFY syntax, but if you want to "generate on demand", then I don't see the point.

Also, I thought D3 was fully integrated with WF. If it is, then all of this may not be necessary and you should be able to use the D3 graphs as documented.

Maybe someone else can add their 2cents on D3, as I have no experience with it.

Okay, this challenge is just too good to pass up without giving it a try. I like JSON and wish there was more support for it in WebFOCUS.

Since I don't have MarcL's data to work with, I used the sample CAR file instead. I created an HTMLFORM section with enough JavaScript to generate the JSON format detailed at the beginning of this thread. Now, by doing that, I can only output the JSON data to a browser window, so I'm not sure if that is useful.

App Studio's internal browser doesn't seem to have JavaScript JSON manipulation capability, so I had to change App Studio so that it used Internet Explorer instead.

Anyway, here's the code I came up with:

ENGINE INT CACHE SET ON
SET MESSAGE = OFF
SET HOLDLIST = PRINTONLY

-* now start appending to the file
FILEDEF H2 DISK h2.txt (APPEND
-RUN
-WRITE H2 [

TABLE FILE ibisamp/car
SUM
    COMPUTE CNT/I11 = CNT + 1;
    COMPUTE OPTION/A200 = '{"country":"' || COUNTRY || '", "car":"' || CAR || '","model":"' || MODEL || '"}';
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
BY CAR.CARREC.MODEL
ON TABLE HOLD AS H1
END
-RUN
-SET &LAST_RECORD = &RECORDS;

-* Write JSON items
TABLE FILE H1
PRINT
    COMPUTE OPTION/A205 = IF CNT EQ &LAST_RECORD THEN OPTION ELSE OPTION || ',';
ON TABLE HOLD AS H2 FORMAT ALPHA
END
-RUN

-* Write Closing Bracket
-WRITE H2 ]


-* Output the text file using EDAGET
-* -MRNOEDIT EX EDAGET TXT,h2.txt,T

-HTMLFORM BEGIN
<!DOCTYPE html><html><head>
<script>
var json_object_in = !IBI.FIL.H2;
var json_object_out = [];

json_object_out.push({"name":"Root", "children":[]});
// Test code to make sure array population is correct
//json_object_out[0].children.push({"name":"africa", "children":[]});
//json_object_out[0].children[0].children.push({"name":"egypt", "children":[]});
//json_object_out[0].children[0].children[0].children.push({"name":"abu rudeis"});
//json_object_out[0].children[0].children[0].children.push({"name":"abu simbel"});
//json_object_out[0].children[0].children[0].children.push({"name":"abu suwayr"});
//json_object_out[0].children[0].children.push({"name":"libya", "children":[]});
//json_object_out[0].children[0].children[1].children.push({"name":"shabbu rudeis"});
//json_object_out[0].children[0].children[1].children.push({"name":"shammu simbel"});
//json_object_out[0].children[0].children[1].children.push({"name":"shammsa suwayr"});
//json_object_out[0].children.push({"name":"europe", "children":[]});
//json_object_out[0].children[1].children.push({"name":"germany", "children":[]});
//json_object_out[0].children[1].children[0].children.push({"name":"adolph maximus"});
//document.write(JSON.stringify(json_object_out));
var level1 = '';
var level2 = '';
var level1_counter = 0;
var level2_counter = -1;
var level3_counter = 0;

for (x = 0; x < json_object_in.length; x++) {
	if (level1 == json_object_in[x].country && level2 == json_object_in[x].car) {
		//alert("level1_counter=" + level1_counter + ", level2_counter=" + level2_counter + ", level3_counter=" + level3_counter);
		json_object_out[level1_counter].children[level2_counter].children[level3_counter].children.push({"name":json_object_in[x].model});
	} else if (level1 == json_object_in[x].country && level2 != json_object_in[x].car) {
		level3_counter++;
		json_object_out[level1_counter].children[level2_counter].children.push({"name":json_object_in[x].car, "children":[]});
		json_object_out[level1_counter].children[level2_counter].children[level3_counter].children.push({"name":json_object_in[x].model});
	} else {
		level2_counter++;
		level3_counter = 0;
		json_object_out[level1_counter].children.push({"name":json_object_in[x].country, "children":[]});
		json_object_out[level1_counter].children[level2_counter].children.push({"name":json_object_in[x].car, "children":[]});
		json_object_out[level1_counter].children[level2_counter].children[level3_counter].children.push({"name":json_object_in[x].model});
	}

	level1 = json_object_in[x].country;
	level2 = json_object_in[x].car;
}

document.write(JSON.stringify(json_object_out));
</script>
</head><body></body></html>
-HTMLFORM END

Output (after formatting the JSON to make it more readable):

[
   {
      "name":"Root",
      "children":[
         {
            "name":"ENGLAND",
            "children":[
               {
                  "name":"JAGUAR",
                  "children":[
                     {
                        "name":"V12XKE AUTO"
                     },
                     {
                        "name":"XJ12L AUTO"
                     }
                  ]
               },
               {
                  "name":"JENSEN",
                  "children":[
                     {
                        "name":"INTERCEPTOR III"
                     }
                  ]
               },
               {
                  "name":"TRIUMPH",
                  "children":[
                     {
                        "name":"TR7"
                     }
                  ]
               }
            ]
         },
         {
            "name":"FRANCE",
            "children":[
               {
                  "name":"PEUGEOT",
                  "children":[
                     {
                        "name":"504 4 DOOR"
                     }
                  ]
               }
            ]
         },
         {
            "name":"ITALY",
            "children":[
               {
                  "name":"ALFA ROMEO",
                  "children":[
                     {
                        "name":"2000 4 DOOR BERLINA"
                     },
                     {
                        "name":"2000 GT VELOCE"
                     },
                     {
                        "name":"2000 SPIDER VELOCE"
                     }
                  ]
               },
               {
                  "name":"MASERATI",
                  "children":[
                     {
                        "name":"DORA 2 DOOR"
                     }
                  ]
               }
            ]
         },
         {
            "name":"JAPAN",
            "children":[
               {
                  "name":"DATSUN",
                  "children":[
                     {
                        "name":"B210 2 DOOR AUTO"
                     }
                  ]
               },
               {
                  "name":"TOYOTA",
                  "children":[
                     {
                        "name":"COROLLA 4 DOOR DIX AUTO"
                     }
                  ]
               }
            ]
         },
         {
            "name":"W GERMANY",
            "children":[
               {
                  "name":"AUDI",
                  "children":[
                     {
                        "name":"100 LS 2 DOOR AUTO"
                     }
                  ]
               },
               {
                  "name":"BMW",
                  "children":[
                     {
                        "name":"2002 2 DOOR"
                     },
                     {
                        "name":"2002 2 DOOR AUTO"
                     },
                     {
                        "name":"3.0 SI 4 DOOR"
                     },
                     {
                        "name":"3.0 SI 4 DOOR AUTO"
                     },
                     {
                        "name":"530I 4 DOOR"
                     },
                     {
                        "name":"530I 4 DOOR AUTO"
                     }
                  ]
               }
            ]
         }
      ]
   }
]

This message has been edited. Last edited by: Squatch,