June 19, 2008, 03:56 PM
JOEAcross Reporting
I need my across query to do the following:
AHT Aux
Jan Feb Mar Apr May Jan Feb Mar Apr May
Currently, it does this:
Jan Feb Ect.
AHT Aux AHT AUX
Part of My Code:
TABLE FILE 99GROWTH
PRINT
MONTHEND
FULLNAME
PPNUMBER
TIMESMONITORED
ANSWERED
PRODAUXPRCNT
TTLSTAFFED
AUXPERCENT
OSCARSCORE
AVGTALK
ACWTIME
MARKETTEAM
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS ROBAHTTREND FORMAT FOCUS INDEX 'MARKETTEAM'
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
SET NODATA = 0
JOIN
TEAM_HIARCHY.TEAM_HIARCHY.TEAMNAME IN TEAM_HIARCHY TO MULTIPLE
ROBAHTTREND.SEG01.MARKETTEAM IN ROBAHTTREND AS J0
END
DEFINE FILE TEAM_HIARCHY
Month/A15=DECODE MONTHEND( '05312008' 'May 2008' ELSE other );
uaux/D3.2=TTLSTAFFED * AUXPERCENT;
auxa/D3.2=TTLSTAFFED * PRODAUXPRCNT;
NMONTH/tMYY=MONTHEND;
AHT/D4=AVGTALK + ACWTIME;
AHTA/D8.2=AHT * ANSWERED;
ATTA/I8=AVGTALK * ANSWERED;
ACWA/I8=ACWTIME * ANSWERED;
oscara/I5=TIMESMONITORED * OSCARSCORE;
ServiceGroup/A20=DECODE MANAGER( Bunch of Names);
Debs/A10=DECODE ServiceGroup( 'Service Group 1' Deb 'Service Group 3' Deb 'Service Group 4' Deb 'Service Group 5' Deb Online Deb ELSE Other );
END
TABLE FILE TEAM_HIARCHY
-*
-*
-*WHERE MONTHEND EQ '05312008';
SUM
COMPUTE ACW1/I8 = ACWA / ANSWERED; NOPRINT
COMPUTE ATT1/I8 = ATTA / ANSWERED; NOPRINT
COMPUTE AHT/D5 = ACW1 + ATT1;
COMPUTE uauxb/D8.2 = uaux / TTLSTAFFED; AS 'Un-Prod Aux'
COMPUTE pauxb/D8.2 = auxa / TTLSTAFFED; AS 'Prod Aux'
COMPUTE oscarb/D10.2 = oscara / TIMESMONITORED; AS 'OSCAR'
BY ServiceGroup
BY MARKETTEAM
BY PPNUMBER
BY FULLNAME
ACROSS MONTHEND
ON ServiceGroup RECOMPUTE AS '*TOTAL ServiceGroup'
ON MARKETTEAM RECOMPUTE AS '*TOTAL'
ON MARKETTEAM PAGE-BREAK
Thanks for any help that you can provide.
Joe
June 20, 2008, 12:57 AM
WazBy default the Titles will be below the across values.
One way to achieve this would be to have your AHT and Aux columns as rows and across them as well, but do it before MONTHEND.
Something like
TABLE FILE CAR
PRINT
DEALER_COST
RETAIL_COST
BY COUNTRY
ACROSS SEATS AS ''
ACROSS BODYTYPE AS ''
END
June 20, 2008, 10:46 AM
j.grossYou need to generate something like this?
Xxxx Yyyy ...
Jan Feb Mar Apr May Jan Feb Mar Apr May ...
---------------------------------------------------------
WHATEVER
--------
aaaaaaa xxx xxx xxx xxx xxx yyy yyy yyy yyy yyy ...
bbbbbbb xxx xxx xxx xxx xxx yyy yyy yyy yyy yyy ...
. . .
If all the measures have a common format, just apply a bit of McGuyver pixie dust.
Join to a sequencer file, to generate N rows (one per measure) for each original data row, define the measure indicator field (VAR) and the measure value (VALUE) as appropriate to each row, and sum VALUE ACROSS VAR ACROSS MONTH:
JOIN NADA WITH MONTH IN HOLD TO NADA IN SEQLIST
DEFINE FILE HOLD
NADA /A1 WITH MONTH = ' ';
VAR/XXX =DECODE Seq(1 'Xxxx' 2 'Yyyy' ...);
VALUE/XXX = IF Seq EQ 1 THEN XVAR ...
ELSE IF Seq EQ 2 THEN YVAR etc.;
END
TABLE FILE HOLD
WRITE VALUE AS ''
ACROSS Seq NOPRINT <- to force the VAR names into desired order
ACROSS VAR AS '' <- shows the original measure names
ACROSS MONTH AS ''
BY whatever <- sort keys for the rows of output
IF Seq FROM 1 TO [N]
END
June 20, 2008, 05:33 PM
JOEThanks JG. I'm going to work on your suggestion. I'll let you know how it turns out.
June 21, 2008, 04:42 PM
Danny-SRLJoe,
Just notice that in order to produce this kind of report, the formats of AHT, uauxb etc. will have to be identical. Maybe you will be able to modify them dynamically (see page 6-17 of the creating reports manual 7.6).
June 22, 2008, 04:42 AM
Danny-SRLJoe,
Having had a bit of time on my hands, I concocted this example:
-* File Joe2.fex
SET HOLDLIST=PRINTONLY
-*
-* this is to create a fictitious month
DEFINE FILE CAR
XMONTH/MYY=WEIGHT/10 + HEIGHT + LENGTH;
MONTH/Mt=XMONTH;
END
-*
-* save data. use COMPUTE to add fields which will serve for the across sort
TABLE FILE CAR
SUM
COMPUTE TITL1/A5='SALES';
SALES/I7
COMPUTE TITL2/A5='RCOST';
RCOST/I7
COMPUTE TITL3/A5='DCOST';
DCOST/I7
BY COUNTRY
BY MONTH
ON TABLE SAVE AS JOE
END
-RUN
-*
-* The JOE master:
-* Notice that instead of having three pairs of title/values, it has one repeating pair within a child segment.
-*
-*FILENAME=JOE , SUFFIX=FIX
-*SEGMENT=JOE, SEGTYPE=S0
-* FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A10, ACTUAL=A10, $
-* FIELDNAME=MONTH, ALIAS=E02, USAGE=Mt, ACTUAL=A02, $
-*SEGMENT=FIELDS, PARENT=JOE, OCCURS=VARIABLE
-* FIELDNAME=TITL, ALIAS=E03, USAGE=A5, ACTUAL=A05, $
-* FIELDNAME=VALUES,ALIAS=E04, USAGE=I7, ACTUAL=A07, $
TABLE FILE JOE
SUM VAL
BY COUNTRY
ACROSS TITL AS ''
ACROSS MONTH AS ''
END
See if this answers your question.