Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Summary Accordian Report
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Summary Accordian Report
 Login/Join
 
Guru
posted
I have posted this question before. I am trying to create an accordian report that shows summary level information than user drills is down to the object level information. Is it still not possible in Webfocus? Exmaple
                         U-sale   V-Sale W-Sale 

+ Division               1000      1000   1000  
    +District1           300       500    1000
           Store1        200       400    1000
           Store2        100      100      0
    +District2           700      500      0
           StoreA         400     500      0
           StoreB         300      0       0
                         --------------------
Total                    1000     1000     1000


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


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Not the way you want it.

TABLE FILE CAR
SUM SALES
BY COUNTRY
SUM SALES
BY COUNTRY
BY CAR
SUM SALES
BY COUNTRY
BY CAR
BY MODEL
ON TABLE SET EXPANDABLE ON
END


I have done something like this before, but it was with javascript to "Turn on/off" lines of the report.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
quote:
Not the way you want it.


TABLE FILE CAR
SUM SALES
BY COUNTRY
SUM SALES
BY COUNTRY
BY CAR
SUM SALES
BY COUNTRY
BY CAR
BY MODEL
ON TABLE SET EXPANDABLE ON
END


I have done something like this before, but it was with javascript to "Turn on/off" lines of the report.


Can give you example of that JAVE code with your report?


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
There is a way to do it. After much searching and testing I found several Javascript Framework plugins that can help perform what you need. The programming is somewhat complex and you will want to be careful with how many total records you will pushing to the browser. IE has a difficult time with a record count greater than 5000.

It would be great if we had a simple method for programming this. The expandable horizontal accordian just isn't useful, in my view. While an expandable vertical accordian, like this, is very useful.

Here's one of my test screenshots:



The Javascript Framework is JQuery (which I have written about on this forum) and the plugin is TreeTable.

The actual fex code is below:

-SET &ECHO=ALL;
SET HOLDLIST   = PRINTONLY
SET HOLDFORMAT = ALPHA
-RUN
TABLE FILE CAR
SUM
COMPUTE CAR/A16=' ';
COMPUTE MODEL/A24=' ';
SALES RETAIL_COST DEALER_COST
COMPUTE LEVEL/I1=1;
BY COUNTRY
ON TABLE HOLD AS TEST
END
FILEDEF TEST DISK TEST.FTM (APPEND

TABLE FILE CAR
SUM
COMPUTE MODEL/A24=' ';
SALES RETAIL_COST DEALER_COST
COMPUTE LEVEL/I1=2;
BY COUNTRY
BY CAR
ON TABLE HOLD AS TEST
END

TABLE FILE CAR
SUM
SALES RETAIL_COST DEALER_COST
COMPUTE LEVEL/I1=3;
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS TEST
END

TABLE FILE TEST
PRINT
COMPUTE OCNT  /I2 = OCNT+1                                              ;NOPRINT
COMPUTE AOCNT /A5 = LJUST(5,FTOA(OCNT,'(D4)',AOCNT),AOCNT)              ;NOPRINT
COMPUTE CNT   /I2 = IF LEVEL EQ 1 THEN 1+CNT ELSE CNT                   ;NOPRINT
COMPUTE ACNT  /A5 = LJUST(5,FTOA(CNT,'(D4)',ACNT),ACNT)                 ;NOPRINT
COMPUTE NCNT  /I2 = IF COUNTRY NE LAST COUNTRY THEN 1 ELSE NCNT+1       ;NOPRINT
COMPUTE ANCNT /A5 = LJUST(5,FTOA(NCNT,'(D4)',ANCNT),ANCNT)              ;NOPRINT
COMPUTE N2CNT /I2 = IF COUNTRY NE LAST COUNTRY THEN OCNT ELSE LAST N2CNT;NOPRINT
COMPUTE AN2CNT/A5 = LJUST(5,FTOA(N2CNT,'(D4)',AN2CNT),AN2CNT)           ;NOPRINT
COMPUTE N3CNT /I2 = IF CAR NE LAST CAR THEN OCNT ELSE LAST N3CNT        ;NOPRINT
COMPUTE AN3CNT/A5 = LJUST(5,FTOA(N3CNT,'(D4)',AN3CNT),AN3CNT)           ;NOPRINT
COMPUTE XXX/A120=
     IF LEVEL EQ 1
     THEN '<tr id="node-'||AOCNT||'"><td><span class="folder">'||COUNTRY||'</span></td>'
     ELSE
     IF LEVEL EQ 2
     THEN '<tr id="node-'||AOCNT||'" class="child-of-node-'    ||AN2CNT ||'"><td><span class="folder">'||CAR  ||'</span></td>'
     ELSE
     IF LEVEL EQ 3
     THEN '<tr id="node-'||AOCNT||'" class="child-of-node-'    ||AN3CNT ||'"><td><span class="file">'  ||MODEL||'</span></td>'
     ELSE ''
;AS ''

COMPUTE SALESX      /A50=FTOA(SALES,'(D12.2M)',SALESX);NOPRINT
COMPUTE RETAIL_COSTX/A50=FTOA(RETAIL_COST,'(D12.2M)',RETAIL_COSTX);NOPRINT
COMPUTE DEALER_COSTX/A50=FTOA(DEALER_COST,'(D12.2M)',DEALER_COSTX);NOPRINT
COMPUTE SALES       /A80='<td class=right>'||SALESX||'</td>';
COMPUTE RETAIL_COST /A80='<td class=right>'||RETAIL_COSTX||'</td>';
COMPUTE DEALER_COST /A80='<td class=right>'||DEALER_COSTX||'</td></tr>';
BY COUNTRY NOPRINT
BY CAR     NOPRINT
BY MODEL   NOPRINT
BY LEVEL   NOPRINT
WHERE LEVEL LT 4
ON TABLE HOLD AS FILEA
ON TABLE SET PAGE NOLEAD
ON     TABLE SET STYLE *
     UNITS=IN,
     LEFTMARGIN=0.250000,
     RIGHTMARGIN=0.250000,
     TOPMARGIN=0.250000,
     BOTTOMMARGIN=0.250000,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
     RIGHTGAP=0.125000,
$
ENDSTYLE
END
-*EXIT
-HTMLFORM BEGIN
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html lang="en" xml:lang="en" xmlns="http://www.w3.org/1999/xhtml">

<head>
  <link href="\\infobrpt\mreprod\ibi\apps\js\treeTable\doc\stylesheets\master.css" rel="stylesheet" type="text/css" />
  <script type="text/javascript" src="\\infobrpt\mreprod\ibi\apps\js\jquery-1.4.2.min.js"></script>
  <script type="text/javascript" src="\\infobrpt\mreprod\ibi\apps\js\jquery-ui-1.8rc3.custom.min.js"></script>
  <!-- BEGIN Plugin Code -->

  <link href="\\infobrpt\mreprod\ibi\apps\js\treeTable\src\stylesheets\jquery.treeTable.css" rel="stylesheet" type="text/css" />
  <script type="text/javascript" src="\\infobrpt\mreprod\ibi\apps\js\treeTable\src\javascripts\jquery.treeTable.min.js"></script>
  <script type="text/javascript">

  $(document).ready(function(){
    $("#car").treeTable({
    expandable: true,
    clickableNodeNames: true,
    indent: 15,
    initialState: "collapsed",
    treeColumn: 0
    });

// Configure draggable nodes
$("#car .file, #car .folder").draggable({
  helper: "clone",
  opacity: .75,
  refreshPositions: true, // Performance?
  revert: "invalid",
  revertDuration: 300,
  scroll: true
});

// Configure droppable rows
$("#car .folder").each(function() {
  $(this).parents("tr").droppable({
    accept: ".file, .folder",
    drop: function(e, ui) {
      // Call jQuery treeTable plugin to move the branch
      $($(ui.draggable).parents("tr")).appendBranchTo(this);
    },
    hoverClass: "accept",
    over: function(e, ui) {
      // Make the droppable branch expand when a draggable node is moved over it.
      if(this.id != $(ui.draggable.parents("tr")[0]).id && !$(this).is(".expanded")) {
        $(this).expand();
      }
    }
  });
});

 /// Make visible that a row is clicked
$("table#car tbody tr").mousedown(function() {
  $("tr.selected").removeClass("selected"); // Deselect currently selected rows
  $(this).addClass("selected");
});

// Make sure row is selected when span is clicked
$("table#car tbody tr span").mousedown(function() {
  $($(this).parents("tr")[0]).trigger("mousedown");
});
 });  </script>

  <!-- END Plugin Code -->
</head>
<body>

<table id="car">
  <thead>
    <tr>
      <th>Car</th>
      <th>Sales</th>
      <th>Retail Cost</th>
      <th>Dealer Cost</th>
    </tr>
  </thead>
  <tbody>
!IBI.FIL.FILEA;
  </tbody>
</table>

</body>
</html>
-HTMLFORM END


Cheers!

David



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
David,

WELL DONE!!! Tip of the Year!!!!! So far, anyway... Don't like the hard-coding of the headings, though, I would add a loop and create them as amperes...

Hopefully, Kerry will see this and let IBI know...

Good One

Tom

EDIT: Strike the hard-coding part - We do it any way in Headings/Subheads...Never mind! Smiler

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 7.7.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1946 | Location: Parker, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks for the feedback Tom. I've seen much great HELP from you, Fancis, Waz, and others and I felt it was time to 'Give back' something of my own that might be useful.

Who is Kerry?

BTW, I think you worked here at Brinker a while back. I've seen your name on a few fexes!

Thanks again.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
Hi David,

YEP! Brinker, great folks!!! Was there for a while, say Hi to Edwin for me, he is a great resource!

Kerry is the Focal Point moderator, she monitors the Forum, and provides some answers and links to assist in opening cases with IBI. She is with IBI.

THANKS!! for giving back, too many just take...

AND, this, is a great tip!

Tom


Tom Flynn
WebFOCUS 7.7.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1946 | Location: Parker, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
Can give you example of that JAVE code with your report?


This is what i posted back in 2009, similar, but not as nice looking as Davids.

http://forums.informationbuild...061047413#5061047413


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
Guys, thank you for being a great help. I was in IBI class earlier today, that is why I couldnt answer earlier. I appriciate all the help.
When I past Daves code for some reason, I am not getting same option as he has show in the email. I am pasting following code
  -SET &ECHO=ALL;
SET HOLDLIST   = PRINTONLY
SET HOLDFORMAT = ALPHA
-RUN
TABLE FILE CAR
SUM
COMPUTE CAR/A16=' ';
COMPUTE MODEL/A24=' ';
SALES RETAIL_COST DEALER_COST
COMPUTE LEVEL/I1=1;
BY COUNTRY
ON TABLE HOLD AS TEST
END
FILEDEF TEST DISK TEST.FTM (APPEND

TABLE FILE CAR
SUM
COMPUTE MODEL/A24=' ';
SALES RETAIL_COST DEALER_COST
COMPUTE LEVEL/I1=2;
BY COUNTRY
BY CAR
ON TABLE HOLD AS TEST
END

TABLE FILE CAR
SUM
SALES RETAIL_COST DEALER_COST
COMPUTE LEVEL/I1=3;
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS TEST
END

TABLE FILE TEST
PRINT
COMPUTE OCNT  /I2 = OCNT+1                                              ;NOPRINT
COMPUTE AOCNT /A5 = LJUST(5,FTOA(OCNT,'(D4)',AOCNT),AOCNT)              ;NOPRINT
COMPUTE CNT   /I2 = IF LEVEL EQ 1 THEN 1+CNT ELSE CNT                   ;NOPRINT
COMPUTE ACNT  /A5 = LJUST(5,FTOA(CNT,'(D4)',ACNT),ACNT)                 ;NOPRINT
COMPUTE NCNT  /I2 = IF COUNTRY NE LAST COUNTRY THEN 1 ELSE NCNT+1       ;NOPRINT
COMPUTE ANCNT /A5 = LJUST(5,FTOA(NCNT,'(D4)',ANCNT),ANCNT)              ;NOPRINT
COMPUTE N2CNT /I2 = IF COUNTRY NE LAST COUNTRY THEN OCNT ELSE LAST N2CNT;NOPRINT
COMPUTE AN2CNT/A5 = LJUST(5,FTOA(N2CNT,'(D4)',AN2CNT),AN2CNT)           ;NOPRINT
COMPUTE N3CNT /I2 = IF CAR NE LAST CAR THEN OCNT ELSE LAST N3CNT        ;NOPRINT
COMPUTE AN3CNT/A5 = LJUST(5,FTOA(N3CNT,'(D4)',AN3CNT),AN3CNT)           ;NOPRINT
COMPUTE XXX/A120=
     IF LEVEL EQ 1
     THEN '<tr id="node-'||AOCNT||'"><td><span class="folder">'||COUNTRY||'</span></td>'
     ELSE
     IF LEVEL EQ 2
     THEN '<tr id="node-'||AOCNT||'" class="child-of-node-'    ||AN2CNT ||'"><td><span class="folder">'||CAR  ||'</span></td>'
     ELSE
     IF LEVEL EQ 3
     THEN '<tr id="node-'||AOCNT||'" class="child-of-node-'    ||AN3CNT ||'"><td><span class="file">'  ||MODEL||'</span></td>'
     ELSE ''
;AS ''

COMPUTE SALESX      /A50=FTOA(SALES,'(D12.2M)',SALESX);NOPRINT
COMPUTE RETAIL_COSTX/A50=FTOA(RETAIL_COST,'(D12.2M)',RETAIL_COSTX);NOPRINT
COMPUTE DEALER_COSTX/A50=FTOA(DEALER_COST,'(D12.2M)',DEALER_COSTX);NOPRINT
COMPUTE SALES       /A80='<td class=right>'||SALESX||'</td>';
COMPUTE RETAIL_COST /A80='<td class=right>'||RETAIL_COSTX||'</td>';
COMPUTE DEALER_COST /A80='<td class=right>'||DEALER_COSTX||'</td></tr>';
BY COUNTRY NOPRINT
BY CAR     NOPRINT
BY MODEL   NOPRINT
BY LEVEL   NOPRINT
WHERE LEVEL LT 4
ON TABLE HOLD AS FILEA
ON TABLE SET PAGE NOLEAD
ON     TABLE SET STYLE *
     UNITS=IN,
     LEFTMARGIN=0.250000,
     RIGHTMARGIN=0.250000,
     TOPMARGIN=0.250000,
     BOTTOMMARGIN=0.250000,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
     RIGHTGAP=0.125000,
$
ENDSTYLE
END
-*EXIT
-HTMLFORM BEGIN
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html lang="en" xml:lang="en" xmlns="http://www.w3.org/1999/xhtml">

<head>
  <link href="\\infobrpt\mreprod\ibi\apps\js\treeTable\doc\stylesheets\master.css" rel="stylesheet" type="text/css" />
  <script type="text/javascript" src="\\infobrpt\mreprod\ibi\apps\js\jquery-1.4.2.min.js"></script>
  <script type="text/javascript" src="\\infobrpt\mreprod\ibi\apps\js\jquery-ui-1.8rc3.custom.min.js"></script>
  <!-- BEGIN Plugin Code -->

  <link href="\\infobrpt\mreprod\ibi\apps\js\treeTable\src\stylesheets\jquery.treeTable.css" rel="stylesheet" type="text/css" />
  <script type="text/javascript" src="\\infobrpt\mreprod\ibi\apps\js\treeTable\src\javascripts\jquery.treeTable.min.js"></script>
  <script type="text/javascript">

  $(document).ready(function(){
    $("#car").treeTable({
    expandable: true,
    clickableNodeNames: true,
    indent: 15,
    initialState: "collapsed",
    treeColumn: 0
    });

// Configure draggable nodes
$("#car .file, #car .folder").draggable({
  helper: "clone",
  opacity: .75,
  refreshPositions: true, // Performance?
  revert: "invalid",
  revertDuration: 300,
  scroll: true
});

// Configure droppable rows
$("#car .folder").each(function() {
  $(this).parents("tr").droppable({
    accept: ".file, .folder",
    drop: function(e, ui) {
      // Call jQuery treeTable plugin to move the branch
      $($(ui.draggable).parents("tr")).appendBranchTo(this);
    },
    hoverClass: "accept",
    over: function(e, ui) {
      // Make the droppable branch expand when a draggable node is moved over it.
      if(this.id != $(ui.draggable.parents("tr")[0]).id && !$(this).is(".expanded")) {
        $(this).expand();
      }
    }
  });
});

 /// Make visible that a row is clicked
$("table#car tbody tr").mousedown(function() {
  $("tr.selected").removeClass("selected"); // Deselect currently selected rows
  $(this).addClass("selected");
});

// Make sure row is selected when span is clicked
$("table#car tbody tr span").mousedown(function() {
  $($(this).parents("tr")[0]).trigger("mousedown");
});
 });  </script>

  <!-- END Plugin Code -->
</head>
<body>

<table id="car">
  <thead>
    <tr>
      <th>Car</th>
      <th>Sales</th>
      <th>Retail Cost</th>
      <th>Dealer Cost</th>
    </tr>
  </thead>
  <tbody>
!IBI.FIL.FILEA;
  </tbody>
</table>

</body>
</html>
-HTMLFORM END
I am getting following output
Car     Sales        Retail  Cost  Dealer  Cost 
ENGLAND $12,000.00 $ 45,319.00      $37,853.00 
JAGUAR $12,000.00 $22,369.00 $18,621.00 
V12XKE AUTO $.00 $8,878.00 $7,427.00 
XJ12L AUTO $12,000.00 $13,491.00 $11,194.00 
JENSEN $.00 $17,850.00 $14,940.00 
INTERCEPTOR III $.00 $17,850.00 $14,940.00 
TRIUMPH $.00 $5,100.00 $4,292.00 



WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Arif, my code will not work for you unless you have the required JQuery javascript framework plus plugins. You will need to goto: https://www.jquery.com.

You will need JQUERY, Treetable, and JQUERY UI. The Treetable plugin download contains the master.css and treetable.css files.

Then you will need to replace the following code with your own internal links to wherever you've posted the 3 javascripts plus 2 css.

  <link href="\\infobrpt\mreprod\ibi\apps\js\treeTable\doc\stylesheets\master.css" rel="stylesheet" type="text/css" />
  <script type="text/javascript" src="\\infobrpt\mreprod\ibi\apps\js\jquery-1.4.2.min.js"></script>
  <script type="text/javascript" src="\\infobrpt\mreprod\ibi\apps\js\jquery-ui-1.8rc3.custom.min.js"></script>
  <link href="\\infobrpt\mreprod\ibi\apps\js\treeTable\src\stylesheets\jquery.treeTable.css" rel="stylesheet" type="text/css" />
  <script type="text/javascript" src="\\infobrpt\mreprod\ibi\apps\js\treeTable\src\javascripts\jquery.treeTable.min.js"></script>


As I said earlier, this is somewhat complex as you will need to do plenty of the ol' "try and try again" to get everything looking the way you want.

Until IBI comes up with a simple way to this (I've got some suggestions if IBI wants them), this is the best I could come up with. I'm exploring FLEX to see if it has a simpler built in way of performing, but I'm not confident so far.

Good luck!



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
The "vertical" accordian report produced by the code below isn't as pretty as the one generated by the JQuery technique described above, but because it doesn't use JavaScript or embedded HTML code, it works for both HTML and Excel formats. With parameterization of the StyleSheet, it could probably be made to work properly for PDF, too. The procedure calls itself to generate drill-down/roll-up reports. Therefore, each drill-down/roll-up results in a call to the reporting server. But since this technique uses a HOLD file, performance should be fairly good as long as the amount of data held is not enormous. The JavaScript technique will of course be faster because the drill-downs/roll-ups all take place within the browser.

One problem I could not solve for HTML output is the addition of a blank row between each row of SUBFOOT output when the GRID is turned OFF. This problem makes the report twice as long as it needs to be.

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
-*
-DEFAULTS &CARHOLD = 'N';
-DEFAULTS &COUNTRY = ' '
-DEFAULTS &CAR = ' '
-DEFAULTS &LEVEL = 1
-DEFAULTS &FLAG = 'X'
-*
-SET &THISEXEC = EDIT(&FOCFOCEXEC,'$99999999');
-SET &RNDM = EDIT(HHMMSS('A8'),'99$99$99');
-*
-IF &CARHOLD EQ 'Y' GOTO REPORT ;
-*
TABLE FILE CAR
 SUM
 COMPUTE CAR/A16   = ' ';
 COMPUTE MODEL/A24 = ' ';
 SEATS SALES/D9 RETAIL_COST DEALER_COST
 COMPUTE LEVEL/I1  = 1 ;
 COMPUTE FLAG/A1   = '+';
 COMPUTE DUMMY/A10 = 'XXXXXXXXXX';
 BY COUNTRY
 ON TABLE HOLD AS FOCCACHE/CARHOLD FORMAT ALPHA
END
-*
APP FILEDEF CARHOLD DISK FOCCACHE/CARHOLD.FTM (APPEND
-*
TABLE FILE CAR
 SUM
 COMPUTE MODEL/A24 = ' ';
 SEATS SALES/D9 RETAIL_COST DEALER_COST
 COMPUTE LEVEL/I1  = 2 ;
 COMPUTE FLAG/A1   = 'X';
 COMPUTE DUMMY/A10 = 'XXXXXXXXXX';
 BY COUNTRY
 BY CAR
 ON TABLE SAVE AS CARHOLD
END
-*
TABLE FILE CAR
 SUM
 SEATS SALES/D9 RETAIL_COST DEALER_COST
 COMPUTE LEVEL/I1  = 3 ;
 COMPUTE FLAG/A1   = 'X';
 COMPUTE DUMMY/A10 = 'XXXXXXXXXX';
 BY COUNTRY
 BY CAR
 BY MODEL
 ON TABLE SAVE AS CARHOLD
END
-*
TABLE FILE CAR
 SUM
 COMPUTE COUNTRY/A10 = 'ZZZZZZZZZZ';
 COMPUTE CAR/A16     = ' ';
 COMPUTE MODEL/A24   = ' ';
 SEATS SALES/D9 RETAIL_COST DEALER_COST
 COMPUTE LEVEL/I1    = 0 ;
 COMPUTE FLAG/A1     = '+';
 COMPUTE DUMMY/A10   = 'XXXXXXXXXX';
 ON TABLE SAVE AS CARHOLD
END
-RUN
-*
-REPORT
-IF (&FLAG EQ 'X') GOTO SKIP_SAVE ;
-*
DEFINE FILE FOCCACHE/CARHOLD
 FLAGX/A1 = IF (LEVEL EQ 0) THEN '+' ELSE
            IF (COUNTRY NE '&COUNTRY') THEN FLAG ELSE
            IF ((LEVEL EQ 1) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '+')) THEN '-' ELSE
            IF ((LEVEL EQ 1) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '-')) THEN '+' ELSE
            IF ((LEVEL EQ 2) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '+')) THEN '+' ELSE
            IF ((LEVEL EQ 2) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '-')) THEN 'X' ELSE
            IF ((LEVEL EQ 3) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '-')) THEN 'X' ELSE
            IF ((CAR EQ '&CAR') AND (LEVEL EQ 2) AND (&LEVEL EQ 2) AND ('&FLAG' EQ '+')) THEN '-' ELSE
            IF ((CAR EQ '&CAR') AND (LEVEL EQ 2) AND (&LEVEL EQ 2) AND ('&FLAG' EQ '-')) THEN '+' ELSE
            IF ((CAR EQ '&CAR') AND (LEVEL EQ 3) AND (&LEVEL EQ 2) AND ('&FLAG' EQ '+')) THEN '+' ELSE
            IF ((CAR EQ '&CAR') AND (LEVEL EQ 3) AND (&LEVEL EQ 2) AND ('&FLAG' EQ '-')) THEN 'X' ELSE FLAG;
END
-*
TABLE FILE FOCCACHE/CARHOLD
 SUM SEATS SALES/D9 RETAIL_COST DEALER_COST
 BY DUMMY
 BY COUNTRY
 BY CAR
 BY MODEL
 BY LEVEL
 BY FLAGX AS 'FLAG'
 ON TABLE HOLD AS FOCCACHE/CARHOLD FORMAT ALPHA
END
-*
-SKIP_SAVE
-*
TABLE FILE FOCCACHE/CARHOLD
 SUM MAX.LEVEL
 WHERE (FLAG NE 'X');
 ON TABLE SAVE AS MAXLEVEL
END
-RUN
-READ MAXLEVEL, &MAXLEVEL
-*
TABLE FILE FOCCACHE/CARHOLD
 BY DUMMY NOPRINT
 BY COUNTRY NOPRINT
 BY DUMMY NOPRINT
 BY CAR NOPRINT
 BY MODEL NOPRINT
 WHERE (FLAG NE 'X');
 ON DUMMY SUBHEAD
  "Country<+0>Seats<+0>Dealer Cost<+0>Retail Cost<+0>Sales"
  WHEN &MAXLEVEL EQ 1 ;
 ON DUMMY SUBHEAD
  "Country / Car<+0>Seats<+0>Dealer Cost<+0>Retail Cost<+0>Sales"
  WHEN &MAXLEVEL EQ 2 ;
 ON DUMMY SUBHEAD
  "Country / Car / Model<+0>Seats<+0>Dealer Cost<+0>Retail Cost<+0>Sales"
  WHEN &MAXLEVEL EQ 3 ;
 ON MODEL SUBFOOT
  "<FLAG<COUNTRY<SEATS<DEALER_COST<RETAIL_COST<SALES"
  WHEN LEVEL EQ 1 ;
 ON MODEL SUBFOOT
  " <FLAG<CAR<SEATS<DEALER_COST<RETAIL_COST<SALES"
  WHEN LEVEL EQ 2 ;
 ON MODEL SUBFOOT
  " <+0> <+0> <MODEL<SEATS<DEALER_COST<RETAIL_COST<SALES"
  WHEN LEVEL EQ 3 ;
 ON COUNTRY SUBFOOT
  "TOTALS<SEATS<DEALER_COST<RETAIL_COST<SALES"
  WHEN LEVEL EQ 0 ;
 ON TABLE SET PAGE NOPAGE
 ON TABLE PCHOLD FORMAT HTML
-* ON TABLE PCHOLD FORMAT EXL2K
 ON TABLE SET STYLE *
  UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, FONT='ARIAL', SIZE=9, STYLE=NORMAL, HEADALIGN=BODY, GRID=Off, $
  TYPE=SUBHEAD, LINE=1, OBJECT=TEXT, STYLE=BOLD, BACKCOLOR=SILVER, $
  TYPE=SUBHEAD, LINE=1, OBJECT=TEXT, ITEM=1, COLSPAN=4, $
  TYPE=SUBFOOT, OBJECT=FIELD, JUSTIFY=RIGHT, $
  TYPE=SUBFOOT, OBJECT=FIELD, BACKCOLOR=LIGHT BLUE, WHEN=LEVEL EQ 1, $
  TYPE=SUBFOOT, OBJECT=FIELD, BACKCOLOR=PALE GREEN, WHEN=LEVEL EQ 2, $
  TYPE=SUBFOOT, OBJECT=FIELD, BACKCOLOR=YELLOW, WHEN=LEVEL EQ 3, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=2, BACKCOLOR=LIGHT BLUE, COLSPAN=3, JUSTIFY=LEFT, WHEN=LEVEL EQ 1, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=2, BACKCOLOR=PALE GREEN, COLSPAN=2, JUSTIFY=LEFT, WHEN=LEVEL EQ 2, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=1, BACKCOLOR=YELLOW, JUSTIFY=LEFT, WHEN=LEVEL EQ 3, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=1, STYLE=-UNDERLINE, JUSTIFY=CENTER, SIZE=11, FOCEXEC=&THISEXEC( \
   FLAG=FLAG COUNTRY=COUNTRY LEVEL=LEVEL RNDM=&RNDM CARHOLD='Y'), WHEN=LEVEL EQ 1, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=1, STYLE=-UNDERLINE, JUSTIFY=RIGHT, SIZE=11, FOCEXEC=&THISEXEC( \
   FLAG=FLAG COUNTRY=COUNTRY CAR=CAR LEVEL=LEVEL RNDM=&RNDM CARHOLD='Y'), WHEN=LEVEL EQ 2, $
  TYPE=SUBFOOT, OBJECT=TEXT, ITEM=1, COLSPAN=4, STYLE=BOLD, BACKCOLOR=SILVER, WHEN=LEVEL EQ 0, $
  TYPE=SUBFOOT, OBJECT=FIELD, STYLE=BOLD, BACKCOLOR=SILVER, WHEN=LEVEL EQ 0, $
 ENDSTYLE
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
quote:
e problem I could not solve for HTML output is the addition of a blank row between each row of SUBFOOT output when the GRID is turned OFF. This problem makes the report twice as long as it neds to be.



Thank you Dan. It looks great. The only problem is when I execute this procedue I see the summary level infomaiton but when I click plus sign to drill it down i get error message that procedure not found.


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
when I click plus sign to drill it down i get error message that procedure not found


Arif, the drill-down is implemented by using the FOCEXEC keyword in the stylesheet. If you analyse the code, you'll see that it's relying on the &THISEXEC variable to determine the procedure to execute upon clicking the drill-down link. I am confident that you will be able to debug the code, track which value is being assigned to that variable and adjust it to suit your environment.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Arif, if you examine the code, you will see that a variable is being set with the current name of the running focexec.

-SET &THISEXEC = EDIT(&FOCFOCEXEC,'$9999999999');


I saved mine as "TEST12.FEX". To make it work, I extended the EDIT mask with two more 9's. The program works as advertised.

As a help to you, you will want to read the Focus manual regarding DrillDowns

Dan, great job. Not as complicated as my JQuery example. True, not as pretty, but as far as styling, well, I'm sure a couple of other GURU's could gussy up your code and come up with something prettier than mine! Regards the spacing, I'm sure I've seen a few answers to the same problem of blank rows after subtotals/subfoots/etc somewhere in the Focal Point cloud. What's interesting about the Excel version is the blank rows are gone. The problem with the Excel/PDF version is that each click creates a separate report. HTML is doing that, too, but it overwrites the existing page to make it look like it's dropping down or collapsing up.

We also had a fex that used WebFocus drill-downs as a psuedo vertical accordian, but it was slow wading through the amount of data and regenerating/rebuilding the HTML with each expansion/contraction. We needed a way to pull large amounts of data (2 to 3 thousand rows) into the browser. If you do a search on Google, there are a few Data Grid Accordian tools out there, some costing a pretty penny. After I tested about 12 different tools, the JQuery method met our criteria for:
1. Ease of use (relatively speaking compared to other tools/methods)
2. Look
3. Speed
4. HTML only

But I'll repeat, Nice Job!

Cheers,

David



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I was finally able to eliminate the unwanted blank lines in the HTML output by turning ON the GRID and setting the BORDER size to zero.

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
-*
-DEFAULTS &CARHOLD = 'N';
-DEFAULTS &COUNTRY = ' '
-DEFAULTS &CAR = ' '
-DEFAULTS &LEVEL = 1
-DEFAULTS &FLAG = 'X'
-*
-SET &THISEXEC = EDIT(&FOCFOCEXEC,'$99999999');
-SET &RNDM = EDIT(HHMMSS('A8'),'99$99$99');
-*
-IF &CARHOLD EQ 'Y' GOTO REPORT ;
-*
TABLE FILE CAR
 SUM
 COMPUTE CAR/A16   = ' ';
 COMPUTE MODEL/A24 = ' ';
 SEATS SALES/D9 RETAIL_COST DEALER_COST
 COMPUTE LEVEL/I1  = 1 ;
 COMPUTE FLAG/A1   = '+';
 COMPUTE DUMMY/A10 = 'XXXXXXXXXX';
 BY COUNTRY
 ON TABLE HOLD AS FOCCACHE/CARHOLD FORMAT ALPHA
END
-*
APP FILEDEF CARHOLD DISK FOCCACHE/CARHOLD.FTM (APPEND
-*
TABLE FILE CAR
 SUM
 COMPUTE MODEL/A24 = ' ';
 SEATS SALES/D9 RETAIL_COST DEALER_COST
 COMPUTE LEVEL/I1  = 2 ;
 COMPUTE FLAG/A1   = 'X';
 COMPUTE DUMMY/A10 = 'XXXXXXXXXX';
 BY COUNTRY
 BY CAR
 ON TABLE SAVE AS CARHOLD
END
-*
TABLE FILE CAR
 SUM
 SEATS SALES/D9 RETAIL_COST DEALER_COST
 COMPUTE LEVEL/I1  = 3 ;
 COMPUTE FLAG/A1   = 'X';
 COMPUTE DUMMY/A10 = 'XXXXXXXXXX';
 BY COUNTRY
 BY CAR
 BY MODEL
 ON TABLE SAVE AS CARHOLD
END
-*
TABLE FILE CAR
 SUM
 COMPUTE COUNTRY/A10 = 'ZZZZZZZZZZ';
 COMPUTE CAR/A16     = ' ';
 COMPUTE MODEL/A24   = ' ';
 SEATS SALES/D9 RETAIL_COST DEALER_COST
 COMPUTE LEVEL/I1    = 0 ;
 COMPUTE FLAG/A1     = '+';
 COMPUTE DUMMY/A10   = 'XXXXXXXXXX';
 ON TABLE SAVE AS CARHOLD
END
-RUN
-*
-REPORT
-IF (&FLAG EQ 'X') GOTO SKIP_SAVE ;
-*
DEFINE FILE FOCCACHE/CARHOLD
 FLAGX/A1 = IF (LEVEL EQ 0) THEN '+' ELSE
            IF (COUNTRY NE '&COUNTRY') THEN FLAG ELSE
            IF ((LEVEL EQ 1) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '+')) THEN '-' ELSE
            IF ((LEVEL EQ 1) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '-')) THEN '+' ELSE
            IF ((LEVEL EQ 2) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '+')) THEN '+' ELSE
            IF ((LEVEL EQ 2) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '-')) THEN 'X' ELSE
            IF ((LEVEL EQ 3) AND (&LEVEL EQ 1) AND ('&FLAG' EQ '-')) THEN 'X' ELSE
            IF ((CAR EQ '&CAR') AND (LEVEL EQ 2) AND (&LEVEL EQ 2) AND ('&FLAG' EQ '+')) THEN '-' ELSE
            IF ((CAR EQ '&CAR') AND (LEVEL EQ 2) AND (&LEVEL EQ 2) AND ('&FLAG' EQ '-')) THEN '+' ELSE
            IF ((CAR EQ '&CAR') AND (LEVEL EQ 3) AND (&LEVEL EQ 2) AND ('&FLAG' EQ '+')) THEN '+' ELSE
            IF ((CAR EQ '&CAR') AND (LEVEL EQ 3) AND (&LEVEL EQ 2) AND ('&FLAG' EQ '-')) THEN 'X' ELSE FLAG;
END
-*
TABLE FILE FOCCACHE/CARHOLD
 SUM SEATS SALES/D9 RETAIL_COST DEALER_COST
 BY DUMMY
 BY COUNTRY
 BY CAR
 BY MODEL
 BY LEVEL
 BY FLAGX AS 'FLAG'
 ON TABLE HOLD AS FOCCACHE/CARHOLD FORMAT ALPHA
END
-*
-SKIP_SAVE
-*
TABLE FILE FOCCACHE/CARHOLD
 SUM MAX.LEVEL
 WHERE (FLAG NE 'X');
 ON TABLE SAVE AS MAXLEVEL
END
-RUN
-READ MAXLEVEL, &MAXLEVEL
-*
TABLE FILE FOCCACHE/CARHOLD
 BY DUMMY   NOPRINT
 BY COUNTRY NOPRINT
 BY DUMMY   NOPRINT
 BY CAR     NOPRINT
 BY MODEL   NOPRINT
 WHERE (FLAG NE 'X');
 ON DUMMY SUBHEAD
  "Country<+0>Seats<+0>Dealer Cost<+0>Retail Cost<+0>Sales"
  WHEN &MAXLEVEL EQ 1 ;
 ON DUMMY SUBHEAD
  "Country / Car<+0>Seats<+0>Dealer Cost<+0>Retail Cost<+0>Sales"
  WHEN &MAXLEVEL EQ 2 ;
 ON DUMMY SUBHEAD
  "Country / Car / Model<+0>Seats<+0>Dealer Cost<+0>Retail Cost<+0>Sales"
  WHEN &MAXLEVEL EQ 3 ;
 ON MODEL SUBFOOT
  "<FLAG<COUNTRY<SEATS<DEALER_COST<RETAIL_COST<SALES"
  WHEN LEVEL EQ 1 ;
 ON MODEL SUBFOOT
  " <FLAG<CAR<SEATS<DEALER_COST<RETAIL_COST<SALES"
  WHEN LEVEL EQ 2 ;
 ON MODEL SUBFOOT
  " <+0> <+0> <MODEL<SEATS<DEALER_COST<RETAIL_COST<SALES"
  WHEN LEVEL EQ 3 ;
 ON COUNTRY SUBFOOT
  "TOTALS<SEATS<DEALER_COST<RETAIL_COST<SALES"
  WHEN LEVEL EQ 0 ;
 ON TABLE SET PAGE NOPAGE
 ON TABLE SET HTMLCSS ON
 ON TABLE PCHOLD FORMAT HTML
-* ON TABLE PCHOLD FORMAT EXL2K
 ON TABLE SET STYLE *
  UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, FONT='ARIAL', SIZE=9, STYLE=NORMAL, HEADALIGN=BODY,
   GRID=ON, BORDER=0, $
  TYPE=SUBHEAD, LINE=1, OBJECT=TEXT, STYLE=BOLD, BACKCOLOR=SILVER, $
  TYPE=SUBHEAD, LINE=1, OBJECT=TEXT, ITEM=1, COLSPAN=4, $
  TYPE=SUBFOOT, OBJECT=FIELD, JUSTIFY=RIGHT, $
  TYPE=SUBFOOT, OBJECT=FIELD, BACKCOLOR=LIGHT BLUE, WHEN=LEVEL EQ 1, $
  TYPE=SUBFOOT, OBJECT=FIELD, BACKCOLOR=PALE GREEN, WHEN=LEVEL EQ 2, $
  TYPE=SUBFOOT, OBJECT=FIELD, BACKCOLOR=YELLOW, WHEN=LEVEL EQ 3, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=2, BACKCOLOR=LIGHT BLUE, COLSPAN=3, JUSTIFY=LEFT, WHEN=LEVEL EQ 1, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=2, BACKCOLOR=PALE GREEN, COLSPAN=2, JUSTIFY=LEFT, WHEN=LEVEL EQ 2, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=1, BACKCOLOR=YELLOW, JUSTIFY=LEFT, WHEN=LEVEL EQ 3, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=1, STYLE=-UNDERLINE, JUSTIFY=CENTER, SIZE=11, FOCEXEC=&THISEXEC( \
   FLAG=FLAG COUNTRY=COUNTRY LEVEL=LEVEL RNDM=&RNDM CARHOLD='Y'), WHEN=LEVEL EQ 1, $
  TYPE=SUBFOOT, OBJECT=FIELD, ITEM=1, STYLE=-UNDERLINE, JUSTIFY=RIGHT, SIZE=11, FOCEXEC=&THISEXEC( \
   FLAG=FLAG COUNTRY=COUNTRY CAR=CAR LEVEL=LEVEL RNDM=&RNDM CARHOLD='Y'), WHEN=LEVEL EQ 2, $
  TYPE=SUBFOOT, OBJECT=TEXT, ITEM=1, COLSPAN=4, STYLE=BOLD, BACKCOLOR=SILVER, WHEN=LEVEL EQ 0, $
  TYPE=SUBFOOT, OBJECT=FIELD, STYLE=BOLD, BACKCOLOR=SILVER, WHEN=LEVEL EQ 0, $
 ENDSTYLE
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Dan,

Nice Job!

no Javascript
no CSS
no Loops

Just pure WebFocus programming. And is there a WebFocus coder who doesn't love that?!!!!

I think your code should get bumped to the head of the line over mine for TIP OF THE YEAR.

When someone looks for "Accordian Report" this is defintely the thread to review.

David



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
David, thanks for the kind words. However, there are a few caveats. As currently coded, this method won't work properly for PDF output, although customization/parameterization of the SrtyleSheet would probably allow it to work for HTML, EXL2K, and PDF. The color scheme I used doesn't transfer well to Excel (which translates two of the colors into shades of gray), so some experimentation will be necessary to fix that problem. Finally, my code is very specific to the first three segments of the CAR file and will require substantial adaptation to make it work for another data source - or even to just add another drill-down level from the CAR file. In other words, the complexity, and fine tuning required, of this approach may be more trouble than its worth.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Ditto with David,

Awesome job.



Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Some Very Valuable information here:

"The expandable horizontal accordion just isn't useful, in my view." ~ Subjective, but rue. IB: Can't we have BOTH?

"Just pure WebFOCUS programming. And is there a WebFOCUS coder who doesn't love that?!!!! ~ I sure do!

"Hopefully, Kerry will see this and let IBI know..."

"I felt it was time to 'Give back' something of my own that might be useful. (Excellent Attitude !!!) " "


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
   Currently Available. Please contact me.
 
Posts: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Ready for some bangin' news? To anyone still following this topic, IBI has finally provided an easy, intuitive method to create a VERTICAL ACCORDIAN REPORT in HTML format. This has been a twinkle in my eye since I started using VaxFocus in 1985. I can't test this yet, as we have just upgraded to 7.6.11, but will soon move to 7.7.02. Woo Wanda!

From the New Features guide for 7.7.02:

quote:
Creating Accordion By Row Reports
Usage Notes for SET EXPANDBYROW

Accordion By Row reports are HTML reports that offer an interactive interface to data
aggregated at multiple levels by presenting the sort fields within an expandable tree. The
highest dimension or sort field (BY value) and the aggregated measures for each value
display at the top of the report. The tree control can be used to open or close each dimension and view the associated aggregated values. Clicking the plus sign (+) next to a sort field value opens new rows that display the next lower level sort field values and subtotals. Thelowest level sort field, when expanded, displays the aggregated data values.

Using the SET EXPANDBYROW command and turning HTMLCSS ON enables any single verb
SUM (WRITE, ADD) or COUNT report that has at least two BY fields to be turned into an
Accordion By Row request. EXPANDBYROW automatically invokes the SET SUBTOTALS=ABOVE
command, which moves the subtotal rows above the subheading and data rows. A SUBTOTAL
command is automatically added for the next-to-last BY field. Each level will be presented at the aggregated level, and the data values will represent the aggregation of thelowest level BY.

Pop-up field descriptions are supported for the fields within the BY tree and will present the
field descriptions maintained within the Master File or defined associated with the fields.

Styling an Accordian By Row report can be done using standard HTML report techniques,but it is important to keep the report structure in mind. All rows except the lowest level are actually SUBTOTAL rows and the lowest level contains the report DATA.

Accordion reports created with the SET EXPANDABLE command open by column, not row.

Syntax: How to Create Accordion Reports That Expand by Row
SET EXPANDBYROW = {OFF|ON|n}
ON TABLE SET EXPANDBYROW {OFF|ON|n}

where:

OFF
Does not create an accordion report. OFF is the default value.

ON
Creates an accordion report which initially displays only the highest sort field level. To
see rows on lower levels, click the plus sign (+) next to one of the displayed sort field
values.

ALL
Creates an accordion report in which all sort field levels are initially expanded. To roll
up a sort field level, click the minus sign (-) next to one of the sort field values on that
level.

n
Creates an accordion report in which n sort field levels are initially expanded. To roll up
an expanded sort field level, click the minus sign (-) next to one of the sort field values
on that level.

Note: Accordion By Row reports require that the HTMLCSS parameter be set to ON and that the request have at least two BY fields.


Example report code:
SET EXPANDBYROW=ON
TABLE FILE GGSALES
SUM BUDUNITS UNITS BUDDOLLARS DOLLARS
BY REGION
BY ST
BY CATEGORY
BY PRODUCT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
GRID=OFF,FONT=ARIAL,$
END  



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Summary Accordian Report

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.