Hey all,

I'm including some simple TABLE FILE queries that do not have an output format specified yet. Is there some way to specify that using a setting instead of using ON TABLE PCHOLD?

The reason for this is that I want to keep those table file definitions as generic as possible; I don't want to include the output format in them, but instead want to control that from outside the TABLE FILE definition. Can that be done? And if so, how?

I'm effectively trying to accomplish something similar to what /ibi_apps/RemoteValues does (see also the discussion on "Wanting to beat HTML Composer into submission!"), but done properly.

What I'm looking for would look like the following:

<script type="text/javascript">
var tableFile = $("#query").text();
var data = "SET HOLD = PCHOLD\r\nSET FORMAT = XML\r\n" + tableFile;
</script>
<div id="query" style="display:none;">
TABLE FILE CAR
SUM FST.CAR
BY CAR
BY MODEL
WHERE COUNTRY = &country;
END
</div>

P.S. A JSON output format would even be better, but that doesn't appear to be supported (in our version).

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

ON TABLE PCHOLD FORMAT XML

Be warned, this gives you ugly XML. If you need to customize it per a homegrown DTD, you need to roll your own.

- ABT

How about SET ONLINE-FMT? It's in the help file but it does not specify XML.

  
SET ONLINE-FMT = XML

TABLE FILE CAR
SUM
   FST.CAR
BY CAR
BY MODEL
END

Regarding your comment about wanting JSON. Here is sample of something similar to what was used on a project of mine.

  
SET MESSAGE = OFF
SET HOLDLIST = PRINTONLY

-* FILEDEF to store the text file
FILEDEF H2 DISK h2.txt (APPEND
-RUN

TABLE FILE CAR
SUM
    COMPUTE CNT/I11 = CNT + 1;
    COMPUTE OPTION/A65 = '{"carText":"' || CAR || '", "carValue":"' || CAR || '"}';
BY CAR NOPRINT
ON TABLE HOLD AS H1
END
-RUN
-SET &LAST_RECORD = &RECORDS;

-* Write Opening Bracket
-WRITE H2 [

-* Write JSON items
TABLE FILE H1
PRINT
    COMPUTE OPTION/A70 = 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
EX EDAGET TXT,h2.txt,T

quote:

Originally posted by Mighty Max:
How about SET ONLINE-FMT? It's in the help file but it does not specify XML.

Ah, brilliant, that works! Silly documentation...

Regarding the JSON format; I can't readily hard-code all that stuff as I don't know what the query will be like before-hand. It just gets sent verbatim to the reporting server, which is also why I can't use ON TABLE PCHOLD.

I ran into a snag: FOC034...

If I execute this code in the command console (I call the adhocfex in my code):

SET ONLINE-FMT=XML

-SET &ECHO = ALL;
SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
TABLE FILE CAR
SUM COMPUTE CS/A43 = CAR | ' ('|| MODEL || ')';
BY COUNTRY
BY CAR
BY MODEL
END

The result is:

 0 ERROR AT OR NEAR LINE     11  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC034) THE PRINT LINE EXCEEDS THE MAXIMUM LINE WIDTH. WIDTH IS: 98

If I use ON TABLE PCHOLD FORMAT XML instead of SET OUTPUT-FMT=XML the code works, but I can't do that in my application...

Is there a way around this issue?

SET PANEL=200

Thanks, that helped.