Hello everybody,
This is the first time i post a comment !
I'm trying to produce a report on EXCEL 2010 using ODSOUT (RWI).
My firts point is I can not manage to produce my report on a single sheet because there are two DATA _NULL_ that hamper the one sheet production. The first one produce a banner with the name of the company.
The second DATA _NULL_, procduce the "body" results.
I would like to get a single sheet report but I don't know how to do. May be there are some default options that hamper the report.
And my styles are not taken into account. Particularly the Border. I don't know why !
I'm under 9.4M2 at work and at home I tried under the university edition.
I post my program below, it is running without error :
/*
PREMIERS TESTS DE PROD DES RAPPORTS SOUS EXCEL
*/
DATA page2 ;
INFILE DATALINES DELIMITER='*' TRUNCOVER DSD ;
LENGTH _info_ $4 level $1. sort 8. var1 $80. var2 $12. var3 $200. var4 $200.var5 $200. var6 $200. tableau $1. bloc 8 titreTableau $55. ;
LABEL var1 ="^_" var2 = "Volumes" var3 ="^S={FONTWEIGHT=BOLD}NPS" var4 ="Promotors" var5 ="Neutral" var6 ="Détractors" ;
INPUT _info_ $ level $ sort var1 $ var2 $ var3 $ var4 $ var5 $ var6 $ tableau $ bloc titreTableau $ ;
DATALINES;
bold*5*.*Résultats mensuels DG ASS (mai - 2014)*23502*37|+*51%|+*35%|0*14%|+*A*0*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*1*DIR ASS1*229*31|+*50%|=*31%|0*19%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*2*DIR ASS2*4105*37|+*52%|+*33%|0*15%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*3*DIR ASS3*1156*21|+*38%|+*44%|0*17%|+*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*4*DIR ASS4*16272*38|+* 51%|+*35%|0*13%|+*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*5*DIR ASS5*367*10|-*36%|=*38%|0*26%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*6*DIR ASS6*343*41|+*53%|=*35%|0*12%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*7*DIR ASS7*1030*45|=*57%|=*31%|0*12%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
bold*5*1*Comparaison mois précédent (avril - 2014)**38*52%*34%*14%*B*2*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
bold*5*2*Comparaison année précédente (mai - 2013)**29*45%*38%*16%*B*2*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
bold*5*1*Moyenne annuelle cumulée**37*51%*35%*14%*C*2*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
;
RUN ;
%LET mypath = %SYSFUNC(PATHNAME(WORK));
%PUT &mypath. ;
ODS _ALL_ CLOSE ;
/* pour éviter un WARNING */
GOPTIONS DEV=PNG ;
ODS ESCAPECHAR="^";
/* modèles pour l'ODS */
/* ODS PATH(PREPEND) RWI.firm RWI.graph ; <== ne fonctionne apparemment pas ! Voir plus bas
retester avec les styles et modèles dans la work !
*/
ODS EXCEL FILE = "&mypath./test_ReportRWI_Excel.xlsx"
OPTIONS( /*START_AT ="1,1" <== uniquement en 9.4M3 */
EMBEDDED_TITLES = "yes"
SHEET_NAME="Recommandation"
TAB_COLOR = "cx94D2DE"
)
/*STYLE = excel*/; /* <== s'impose à tous les autres styles mais il peut être modifié dans une tabulate, report ou print */
;
/*ODS TEXT="^S={FONTSIZE=4PT} Pretext via ODS TEXT" ;*/
/* ODS TEXT="^_" ;*/
DATA _NULL_ ;
bg = " BACKGROUNDCOLOR=cx94D2DE " ;
DECLARE ODSOUT o () ;
o.TABLE_START (STYLE_ATTR:"FRAME=VOID RULES=NONE") ;
/*o.ROW_START () ;
o.FORMAT_CELL(DATA:"^_", STYLE_ATTR:"FONTSIZE=2PT BACKGROUNDCOLOR=red ", COLUMN_SPAN:4) ;
o.ROW_END () ;*/
o.ROW_START () ;
o.FORMAT_CELL(DATA:"SAS &SYSVER.",
STYLE_ATTR:"JUST=LEFT VJUST=MIDDLE FONTWEIGHT=BOLD FOREGROUND=#E64415"!!bg, ROW_SPAN:2) ;
o.FORMAT_CELL(DATA:"Période^_de^_mesure^_:^nmai 2014",
STYLE_ATTR:"JUST=LEFT VJUST=TOP "!!bg, ROW_SPAN:2) ;
o.FORMAT_CELL(DATA:"DIRECTION GENERALE FIRM",
STYLE_ATTR:"WIDTH=72.5% JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg) ;
o.FORMAT_CELL(DATA:"FIRM",
STYLE_ATTR:"JUST=LEFT VJUST=MIDDLE FONTWEIGHT=BOLD FOREGROUND=BLACK"!!bg, ROW_SPAN:2) ;
o.ROW_END () ;
o.ROW_START () ;
o.FORMAT_CELL(DATA:"I- CUSTOMERS EXPERIENCE - Recommandation FIRM",
STYLE_ATTR:"JUST=CENTER FONTSIZE=12PT FONTSTYLE=ITALIC"!!bg) ;
o.ROW_END () ;
o.TABLE_END () ;
RUN ;
DATA _NULL_ ;
SET work.page2 END=eof ;
BY bloc ;
ARRAY val_pastille $ var3-var6 ;
vide = " FONTSIZE=1PT BACKGROUNDCOLOR=WHITE " ;
space = vide!!" WIDTH=1MM " ;
calage = ' ' ;
IF _N_=1 THEN DO ;
DECLARE ODSOUT o () ;
o.TABLE_START () ;
o.ROW_START (TYPE:"H") ;
o.FORMAT_CELL(DATA:"^_", ROW_SPAN:2, INHIBIT:"TBLR", STYLE_ATTR:space) ;
o.FORMAT_CELL(DATA:"^_", COLUMN_SPAN:2, INHIBIT:"TBLR", STYLE_ATTR:vide) ;
o.FORMAT_CELL(DATA:"^_", INHIBIT:"TL", STYLE_ATTR:space) ;
o.FORMAT_CELL(DATA:titreTableau, COLUMN_SPAN:4) ;
o.ROW_END () ;
o.ROW_START (TYPE:"H") ;
o.FORMAT_CELL(DATA:"^_", INHIBIT:"TL" , STYLE_ATTR:vide) ;
o.FORMAT_CELL(DATA:VLABEL(var2) , STYLE_ATTR:"WIDTH=2CM") ;
o.FORMAT_CELL(DATA:"^_", INHIBIT:"TB" , STYLE_ATTR:space) ;
DO OVER val_pastille ;
o.FORMAT_CELL(DATA:VLABEL(val_pastille) ) ;
END ;
o.ROW_END () ;
END ;
IF FIRST.bloc AND _N_ > 1 THEN DO ;
o.ROW_START (TYPE:"B") ;
DO i=1 TO DIM(val_pastille)+4 ;
o.FORMAT_CELL(DATA:"^_", INHIBIT:"LR", STYLE_ATTR:"FONTSIZE=6PT PADDING=0 BORDERWIDTH=0") ;
END ;
o.ROW_END () ;
END ;
LENGTH inhib $ 2 val $ 6 pastille $ 1 gras $ 20 ;
IF FIRST.bloc AND LAST.bloc THEN inhib=" " ;
ELSE IF FIRST.bloc THEN inhib="B" ;
ELSE IF LAST.bloc THEN inhib="T" ;
ELSE inhib="BT" ;
LENGTH mEf $ 40 ;
IF _info_="boldgray" THEN mEf = " FONT_WEIGHT=BOLD BACKGROUND=cxDEDEDE " ;
ELSE mEf = "" ;
SELECT (tableau) ;
WHEN ("A") DO ;
o.ROW_START (TYPE:"B") ;
o.FORMAT_CELL(DATA:"^_" , INHIBIT:"BTL" , STYLE_ATTR:space) ;
o.FORMAT_CELL(DATA:var1 , INHIBIT:inhib , STYLE_ATTR:mEf!!IFC(_info_="bold","FONT_WEIGHT=BOLD","")!!" TEXTALIGN=LEFT") ;
o.FORMAT_CELL(DATA:STRIP(var2)!!"^_^_" , INHIBIT:inhib , STYLE_ATTR:mEf!!" ASIS=ON TEXTALIGN=RIGHT") ;
o.FORMAT_CELL(DATA:"^_" , INHIBIT:"BT" , STYLE_ATTR:"WIDTH=2MM BACKGROUND=WHITE") ;
DO OVER val_pastille ;
val = SCAN(val_pastille,1,"|") ;
pastille = SCAN(val_pastille,2,"|") ;
gras = IFC(UPCASE(VNAME(val_pastille))="VAR3", "FONT_WEIGHT=BOLD ", "") ;
o.FORMAT_CELL(DATA:CATS("^_^_",val,"^_^_") , INHIBIT:inhib
, STYLE_ATTR:mEf!!gras!!IFC(pastille = "+"," POSTTEXT='^{STYLE[COLOR=GREEN FONT_WEIGHT=BOLD] ^{unicode 2B06}}' JUST=L ",
IFC(pastille = "-"," POSTTEXT='^{STYLE[COLOR=RED FONT_WEIGHT=BOLD] ^{unicode 2B07}}' JUST=L ",
IFC(pastille = "="," POSTTEXT='^{STYLE[COLOR=DARKGRAY FONT_WEIGHT=BOLD] ^{unicode 25CF}}' JUST=L "
,"")
,"")
,"")
);
END ;
o.ROW_END () ;
END ;
WHEN ("B") DO ;
o.ROW_START (TYPE:"B") ;
o.FORMAT_CELL(DATA:"^_" , INHIBIT:"BTL" , STYLE_ATTR:space) ;
o.FORMAT_CELL(DATA:var1 , STYLE_ATTR:IFC(_info_="bold","FONT_WEIGHT=BOLD","")!!" JUST=L", COLUMN_SPAN:2) ;
o.FORMAT_CELL(DATA:"^_" , INHIBIT:"BT" , STYLE_ATTR:"WIDTH=2MM") ;
DO OVER val_pastille ;
gras = IFC(UPCASE(VNAME(val_pastille))="VAR3", "FONT_WEIGHT=BOLD ", "") ;
o.FORMAT_CELL(DATA:CATS("^_^_",val_pastille,"^_^_") , STYLE_ATTR:gras!!calage) ;
END ;
o.ROW_END () ;
END ;
OTHERWISE ;
END ;
IF eof THEN DO ;
o.TABLE_END() ;
END ;
RUN ;
ODS EXCEL CLOSE ;
If anybody could help me to produce my report, il would be great.
Amazigh.
.
and I got my one sheet report. I just have to modify the banner in the first DATA _NULL_ to fit with the "BODY" report in the second DATA _NULL_. 
