[CLOSED]How to print on the last line of a group within Report Painter
Hi,
Some columns of my sales report need to be print only on the last line of a group (before the next break). The report has to break by profit center and by accounting period week#. So each profit center has 5 lines to print for March period. One of the specific columns is CYCUMSLC should be pinted on the last line (week#5) that you can visualize in the code below.
-SET &ECHO ='ALL';
...
DEFINE FILE V_SLSWKRPT
APCDESC/A3=DECODE PROFITCENT (401 'QUE' 001 'MTL' 150 'BOO' 200 'DES' 300 'ALM' 500 'HUO' 100 'BAR' 600 'MUE' 999 'GRP');
BYSEQ/D2.0=DECODE PROFITCENT (401 01 001 02 150 03 200 04 300 05 500 06 100 07 600 08 999 09);
END
TABLE FILE V_SLSWKRPT
SUM
-* THIS_YEAR_CURRENT_PERIOD
CURSALES/P12C WITHIN PERIODWK
AS 'RÉEL(R),ACTUEL'
...
-* CUMULATIVE YEARS
COMPUTE [COLOR:RED]cycumsls[/COLOR]/D14 = IF APCDESC EQ LAST APCDESC THEN CYCUMSLC + ( CYCUMSLS / MIN.NBRWDYMT * MIN.NBRWDYWK ) ELSE CYCUMSLS / MIN.NBRWDYMT * MIN.NBRWDYWK;
AS '&CURYR,RÉEL'
...
BY BYSEQ NOPRINT
BY APCDESC AS 'Centre,Profit'
BY NBRWDYMT AS 'J/M'
BY [COLOR:RED]periodwk[/COLOR] AS 'Sem.'
BY NBRWDYWK AS 'J/S'
ON APCDESC SKIP-LINE
HEADING
"&CONFIDENT<+0>En date du &FFDATEDMYY"
...
FOOTING
"</1 &NOTE_1</1"
"&EOR"
ON TABLE SET SHOWBLANKS ON
ON TABLE SET NODATA 'n/d'
ON TABLE SET EMPTYCELLS OFF
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
UNITS=IN,
FONT='TAHOMA',
SIZE=10,
TITLETEXT='&EXLABEL',
...
How can I do this? Thanks.This message has been edited. Last edited by: Marikaki,
Marikaki
WF 7.7.03m, MRE, BI Dashboard, DevStudio, Report Caster, Windows 7, I.E. 8/9, Apache Tomcat 6.0, Derby Output formats: Excel2K, PDF, HTML, AHTML
March 15, 2010, 04:41 PM
Waz
Are you able to give an example using one of the sample files, it will be easier to understand.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
March 15, 2010, 05:06 PM
Marikaki
Hi Waz,
As additionnal information, the file used by the report is a cluster file. So the sample I'm sending is the model of 3 files concerned by the cluster.
Thanks.
REGION PFT.CTR SELL.BR PER.YEAR PERIOD PER.WK CPSLSCUM
ONT 100 1 2010 3 1 200,000.00
ONT 100 1 2010 3 2 400,000.00
ONT 100 1 2010 3 3 0.00
ONT 100 1 2010 3 4 0.00
ONT 100 1 2010 3 5 0.00
ONT 100 2 2010 3 1 100,000.00
ONT 100 2 2010 3 2 300,000.00
ONT 100 2 2010 3 3 0.00
ONT 100 2 2010 3 4 0.00
ONT 100 2 2010 3 5 0.00
ONT 100 3 2010 3 1 200,000.00
ONT 100 3 2010 3 2 400,000.00
ONT 100 3 2010 3 3 0.00
ONT 100 3 2010 3 4 0.00
ONT 100 3 2010 3 5 0.00
ONT 100 4 2010 3 1 100,000.00
ONT 100 4 2010 3 2 300,000.00
ONT 100 4 2010 3 3 0.00
ONT 100 4 2010 3 4 0.00
…
This message has been edited. Last edited by: Marikaki,
Marikaki
WF 7.7.03m, MRE, BI Dashboard, DevStudio, Report Caster, Windows 7, I.E. 8/9, Apache Tomcat 6.0, Derby Output formats: Excel2K, PDF, HTML, AHTML
March 15, 2010, 05:16 PM
Waz
So this is what you are currently getting ?
If so, what are you expecting, what goes into line 5 ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
March 16, 2010, 09:30 AM
Marikaki
Waz,
I did not send you the complete cluster I did. I provide the most important table which gives you a good idea.
The table contains all the data I need to report. What I want is do not print CYCUMSLC field from line 1 to 4. Print on line 5 and I don't know how to control it.
Please see report sample:
*** CONFIDENTIEL En date du 16 Mars 2010
VENTES ACTUELLES - BUDGET - VISÉE
Période de Mars 2010
Années cumulées fin Février 2010
"Centre
Profit" "
J/M" "
Sem." "
J/S" "2010
RÉEL" "
MRG" "
BUDGET" "MRG
BUD" "R10/
B10" "2009
RÉEL" "
MRG" "10/
9" "2008
RÉEL" "
MRG" "9/
8"
ALM 23 1 5 682,820
2 5 1,365,639
3 5 2,048,459
4 5 2,731,278
5 3 3,140,970
HUO 23 1 5 491,755
2 5 983,510
3 5 1,475,265
4 5 1,967,020
5 3 2,262,073
If the report does not show properly, please tell me how to send you a good copy of it.
Thanks
Marikaki
WF 7.7.03m, MRE, BI Dashboard, DevStudio, Report Caster, Windows 7, I.E. 8/9, Apache Tomcat 6.0, Derby Output formats: Excel2K, PDF, HTML, AHTML
March 16, 2010, 09:41 AM
Marikaki
Waz,
column 1: Profit center column 2: #workable days in the month column 3: period week (accounting period) column 4: #workable days in the week column 5: cumulative sales (CYCUMSLC)
I need to print the sales on period week 5 only ($3,140,970) .
Thank you.
Marikaki
WF 7.7.03m, MRE, BI Dashboard, DevStudio, Report Caster, Windows 7, I.E. 8/9, Apache Tomcat 6.0, Derby Output formats: Excel2K, PDF, HTML, AHTML
March 16, 2010, 10:17 AM
Marikaki
Again Waz,
Don't try to match the sample of the table with the report. I didn't take care of providing the same profit center information. If you need it, feel free to ask me.
Plus: on the sample report, each profit center has week #5 sales to print.
Tks.
Marikaki
WF 7.7.03m, MRE, BI Dashboard, DevStudio, Report Caster, Windows 7, I.E. 8/9, Apache Tomcat 6.0, Derby Output formats: Excel2K, PDF, HTML, AHTML
March 16, 2010, 10:21 AM
GinnyJakes
Have NODATA set to null. Create a DEFINE for your column. Set it to zero if the line number isn't 5. It should print as a blank for everything but line 5.
You gave me the logic I needed to manage it. I'm not yet finished with it.
1. When you say set NODATA to null do you mean
quote:
NULL
characters? If it's a value, please show me.
2. In my question I specified line 5 which is the representation of period week #5 in March 2010. In a future month it could be 4 period weeks in the month. Again I am green in reporting with webfocus. Do you believe that I need a hold file to get the max period week in the current accounting period prior to begin the report? If you can help me, I will appreciate your precious help.
Thank you.
Marikaki
WF 7.7.03m, MRE, BI Dashboard, DevStudio, Report Caster, Windows 7, I.E. 8/9, Apache Tomcat 6.0, Derby Output formats: Excel2K, PDF, HTML, AHTML
March 16, 2010, 11:45 AM
GinnyJakes
SET NODATA=''
Waz above suggested that you use a demo file to try to explain your issue. You might look at CENTURYSALES as it has dates. Maybe you could use that file to get data that looks like what you want, explain it again in a post with the code, and then we can try to solve your problem that way.
I for one do not completely understand what you want to do as the examples and the requests keep changing.