Drill down on an individual field

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

WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF

Posts: 54 | Registered: May 07, 2008

Darin Lee

Virtuoso

posted

Hide Post

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.

Regards,

Darin

In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted

Show Post

Francis Mariani

Expert

posted

Hide Post

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:

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

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

slfmr

Platinum Member

posted

Hide Post

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.

Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6

Posts: 178 | Registered: May 11, 2005

Ignored post by slfmr posted

Show Post

Tracie

Gold member

posted

Hide Post

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

WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF

Posts: 54 | Registered: May 07, 2008

Ignored post by Tracie posted

Show Post

GamP

Virtuoso

posted

Hide Post

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...

GamP

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

Posts: 1961 | Location: Netherlands | Registered: September 25, 2007

Ignored post by GamP posted

Show Post

Robert Teo

Silver Member

posted

Hide Post

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

A message from the darks side, there is!

Posts: 39 | Registered: August 28, 2007

Ignored post by Robert Teo posted

Show Post

Tracie

Gold member

posted

Hide Post

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.

WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF

Posts: 54 | Registered: May 07, 2008

Ignored post by Tracie posted

Show Post

Francis Mariani

Expert

posted

Hide Post

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)

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

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Tracie

Gold member

posted

Hide Post

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

Here is the code, thanks for the help.

WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF

Posts: 54 | Registered: May 07, 2008

Ignored post by Tracie posted

Show Post

GamP

Virtuoso

posted

Hide Post

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).

GamP

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

Posts: 1961 | Location: Netherlands | Registered: September 25, 2007

Ignored post by GamP posted

Show Post

Francis Mariani

Expert

posted

Hide Post

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.

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

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Darin Lee

Virtuoso

posted

Hide Post

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.

Regards,

Darin

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

Drill down on an individual field

Focal Point | Privacy Statement | Terms of Service