Drill down on an individual field

December 01, 2008, 11:52 AM

I have a report that has a very large notes field on it. What I would like to do is to have a + (or something) display on the report if notes exist for that row. If you click on the + sign, the notes will display in a separate box. Can this be done in Webfocus? Thanks

December 01, 2008, 12:32 PM

Darin Lee

At the very simplest, just create a drilldown procedure to display a note using a parameter value to identify the desired note/reference. Then link it to the column that contains the '+' character in the original report, passing the note/reference value as the parm value.

There are probably MANY other ways to do this, but would involve other pieces such as Javascript. This solution would only involve standard WF techniques.

Another idea would be to place the note in a subfoot and use conditional styling to only display the subfoot ne blank. You have a LOT of choices.

December 01, 2008, 12:48 PM

Francis Mariani

I am not too familiar with v7.6, but you should take a look at "Creating an HTML Active Report" in the "Creating Reports With WebFOCUS Language".

Though, I don't think Active Reports has the capability you're looking for.

If you're looking for an HTML solution here's an unconventional WebFOCUS program that was a proof of concept I developed a while ago that does what you're looking for. It tricks WebFOCUS into creating an extra table row per report row with a toggle to hide/show the extra row.

-*-- Hide and Show DIVS to demonstrate displaying extra data for a cell on a report
-SET &ECHO=ALL;

DEFINE FILE CAR
NOTE/A50 =
  IF COUNTRY IN ('ENGLAND', 'ITALY', 'W GERMANY') THEN 'This is a note for Group 1' ELSE
  IF COUNTRY EQ 'JAPAN' THEN 'This is a note for Group 2' ELSE '';
END
-RUN

TABLE FILE CAR
PRINT
COUNTRY
NOTE
CAR
MODEL
DEALER_COST
RETAIL_COST
SALES
COMPUTE COUNTER/I4 = COUNTER + 1;

BY COUNTRY                  NOPRINT
BY CAR                      NOPRINT
BY MODEL                    NOPRINT

ON TABLE HOLD FORMAT ALPHA
END
-RUN

DEFINE FILE HOLD
LINK_OPEN/A125 = IF NOTE EQ '' THEN '' ELSE
  '<img id="idI' || EDIT(COUNTER) ||
  '" src="/approot/test/images/1.gif" width="9" height="9"' ||
  ' onClick="fnToggle(''' || EDIT(COUNTER) || ''')">';

NOTE_EXTRA/A999 = IF NOTE EQ '' THEN '' ELSE
  '</TD>' ||
  '<tr><td></td><td colspan=5 class="cssDetail">' ||
  '<div id="idC' || EDIT(COUNTER) || '" style="display: ''none'';">' ||
  NOTE ||
  '</div>' ||
  '</td></tr>'
;
END
-RUN

TABLE FILE HOLD
  PRINT
    LINK_OPEN       AS ''
    COUNTRY         AS 'Country'
    CAR             AS 'Car'
    MODEL           AS 'Model'
    DEALER_COST     AS 'Dealer,Cost'
    RETAIL_COST     AS 'Retail,Cost'
    SALES           AS 'Sales'
    NOTE_EXTRA      AS ''
  BY COUNTRY        NOPRINT
  BY CAR            NOPRINT
  BY MODEL          NOPRINT

  ON COUNTRY SUBHEAD
" "

HEADING CENTER
"Car Sales"
" "

ON TABLE HOLD AS HOLD01 FORMAT HTMTABLE
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT,  GRID=OFF, SQUEEZE=ON, $

TYPE=HEADING, CLASS=cssHeading, $

TYPE=TITLE,   CLASS=cssTitle, $
TYPE=TITLE,   COLUMN=P1, CLASS=cssBlank, $
TYPE=TITLE,   COLUMN=P8, CLASS=cssBlank, $

TYPE=DATA,    CLASS=cssData, $
TYPE=DATA,    COLUMN=P1, CLASS=cssBlank, $
TYPE=DATA,    COLUMN=P8, CLASS=cssBlank, $
ENDSTYLE
END
-RUN
-IF &FOCERRNUM NE 0 GOTO FOC_ERROR;

-SET &ECHO=OFF;

-HTMLFORM BEGIN
<html>
<head>
<title>Car Test</title>
<style>
U {text-decoration: none;}

TABLE
  { font-family: Verdana, sans-serif; font-size: 7pt; }
.cssHeading
  { font-size: 9pt; font-weight: bold; }
.cssTitle
  { font-weight: bold;
    background-color: RGB(200,200,200);}
.cssData
  { background-color: RGB(230,230,230);}
.cssBlank
  { background-color: none;}
.cssDetail
  { background-color: none;}
</style>

<script language="JavaScript">

function fnToggle(vCounter)
{
vDIV = eval("idC" + vCounter);
vIMG = eval("idI" + vCounter);

if (vDIV.style.display == 'none')
  {
  vDIV.style.display = '';
  vIMG.src = "/approot/test/images/0.gif";
  }
else
  {
  vDIV.style.display = 'none';
  vIMG.src = "/approot/test/images/1.gif";
  }
}
</script>
</head>

<body>
<center>

!IBI.FIL.HOLD01;

</center>
</body>
</html>
-HTMLFORM END
-EXIT

-FOCERROR
-EXIT

You will need these two images, rename then to 0.gif and 1.gif:

December 01, 2008, 01:32 PM

slfmr

Accordian reporting is maybe also an option. You can sort by certain fields and then visibly show or hide them.

DEFINE FILE CAR
FAUX/A20=IF COUNTRY EQ 'USA' THEN 'Super long note' ELSE '';
END
TABLE FILE CAR
BY COUNTRY
BY CAR
BY FAUX
WHERE MODEL NE 'V12XKE AUTO';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET EXPANDABLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END

Or something like that. Just another idea of how to work reports.

December 01, 2008, 01:46 PM

Tracie

I tried the accordian option on the notes field but it only appears to work on a sort field. Is that true? Thanks

December 02, 2008, 03:06 AM

GamP

Yes, you're right, accordion is only meant to work for sort fields.
So the other option (the drilldown) is the only other option for you. And you can make this option as smart and nice looking as you want. You could even -- very!! sophisticated -- drilldown to a maintain procedure to modify or add the notes for the indicated record.

Hope this helps...

December 02, 2008, 03:26 AM

Robert Teo

We have been using drilldown to link the data files (text or xml)that have been sent in to each customer through a report (Html or excel or pdf). It works fine, you need to point out where the text files are stored so that the link can find them.

If you have very large files then you can zip them which is great because then the web browser will ask the client if then want to save the file or unzip it.

Robert

December 02, 2008, 09:25 AM

Tracie

I created a new procedure that I want to drill down to. Its source is:

 TABLE FILE IFS_WI_NOTE_ID_TEXT
PRINT 
     'IFS_WI_NOTE_ID_TEXT.IFS_WI_NOTE_ID_TEXT.ORDER_NO'
     'IFS_WI_NOTE_ID_TEXT.IFS_WI_NOTE_ID_TEXT.NOTE_TEXT'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N2,
     WRAP=6.000000,
$
ENDSTYLE
END

This is the first time I have used a drill down. The first thing I noticed is all of the lines are underlined. Is there anyway to just have the order no underlined and that if is is clicked on the new notes procedure is run? Also I only want the notes to display on the order number clicked on so I need to pass the order number from the parent to the drill down procedure. All help is greatly appreciated.

December 02, 2008, 09:31 AM

Francis Mariani

Show us the code for the drilldown.

The drill-down code should look something like this:

TYPE=DATA, COLUMN=ORDER_NO, FOCEXEC=DD1 (ORDER_NO=ORDER_NO)

December 02, 2008, 09:40 AM

Tracie

  TYPE=DATA,
     SIZE=8,
     BACKCOLOR=( RGB(234 234 255) 'WHITE' ),
     FOCEXEC=displaynotes(orderno=N1),

Here is the code, thanks for the help.

December 02, 2008, 10:07 AM

GamP

TYPE=DATA,
SIZE=8,
COLUMN=THE_DRILLDOWN_FIELD,
BACKCOLOR=( RGB(234 234 255) 'WHITE' ),
FOCEXEC=displaynotes(orderno=N1),

That would show underlines only for the mentioned field (substitute THE_DRILLDOWN_FIELD with the actual fieldname).

December 02, 2008, 10:08 AM

Francis Mariani

TYPE=DATA,
     SIZE=8,
     BACKCOLOR=( RGB(234 234 255) 'WHITE' ),
$
TYPE=DATA,
     COLUMN=orderno,
     FOCEXEC=displaynotes(orderno=orderno),
$

I assume orderno is the Order Number column.

December 02, 2008, 11:38 AM

Darin Lee

You also need to add

WHERE ORDER_NO EQ &orderno ;

in your drilldown procedure which will take the orderno parameter passed to it from the top procedure and use it in selecting the associated note record.

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988