Transcript Dynamic Subtotals - Information Builders

McGyver for Dynamic Subtotals/Subfoots
Presented by David Smith of Brinker
International
Biography

David Smith
◦ Focus/WebFocus since 1985
◦ VMX, Opcom, Octel, Lucent, Avaya, Verizon, Brinker
◦ GL, AR, AP, Payroll, HR, Sales, Mfg, Eng, BI

Brinker International - Chilis, Maggianos, Macaroni Grill, On
the Border
◦ 7.6.11
◦ RPM (Restaurant Performance System)
◦ MRE, Report Caster, Info Assist
◦ Teradata, MS SQL
◦ HTML, PDF, EXCEL, ACTIVE, FLEX
Webinar - Mar 2011
McGyver - David Smith
2
Business Requirement

Our business group needs a purchase report that:

Lists detail values (date, extend cost, qty, etc) grouped by region,
vendor, and product type

At each change in vendor, display on one line beneath the detail the
total sales tax for all purchases from the vendor for the period
selected as free text.

Under the sales tax subtotal line, display a subtotal of the extended
cost for each product type purchased from the vendor for the period
selected as free text. The number of lines is dynamic and could range
from 1 to 100 based on the product types purchased during the period
selected.

On one line, under the product type subtotals, display the Total
Extended cost for the Vendor for all detail lines as free text.

HTML, PDF, Excel formats

Must be efficient.
Webinar - Mar 2011
McGyver - David Smith
3
Business Mockup
Purchase Report
Receipt
Date Region Vendor
Product
Type
Item
Description
QTY
Gross
Ext Cost
3-Jan-11
East
Restaurant Supply, Inc.
Lights
Lamps
20 $
2,000.00
7-Jan-11
East
Restaurant Supply, Inc.
Lights
Lamps
5 $
500.00
4-Jan-11
East
Restaurant Supply, Inc.
Cooking
Microw ave
2 $
300.00
10-Jan-11
East
Restaurant Supply, Inc.
Furnture
Stools
45 $
3,800.00
1-Jan-11
East
Restaurant Supply, Inc.
Lights
Bulbs
50 $
750.00
5-Jan-11
East
Restaurant Supply, Inc.
Lights
Bulbs
10 $
1,500.00
2-Jan-11
East
Restaurant Supply, Inc.
Furnture
Stools
50 $
4,200.00
9-Jan-11
East
Restaurant Supply, Inc.
Furnture
Chairs
25 $
4,000.00
6-Jan-11
East
Restaurant Supply, Inc.
Cooking
Toaster
1 $
30.00
8-Jan-11
East
Restaurant Supply, Inc.
Cooking
Microw ave
2 $
200.00
Total Sales Tax= $128.00
Subtotal Lights=$4,750.00
Subtotal Furniture=$12,000.00
Dynamic Subtotals
Subtotal Cooking=$530.00
Total Costs for Restaurant Supply, Inc.=$17,280.00
Webinar - Mar 2011
McGyver - David Smith
4
Problem:

Grouping dynamic ranges of
subtotals/subfoots together either at
the end of a sort break or end of the
report (or even at the top) is not
possible using standard WebFocus
syntax.
Webinar - Mar 2011
McGyver - David Smith
5
Possible Solutions




Multi-Verb ?
 No Way
Join/Match ?
 Hard coded subfoot/subtotal values. Needs to be
dynamic
Repeat/Loops w/-Includes ?
 Too many pre-holds for every sort break
 Loop at subhead to –INCLUDE hold file hit wall at
84 loops
McGyver ?
 Success!
Webinar - Mar 2011
McGyver - David Smith
6
Solution Report
McGyver Technique with Dynamic Subtotals/SubFoots
Car
Model
Sales
Cost
Country: ITALY
ALFA
2000 4 DOOR
$4,800.00 $4,915.00
ROMEO
BERLINA
ALFA
2000 GT VELOCE $12,400.00 $5,660.00
ROMEO
ALFA
2000 SPIDER
$13,000.00 $5,660.00
ROMEO
VELOCE
MASERATI DORA 2 DOOR
$.00 $25,000.00
Total Dealer Cost: $41,235.00
Subtotal Sales for ALFA ROMEO :$30,200.00
Subtotal Sales for MASERATI :$.00
Total sales for ITALY :$30,200.00
Webinar - Mar 2011
McGyver - David Smith
7
McGyver Setup
-* Set up McGyver master and data file.
-*
-* Build McGyver MFD
FILEDEF MCMAS DISK MCGYV.MAS
-RUN
-WRITE MCMAS FILENAME=mcgyv, SUFFIX=FIX
-WRITE MCMAS
SEGMENT=ONE, SEGTYPE=S0, $
-WRITE MCMAS
-WRITE MCMAS
, $
FIELDNAME=CONTROL, USAGE=A1, ACTUAL=A1, $
SEGMENT=TWO, SEGTYPE=S0, PARENT=ONE, OCCURS=VARIABLE, $
-WRITE MCMAS
FIELDNAME=CHAR1, USAGE=A1, ACTUAL=A1, $
-WRITE MCMAS
FIELDNAME=CTR, ALIAS=ORDER, USAGE=I4, ACTUAL=I4, $
-*
-* Build McGyver FTM
FILEDEF MCGYV DISK MCGYV.FTM
-RUN
-* Control=X: used as Join cross reference target field
-* CHAR1=A, CTR=1: display detail
-* CHAR1=B, CTR=2: display dynamic subtotals/subfoots
-WRITE MCGYV XAB
Webinar - Mar 2011
McGyver - David Smith
8
Join/Define/Set
JOIN CONTROL WITH BODYTYPE IN CAR TO UNIQUE CONTROL IN MCGYV AS J2
-* CTR=1 display detail, CTR=2 display subtotals/subfoots.
DEFINE FILE CAR
CONTROL
/A1 WITH BODYTYPE='X';
SUM_CAR
/A40 =IF CTR EQ 2 THEN ' '
ELSE CAR;
SUM_BY_MODEL /A30 =IF CTR EQ 2 THEN ' '
ELSE MODEL;
SUM_SALES_DTL/A15 =IF CTR EQ 2 THEN ' '
ELSE PTOA(SALES,'(P12.2CM)','A15');
SUM_DCOST_DTL/A15 =IF CTR EQ 2 THEN ' '
ELSE PTOA(DEALER_COST,'(P12.2CM)','A15');
BY_CTYCAR
/A35 =IF CTR EQ 2 THEN CAR
ELSE COUNTRY;
SF_SALES
/P12.2CM=SALES;
SF_DCOST
/P12.2CM=DEALER_COST;
END
-* WebFocus 7.6.11. Remove blank lines above subfoots/subheads.
SET DROPBLNKLINE=ON
Webinar - Mar 2011
McGyver - David Smith
9
Table
TABLE FILE CAR
SUM
SUM_CAR
AS 'Car'
SUM_BY_MODEL
AS 'Model'
SUM_SALES_DTL AS 'Sales'
SUM_DCOST_DTL AS 'Cost'
BY COUNTRY
NOPRINT
BY CTR
NOPRINT
BY BY_CTYCAR
NOPRINT
BY SUM_BY_MODEL NOPRINT
ON COUNTRY SUBHEAD
"</1 Country: <COUNTRY"
ON CTR SUBFOOT
"Total Dealer Cost: <ST.SF_DCOST"
WHEN CTR EQ 1
ON BY_CTYCAR SUBFOOT
"Subtotal Sales for <CAR :<ST.SF_SALES"
WHEN CTR EQ 2
ON CTR SUBFOOT
"Total sales for <COUNTRY :<ST.SF_SALES"
WHEN CTR EQ 2
Webinar - Mar 2011
McGyver - David Smith
10
Table continued
WHERE COUNTRY EQ 'ITALY'
HEADING
"McGyver Technique with Dynamic Subtotals/SubFoots"
ON TABLE NOTOTAL
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS OFF
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
GRID=ON,BORDER=OFF,SQUEEZE=ON,SIZE=8,$
TYPE=HEADING,JUSTIFY=CENTER,$
TYPE=TITLE,JUSTIFY=CENTER,$
TYPE=DATA,COLUMN=SUM_SALES_DTL,JUSTIFY=RIGHT,WHEN=CTR EQ 1,$
TYPE=DATA,COLUMN=SUM_DCOST_DTL,JUSTIFY=RIGHT,WHEN=CTR EQ 1,$
-* Pre 7.6.11. Only for PDF/HTML. Uncomment next line and turn HTMLCSS ON. Not
perfect.
-*TYPE=DATA,SIZE=1,WHEN=CTR EQ 2,$
ENDSTYLE
END
Webinar - Mar 2011
McGyver - David Smith
11
Reference
User Forum Presentation / 2010-12-16: McGyver Technique
http://www.informationbuilders.com/support/developers/presentations?id=388
Tips & Techniques / McGyver Technique Introduction
http://techsupport.informationbuilders.com/bestpractices/macgyver/cof_tcn_m00.html
Tips & Techniques / McGyver Techniques
http://techsupport.informationbuilders.com/bestpractices/macgyver/toc.html
Tips & Techniques / McGyver Dynamic Subtotals/SubFoots
http://www.informationbuilders.com/support/developers/McGyverDynamic
Contact David Smith: [email protected] , [email protected]
Webinar - Mar 2011
McGyver - David Smith
12