Hello,

We have a unique report requirement that I have not been able to solve up to this point. We are doing a report that displays active and issued items. The columns are aptly named: ACTIVE and ISSUED. We would like for the report to display in one column ACTIVE / ISSUED. We have tried many solutions, and none match up to what we need.

The closest we've come to being able to do this is to use PTOA, and concatenate the two fields. When using this technique, we cannot sum the data on the subtotal/grandtotal lines. We can summarize the data in a RECAP field, but that displays on an undesired new line.

Desired Output:

Product Type Active/Issued Production
Test1 T1 3/8 200
Test2 T1 4/5 400
Subtotal T1 7/13 600
Test3 T2 2/3 100
Test4 T2 2/2 200
Test5 T2 1/10 300
Subtotal T2 5/15 600
Test6 T3 8/10 900
Subtotal T3 8/10 900
Total 20/38 2100



Any thoughts on the subject would be greatly appreciated.


Product Info:
Developer Studio 7.6.5
Microsoft SQL Server 2005
Output Formats: PDF, HTML, Excel

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

Did you try using SUBFOOT? You can play with different techniques. ST. TOT. in a Subfoot.

We had a couple of issues when using subfoot. First, we can't get all the data to line up properly underneath one column. Second, we can't draw heavy borders around the column in a subfoot.

Well, the easiest method would be to make a decision. If you want to display the numbers as an alpha string with a slash then you lose the ability to subtotal numbers. OR you can display them as numbers in separate columns and get subtotals. I would opt for the latter. There are numerous ways you could display them as separate numeric columns but make them APPEAR as if they are in the same column with an alpha column containing a '/' in between.
Problem is you want to display alpha but have numeric subtotals. The ONLY way to do that is with SUBFOOT. Your alignment issues could probalby be resolved using POSITION= and JUSTIFY= in the stylesheet. There shouldn't be any issues with drawing borders - would have to see your code - but there are several ways around that as well.

We have decided to use two numeric columns and sum them, and display the '/' as a column of it's own in between them.

The response we received from IBI support was that Borders cannot be drawn in PDF in SubFoots. It is an enhancement coming in a later release.

I'm very interested...what other options are there for formatting a SubFoot?

Code for the current report is:

TABLE FILE RPTPERSISTENCYREPORTBYIMOADHOC
SUM
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ISSUED' AS 'Issued'
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ACTIVE3MONTH' NOPRINT
AS '3Month,Active'
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ISSUED3MONTH' NOPRINT
AS 'Issued'
COMPUTE RatioThree/D20.2% = ( ACTIVE3MONTH / ISSUED3MONTH ) * 100; NOPRINT
AS 'Persist,Ratio'
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ACTIVE6MONTH'
AS '6Month,Active'
Slash AS ''
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ISSUED6MONTH' AS 'Issued'
COMPUTE RatioSix/D20.2% = ( ACTIVE6MONTH / ISSUED6MONTH ) * 100;
AS 'Persist,Ratio'
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ACTIVE14MONTH'
AS '14Month,Active'
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ISSUED14MONTH' AS 'Issued'
COMPUTE RatioFourteen/D20.2% = ( ACTIVE14MONTH / ISSUED14MONTH ) * 100;
AS 'Persist,Ratio'
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ACTIVE26MONTH'
AS '26Month,Active'
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ISSUED26MONTH' AS 'Issued'
COMPUTE RatioTwentysix/D20.2% = ( ACTIVE26MONTH / ISSUED26MONTH ) * 100;
AS 'Persist,Ratio'
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ACTIVE38MONTH'
AS '38Month,Active'
'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.ISSUED38MONTH' AS 'Issued'
COMPUTE RatioThirtyeight/D20.2% = ( ACTIVE38MONTH / ISSUED38MONTH ) * 100;
AS 'Persist,Ratio'
BY 'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.IMO' NOPRINT
BY 'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.MARKET' NOPRINT
BY 'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.AGENTNAME' AS 'Agent,Name'
BY 'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.AGENTNUMBER' AS 'Agent,Number'
BY 'RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.STATUS' AS 'Stat'

ON RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.IMO RECOMPUTE AS 'Grand Total'

ON RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.MARKET SUBHEAD
"ON RPTPERSISTENCYREPORTBYIMOADHOC.ANSWERSET1.MARKET RECOMPUTE AS 'Sub Total'
HEADING
"Americo Persistency Report"
"By <+0>&IMO"
"From: <+0>&BEGINDATE<+0> To: <+0>&ENDDATE"
FOOTING BOTTOM
"WHERE @IMO EQ '&IMO.(FIND IMO,IMO IN imodimension).Please select an IMO.' AND @BEGINDATE EQ DT(&BEGINDATE) AND @ENDDATE EQ DT(&ENDDATE);
WHERE RECORDLIMIT EQ 50
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE SET FORMULTIPLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-IF &WFFMT EQ 'IBI_DEFAULT' GOTO IBI_DEFAULT ELSE IF &WFFMT EQ 'PDF' GOTO IBI_PDF ELSE IF &WFFMT EQ 'HTML' GOTO IBI_DEFAULT ELSE IF &WFFMT EQ 'AHTML' GOTO IBI_DEFAULT ELSE IF &WFFMT EQ 'PPT' GOTO IBI_DEFAULT ELSE IF &WFFMT EQ EXL2K GOTO IBI_DEFAULT ELSE IF &WFFMT EQ 'EXL2K FORMULA' GOTO IBI_DEFAULT ELSE IF &WFFMT EQ 'EXL2K PIVOT' GOTO IBI_DEFAULT;
-IBI_DEFAULT
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=ON,
FONT='ARIAL',
SIZE=10,
COLOR='BLACK',
STYLE=NORMAL,
$
TYPE=DATA,
SIZE=8,
$
TYPE=DATA,
COLUMN=N5,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLOR='WHITE',
BACKCOLOR='NAVY',
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
STYLE=BOLD,
POSITION=2.180556,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
STYLE=BOLD,
POSITION=2.180556,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=2,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
STYLE=BOLD,
POSITION=2.180556,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=2,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=3,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=4,
STYLE=BOLD,
$
TYPE=HEADING,
IMAGE=New americologo_289.jpg,
POSITION=(+0.000000 +0.000000),
SIZE=(2.000000 0.500000),
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=8,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
SIZE=8,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
SIZE=8,
$
TYPE=FOOTING,
LINE=1,
OBJECT=FIELD,
ITEM=1,
SIZE=8,
$
TYPE=FOOTING,
LINE=1,
OBJECT=FIELD,
ITEM=2,
SIZE=8,
$
TYPE=SUBHEAD,
BY=2,
LINE=1,
OBJECT=FIELD,
ITEM=1,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=1,
SIZE=8,
BACKCOLOR='SILVER',
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=2,
SIZE=8,
BACKCOLOR='SILVER',
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=0.180556,
$
TYPE=REPORT,
COLUMN=N22,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N21,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N20,
WRAP=0.555556,
$
TYPE=REPORT,
COLUMN=N19,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N18,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N17,
WRAP=0.555556,
$
TYPE=REPORT,
COLUMN=N16,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N15,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N14,
WRAP=0.555556,
$
TYPE=REPORT,
COLUMN=N13,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N12,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N10,
WRAP=0.555556,
$
TYPE=REPORT,
COLUMN=N9,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N8,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N7,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N6,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=2.500000,
$
TYPE=REPORT,
COLUMN=N2,
WRAP=0.180556,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=0.180556,
$
-GOTO IBI_ENDSTYLE
-IBI_PDF
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.152778,
RIGHTMARGIN=0.152778,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
FONT='ARIAL',
SIZE=10,
COLOR='BLACK',
STYLE=NORMAL,
RIGHTGAP=0.013889,
LEFTGAP=0.013889,
$
TYPE=DATA,
SIZE=8,
$
TYPE=DATA,
COLUMN=N11,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N12,
BORDER-LEFT=OFF,
JUSTIFY=LEFT,
$
TYPE=DATA,
COLUMN=N15,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N18,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N21,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N19,
BORDER-LEFT=HEAVY,
$
TYPE=DATA,
COLUMN=N16,
BORDER-LEFT=HEAVY,
$
TYPE=DATA,
COLUMN=N13,
BORDER-LEFT=HEAVY,
$
TYPE=DATA,
COLUMN=N10,
BORDER-LEFT=HEAVY,
BORDER-RIGHT=OFF,
$
TYPE=DATA,
COLUMN=N5,
JUSTIFY=CENTER,
$
TYPE=TITLE,
BORDER-TOP-COLOR='WHITE',
BORDER-BOTTOM-COLOR='WHITE',
BORDER-LEFT-COLOR='WHITE',
BORDER-RIGHT-COLOR='WHITE',
COLOR='WHITE',
BACKCOLOR='NAVY',
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N12,
BORDER-LEFT=OFF,
$
TYPE=TITLE,
COLUMN=N19,
BORDER-LEFT=HEAVY,
$
TYPE=TITLE,
COLUMN=N16,
BORDER-LEFT=HEAVY,
$
TYPE=TITLE,
COLUMN=N13,
BORDER-LEFT=HEAVY,
$
TYPE=TITLE,
COLUMN=N10,
BORDER-LEFT=HEAVY,
BORDER-RIGHT=OFF,
$
TYPE=HEADING,
BORDER-TOP=OFF,
BORDER-BOTTOM=OFF,
BORDER-LEFT=OFF,
BORDER-RIGHT=OFF,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
STYLE=BOLD,
POSITION=2.180556,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
STYLE=BOLD,
POSITION=2.180556,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=2,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
STYLE=BOLD,
POSITION=2.180556,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=2,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=3,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=4,
STYLE=BOLD,
$
TYPE=HEADING,
IMAGE=New americologo_289.jpg,
POSITION=(+0.000000 +0.000000),
SIZE=(2.000000 0.500000),
$
TYPE=FOOTING,
BORDER-TOP=OFF,
BORDER-BOTTOM=OFF,
BORDER-LEFT=OFF,
BORDER-RIGHT=OFF,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=8,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
SIZE=8,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
SIZE=8,
$
TYPE=FOOTING,
LINE=1,
OBJECT=FIELD,
ITEM=1,
SIZE=8,
$
TYPE=FOOTING,
LINE=1,
OBJECT=FIELD,
ITEM=2,
SIZE=8,
$
TYPE=SUBHEAD,
BY=1,
LINE=1,
OBJECT=FIELD,
ITEM=1,
STYLE=BOLD,
$
TYPE=SUBHEAD,
BY=2,
LINE=1,
OBJECT=FIELD,
ITEM=1,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=1,
SIZE=8,
BACKCOLOR='SILVER',
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BY=2,
SIZE=8,
BACKCOLOR='SILVER',
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N11,
SQUEEZE=0.305556,
$
TYPE=REPORT,
COLUMN=N12,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N15,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N18,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N21,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N19,
WRAP=0.555556,
$
TYPE=REPORT,
COLUMN=N16,
WRAP=0.555556,
$
TYPE=REPORT,
COLUMN=N13,
WRAP=0.555556,
$
TYPE=REPORT,
COLUMN=N10,
WRAP=0.555556,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=0.180556,
$
TYPE=REPORT,
COLUMN=N20,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N17,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N14,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N9,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N8,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N7,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N6,
WRAP=0.500000,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=2.500000,
$
TYPE=REPORT,
COLUMN=N2,
WRAP=0.180556,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=0.180556,
$
-GOTO IBI_ENDSTYLE
-IBI_ENDSTYLE
ENDSTYLE
END

7.6.5 Windows

Americo,

  
-* File americo1.fex
TABLE FILE CAR
PRINT RCOST NOPRINT DCOST NOPRINT 
COMPUTE RD/A13=FTOA(RCOST, '(D6c)', 'A6') | '/' | LJUST(6, FTOA(DCOST, '(D6c)', 'A6'), 'A6');
BY COUNTRY
ON COUNTRY RECAP
RDT/A13=FTOA(RCOST, '(D6c)', 'A6') | '/' | LJUST(6, FTOA(DCOST, '(D6c)', 'A6'), 'A6');
ON COUNTRY SUBFOOT
"<COUNTRY<RDT"
" <+0 "
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN = BODY, 
$
ENDSTYLE
END

A possibility...

Hi Danny,

That was our last attempt, did not quite meet the needs.

The problem becomes the SUBHEAD/SUBFOOT. We need to be able to style the borders of the subhead and subfoots of this report. Columns 5-6 are a group, the left side of column 5 has a heavy dark border, and the right side of column 6 has a heavy dark border (or any kind of border for that matter).

Darin mentioned there was a way around the borders issue, i would love to see it. The only border I can see that we can draw is around the entire subhead - not individual columns within the subhead.

This is a PDF document.

That is correct. Borders can be drawn around the entire subfoot, but not around specific items within the subfoot. If the subfoot always appears at the same position on the page, you could draw a box around it. Your options would be limited based on your specific requirement. However, since you have decided upon using separate columns, you can format them individually as needed.

I think if you need to put borders around the totals, you should use a SUBTOTAL, you can style the columns.

It may be better to create your own totals, that way they are just data and can be styled to any degree.

Americo,

If there is a border (or grid) problem and so, on the one hand SUBFOOT is not a solution, and on the other hand SUBTOTAL doesn't provide for totalling alph fields, then you have to create your total fields as if they were in the database. Here is one possible solution.

  
-* File americo2.fex
SET HOLDLIST=PRINTONLY
-* Create the "totals" field
DEFINE FILE CAR
SRT/I1=2;
END
TABLE FILE CAR
SUM RCOST NOPRINT DCOST NOPRINT
COMPUTE RD/A13=FTOA(RCOST, '(D6c)', 'A6') | '/' | LJUST(6, FTOA(DCOST, '(D6c)', 'A6'), 'A6');
BY COUNTRY
BY SRT
ON TABLE HOLD AS TOT FORMAT ALPHA
END
-*
FILEDEF TOT DISK TOT.FTM (APPEND
-*
-* Create the individual fields and merge them into the first HOLD
DEFINE FILE CAR
SRT/I1=1;
END
TABLE FILE CAR
PRINT RCOST NOPRINT DCOST NOPRINT
COMPUTE RD/A13=FTOA(RCOST, '(D6c)', 'A6') | '/' | LJUST(6, FTOA(DCOST, '(D6c)', 'A6'), 'A6');
BY COUNTRY
BY SRT
ON TABLE SAVE AS TOT
END
-*
-* Report from the HOLD file which now contains both individual and totals
DEFINE FILE TOT
XCOUNTRY/A10=IF SRT EQ 1 THEN COUNTRY ELSE 'Total';
END
TABLE FILE TOT
PRINT RD AS 'RCOST/DCOST'
BY COUNTRY NOPRINT
BY SRT NOPRINT
BY XCOUNTRY AS COUNTRY
ON COUNTRY SUBFOOT
" "
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
GRID=OFF, BORDER=LIGHT,
$
ENDSTYLE
END