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:
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>,
8.1 Version 03 Windows Server 2012
December 08, 2015, 12:27 PM
dhagen
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.
"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
December 08, 2015, 01:28 PM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 08, 2015, 01:44 PM
dhagen
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
In this case, the whole thing is dynamic. Add more columns and the measures get generated.
"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
December 08, 2015, 03:57 PM
Waz
Its a pity FRORMAT JSON doesn't cover this
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 08, 2015, 04:41 PM
Francis Mariani
dhagen, thanks for the detailed example. Regards.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 09, 2015, 11:28 AM
MarcL
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?
8.1 Version 03 Windows Server 2012
December 09, 2015, 04:47 PM
dhagen
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.
"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
December 10, 2015, 10:05 AM
Squatch
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):