Hey guys, have any of you ever done a report with a report inside it? I have a requirement (not yet firm) that reads like this --

Show a list of a client's eligibility periods (a year of time each) and underneath each of those periods show a listing of their salary history for the period.

So I have a listing of time periods, and within each line of that listing I want to have a listing of salaries for the client. I actually have a need for a listing of salaries, listing of addresses, listing of bills, etc., but you get the idea.

And accordion the report. But I'll handle that part! Has anyone created reports of reports, and if you can you give me a primer?

It's tougher than it looks. I brought this question to the ultimate keeper of all FOCUS knowledge, Pat Crossland-Smith, and her reply was, "Uh . . . huh." Then she mumbled something about speaking to the Illuminati and walked slowly away with a dazed look on her face. A simple join produces a listing that puts the two side-by-side but since I need to have more than one subreport I don't think that's a viable solution.

Any help you can provide . . .

Might FML give me this ability? I don't think it handles multiple rows of data though.

J.

quote:

... a list of a client's eligibility periods (a year of time each)
and underneath each of those periods show
a listing of their salary [etc.] history for the period.

What's the issue? How does that differs from

print item-information
 by client
  by elig_period
   by item_key(s)

Granted, "item-information" may take some doing to format non-homgeneous data -- but that's a seperate issue.

(Send my regards to Pat.)

-Jack Gross

Two reasons.

I'm displaying a lot of columns -- about six each from five or six different tables. I'd like each tables data to appear starting at the left-hand side of the page again (or close to it) and have them stacked. I think the format is html, but it may be pdf to get fold-line into the picture. It seems like my only hope so far.

The BY field becomes particularly problematic because the joins will not be hierarchical. I will have one parent and five or so children, so the BY fields will not fall into an inline order. FOCUS (and every RDBMS) will balk the request.

I very likely need the pdf either way for printing, so an html solution may not be required.

Do you mean a grid, with a data table in the cells of one of the columns?

Hmmm ...

In html output, you could generate code for an iframe for each cell in that column, and set the onload to run a drilldown type WF request, with appropriate parameter values, targeted to itself.

Hey -- I tried loading a an html table into the cell on the original call (failed!) but I had not considered a recursive select. "Gump! That answer is OUTSTANDING! I'd recommend you to OCS if you weren't such a FINE example of the enlisted man!"

Unless it doesn't work. I may be able to just return it as a table without the iframe, if I can get it to execute. Huh. I shall play with that this evening and let you know.

Thank you!

J.

On the PDF side -- A standard FOCUS method to interleave parent lines (with lots of dependent columns) and child lines:

define file ...
NADA/A1=' ';
end

table file ...
write parent_fields
by parent_key
write child_fields
by parent_key
BY NADA AS '' FOLD-LINE
by child_key
end

Nada "prints" at the end of the parent line, but its title and value are blank. So you get all the parent-level dependent columns on the parent line (between parent_key and the invisible NADA), and all the child-level columns on the indented lines, after child-key

Another technique that we is is just to incorporate all of the values that would be displayed for each section in a subhead. If you play around with the placement via spot markers, justification, stylesheets, etc., you can get it to look pretty much like its own report, but it's really only a subheading. Then just getting your BYs in the right order gets you what you need.
FOLD-LINE and OVER are also useful for something like this.

quote:

Originally posted by John_Edwards:
Any help you can provide . . .

if it was simple html i would set up a bunch of variables common to all reports (most likely Employee ID)

and then do this (in psudeocode)

 
-SET &EMPLOYEE_ID = '1234';

TABLE FILE ADDRESS
PRINT *
WHERE EMPLOYEE ID EQ '&EMPLOYEE_ID'
ON TABLE SET STYLE *
[STYLING CODE]
END

TABLE FILE SALARY
PRINT *
WHERE EMPLOYEE ID EQ '&EMPLOYEE_ID'
ON TABLE SET STYLE *
[STYLING CODE]
END


TABLE FILE TRAINING_HISTORY
PRINT *
WHERE EMPLOYEE ID EQ '&EMPLOYEE_ID'
ON TABLE SET STYLE *
[STYLING CODE]
END

 

By stacking your reports in your fex (and ensuring you do styling for every one) they will all come out one after another, and by using a field common to all reports im able to filter every report by the same value ensuring you get a 'compound' or linked report.


that gives you a basic start point...

I'll send a slice of delicious pie to you if you can describe how to make those interleave properly when there are multiple lines for the parent report and multiple lines for each of the children. That's the tough part -- how do you get the subreport to run once for each line in the main report?

I think the idea of a focus call embedded into each line of the parent report is a pretty nifty idea. I haven't had the chance to give it a run yet.

J.

When I worked for IBI in Australia, someone created a JS that would allow you to turn on or off columns and lines, etc.

The basic idea that matches here was to have a subhead, and click somewhere and the detail lines would show. Don't know if I have a copy though.

quote:

Originally posted by Waz:
When I worked for IBI in Australia, someone created a JS that would allow you to turn on or off columns and lines, etc.

The basic idea that matches here was to have a subhead, and click somewhere and the detail lines would show. Don't know if I have a copy though.

Waz, here is something i created a while ago as a report that calls itself, i think ive stripped out all code unique to our site:

 SET BYDISPLAY =OFF


DEFINE FILE SYSCOLUM
PREF/A3 = GETTOK(NAME, 3,1,'_',3,PREF);
FIELDTYPE/A10 = DECODE COLTYPE(CHAR 'A' DOUBLE 'D' INTEGER 'I' DATE 'DATE' PACKED 'P');
FFORMAT/A100 = FIELDTYPE||LENGTH;
END

TABLE FILE SYSCOLUM
BY TBNAME
-IF &TBNAME EQ 'FOC_NONE'  THEN SKIPTO;
BY NAME
-IF &TBNAME NE 'FOC_NONE' AND &DRILL  EQ 0  THEN SKIPTO1;
BY COLTYPE
BY FIELDTYPE
BY LENGTH
-SKIPTO
-SKIPTO1
WHERE TBNAME EQ '&TBNAME'
ON TABLE SET STYLE *

COLUMN = TBNAME, FOCEXEC = SysColums(TBNAME = TBNAME DRILL = 0),$
-IF &TBNAME EQ 'FOC_NONE'  THEN MOVETO;
-IF &TBNAME EQ 'FOC_NONE' AND &DRILL  EQ 1 THEN JUMPTO;
COLUMN = NAME, FOCEXEC = SysColums(TBNAME = TBNAME DRILL=1),$
COLUMN = TBNAME, FOCEXEC = SysColums(TBNAME = 'FOC_NONE' DRILL = 1),$
-MOVETO
-JUMPTO
END
-RUN

DEFINE FILE SYSCOLUM
PREF/A3 = GETTOK(NAME, 3,1,'_',3,PREF);
END
-RUN

TABLE FILE SYSCOLUM
BY TBNAME
BY PREF
BY NAME
WHERE TBNAME EQ '&TBNAME'
END
 

John, i don't know what you mean by interleave though as ive only stacked reports in this way and had one follow the other- i guess if you wanted it to do a record at a time you could do a page break on the BY field of the first report and see if that cascades down to the others im not sure it will though

John, does this do what you want ?

It uses HTML+JS+CSS

DEFINE  FILE CAR
 Key/A26         = COUNTRY | CAR ;
 Id/A26          = CTRAN(26,Key,32,95,Id) ;
 ShowDetail/A100 = '<input type=checkbox onclick="funShowHide(this.checked,''' | Id | ''')">' ;
 Control/A60     = '<input type=hidden name="' | Id | '">' ;
END

TABLE   FILE CAR
 PRINT  
        MODEL
        BODYTYPE
        SEATS
        DEALER_COST
        RETAIL_COST
        SALES
        Control      AS ''

 BY     COUNTRY   NOPRINT
 BY     CAR       NOPRINT

 ON     CAR SUBHEAD
        " "
        "<COUNTRY> <CAR>"
        "Show Detail <ShowDetail "
 ON     TABLE HOLD AS CTRL_RPT FORMAT HTMTABLE
 ON     TABLE SET HTMLCSS ON
 ON     TABLE SET STYLE *
TYPE=SUBHEAD, SIZE=12, $
TYPE=DATA, CLASS=cssHide, $
END

-RUN

-HTMLFORM BEGIN
<html>
<head>
<STYLE>
.cssHide {
 display: none ;
}
.cssShow {
 display: inline ;
}
</STYLE>
<script language="JavaScript">
 function funShowHide(_State,_Key) {
  var _Lines = document.getElementsByName(_Key) ;
  for (_c1=0;_c1<_Lines.length;_c1++) {
    var _TD = _Lines[_c1].parentElement ;
    var _TR = _TD.parentElement ;
    for (_c2=0;_c2<_TR.childNodes.length - 1 ; _c2++) {
      _TR.childNodes[_c2].className=_State?'cssShow':'cssHide';
    }
  }
 }
</script>
</head>
!IBI.FIL.CTRL_RPT;
</html>
-HTMLFORM END
-RUN

You know, I like that. Not exactly what I'm looking for, but it's in the neighborhood. I'd need to be able to get everything into the subhead and subfoot, and I may have to nest three levels deep. But the concept is there.

I came up with this on the train this morning --

-SET &ECHO=ALL;
-* File subreports.fex
DEFINE FILE GGPRODS
SUBREPORT/A255='<iframe src=''/ibi_apps/WFServlet?IBIF_webapp=/ibi_apps&' |
               'IBIC_server=EDASERVE&' |
               'IBIWF_msgviewer=OFF&' |
               'IBIAPP_app=madap&' |
               'IBIF_ex=insidereport&' |
               'CLICKED_ON=&' |
               ''' id=''<PRODUCT_ID'' name=''expando'' align=''right'' width=''90%'' height=''200px'' frameborder=''0'' ></iframe>';
END
TABLE FILE GGPRODS
PRINT
     'GGPRODS.PRODS01.PRODUCT_DESCRIPTION'
     'GGPRODS.PRODS01.VENDOR_CODE'
     'GGPRODS.PRODS01.VENDOR_NAME'
     'GGPRODS.PRODS01.PACKAGE_TYPE'
     'GGPRODS.PRODS01.SIZE'
     'GGPRODS.PRODS01.UNIT_PRICE'
BY 'GGPRODS.PRODS01.PRODUCT_ID'

ON GGPRODS.PRODS01.PRODUCT_ID SUBFOOT
"<SUBREPORT "
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS SUBBER FORMAT HTMTABLE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     RIGHTGAP=0.125000,
$
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,
$
ENDSTYLE
END
-HTMLFORM BEGIN
<html>


<body>
!IBI.FIL.SUBBER;
</body>
</html>
-HTMLFORM END

The define at the top creates an iframe with the contents of another report like Jack recommended above. It can go as many levels deep as your server will tolerate.

I have a bit of a concern that I think each of these will need to run in PDF as well, so that could be a problem. But both are approaching the correct solution. Very happy to see smart people chiming in on this -- I may try to work up a doc on the solutions I come up with.

J.

so, John
how about going to the 1st sticky post and
NOMINATE this technique for one of the TOP 10 T&T for SUMMIT this year!!

btw, i can't get your fex to work.
the subhead string doesnt recognize and i assume you're passing &id to the child fex? you need to nuke the space before the ' id'
but even that, i can't make it work.
Can you post a working example, when you get around to it.

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

This one may give you the output in PDF format as you want but not the pretty one.

-SET &ECHO=ALL;


FILEDEF TEMPFILE DISK C:\KAMESH\TEMPFILE.DAT
-RUN

DEFINE FILE GGPRODS
FILLER1/A1=HEXBYT(160,'A1');
END


-* File subreports.fex
TABLE FILE GGPRODS
PRINT
FILLER1
PRODUCT_DESCRIPTION
FILLER1
VENDOR_CODE
BY PRODUCT_ID
ON TABLE SAVE AS TEMPFILE
END
-RUN

TABLE FILE GGPRODS
BY PRODUCT_ID
ON TABLE SAVE AS PRODID
END
-RUN



-SET &TT='';

-NEXTVAL1
-READ PRODID &PID.A4
-IF &IORETURN EQ 1 THEN GOTO ENDREAD1 ELSE GOTO CONTREAD1;
-CONTREAD1

TABLE FILE GGPRODS
PRINT
PRODUCT_ID
PRODUCT_DESCRIPTION
VENDOR_CODE
VENDOR_NAME

WHERE PRODUCT_ID EQ '&PID'

-SET &TT1='';

ON TABLE SUBFOOT

" &TT "
-NEXTVAL
-READ TEMPFILE &TT.A26.
-IF &IORETURN EQ 1 THEN GOTO ENDREAD ELSE GOTO CONTREAD;
-CONTREAD
-SET &TT1=SUBSTR(26,&TT,1,4,4,'A4');
-*EDIT(&TT,'9999$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
-TYPE TT1 &TT1
-IF '&PID.EVAL' EQ '&TT1.EVAL' THEN GOTO SHOWVAL ELSE GOTO ENDREAD;
-SHOWVAL
" &TT "
-GOTO NEXTVAL;
-ENDREAD

HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF OPEN
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
RIGHTGAP=0.125000,
$
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,
$
ENDSTYLE
END
-GOTO NEXTVAL1;
-ENDREAD1

TABLE FILE GGPRODS
PRINT PRODUCT_ID NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE PCHOLD FORMAT PDF CLOSE
ON TABLE SET EMPTYREPORT ON
END
-EXIT

quote:

how about going to the 1st sticky post and
NOMINATE this technique for one of the TOP 10 T&T for SUMMIT this year!!

I posted an almost identical method back in March 2006 (see this post) but had forgotten about it until I saw John's submission - which looked very familiar!

Sorry

T

For PDF, you might want to check out coordinated reporting with MERGE=ON.

T

T, that post you link to is all screwed up, HTML-ly speaking...can you edit with some code tags to see if you can make it appear clearly?

Susannah,

I can only edit my posts and the errorneous code is not within that (I had already "de-mabelled" it!). Looking at the source of the page I can see that JG's following post has been "Mabelled" (bless her). JG might be able to edit his post? JG ......

Mabel seems to have been very active in that topic!

T