Focal Point
[SOLVED] Summary line

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7617049186

April 21, 2016, 03:50 PM
Piter
[SOLVED] Summary line
Dear Colleagues,

I have a question. I need report where each line of report show number of unique users in City and final summary line must show count ofvunique users in Region. Like

PLACE: USERS:
Paris 10
Berlin 5
Total Europe 11

(11 as the 4 users were using service in both Paris and Berlin)

What is the best technique to do that?

Normally I do 2 hold files(one for summary line, another for lines - same fields, but using BY CITY), later merge them with MORE into HOLD3 and printing HOLD3.

There are two problem with that technique
1. I do drill on PLACE. I need drill on City but do not need drill on "Total Europe" line. Normally when I describe drill I do it like

TYPE=DATA, COLUMN=N1, FOCEXEC=report(), END

I cannot skip drill on last (total) row, that loan is treated as normal data row


3. making 2 files is a bit strange technique. In SQL I can do it with simple union in one sentence. May be I do all in too complicated way? I feel that I miss something to make my report easier.

Thank you for your advise

Piter

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


Wf7704/WF8,Win64/32
April 22, 2016, 08:07 AM
MartinY
Personally, I'll do it this way :
TABLE FILE CAR
SUM SEATS
BY TOTAL COMPUTE ROWID /I1  = 1;
BY CAR
WHERE CAR NE 'AUDI';
ON TABLE HOLD AS TMPDET FORMAT FOCUS
END
-RUN

TABLE FILE CAR
SUM SEATS
BY TOTAL COMPUTE ROWID /I1  = 2;
BY TOTAL COMPUTE CAR   /A16 = 'TOTAL';
ON TABLE HOLD AS TMPTOT FORMAT FOCUS
END
-RUN

TABLE FILE TMPDET
PRINT SEATS
BY ROWID
BY CAR
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE TMPTOT
END
-RUN

TABLE FILE RPTDATA
PRINT SEATS
BY ROWID NOPRINT
BY CAR
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     DEFMACRO=NODRILL,
     MACTYPE=RULE,
     WHEN=CAR  NE 'TOTAL',
-* OR USING THE ROWID
-*   WHEN=ROWID NE 2,
$
TYPE=DATA,
     COLUMN=CAR,
     FOCEXEC=cardrill(CNTRY=CAR),
     TARGET='_BLANK',
     MACRO=NODRILL,
$
ENDSTYLE
END
-RUN



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
April 22, 2016, 08:33 AM
Piter
MartinY, thank you a lot for this part of code:

quote:
N TABLE SET STYLE *
DEFMACRO=NODRILL,
MACTYPE=RULE,
WHEN=CAR NE 'TOTAL',



I am so happy, that there are people like you who help us and ibi! Thank you!


Wf7704/WF8,Win64/32
April 22, 2016, 09:15 AM
jgelona
MacGyver is how I would do it.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
April 22, 2016, 09:54 AM
MartinY
Glad to see that helps.

Update your first post title and add [SOLVED] at the beginning.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
April 22, 2016, 10:23 AM
Piter
quote:
Originally posted by jgelona:
MacGyver is how I would do it.


Hm. will it work for Unique calculation? it looks that MacGyver will do arithmetical sum of lines. No?

looks like it kind of SQL "HAVING" not SQL UNION (union with total line). I am wrong? May be thare is link to some post describing UNION in MacGyver?

Thank you


Wf7704/WF8,Win64/32
April 25, 2016, 09:26 AM
jgelona
There was an old Systems Journal where most of it was written by Noreen and Art. The issue was almost completely dedicated to MacGyver. For all of my new people it is required study. With MacGyver, FOCUS/WebFOCUS can do almost any kind of data manipulation you want.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
April 25, 2016, 09:40 AM
Francis Mariani
Too bad there isn't a magical MacGyver button in the GUI for all the young ones that don't know what "code" is...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 02, 2016, 06:08 AM
Piter
MartinY, if you see this post. Is there any workaraund not too include TOTAL line for GRAPH. looks when we merge report with final line, that becomes data line. and if I do graph on column, then TOTAL is big and rest of graphs becomes very small


Wf7704/WF8,Win64/32
May 02, 2016, 08:54 AM
MartinY
Hi Piter,

When using data for report use it as is (detail and total merged into one file), when you use the merged data for graph, just add a condition on the data such as :
WHERE ROWID NE 2;


This way you can use the same RPTDATA file from my previous sample in both situation but displaying different data (omit TOTAL row data) for the graph.

GRAPH FILE RPTDATA
SUM...
BY...
WHERE ROWID NE 2;
...



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
May 02, 2016, 09:48 AM
Piter
Pardon, Martin, may be I was wrong in my question. I mean Visualization, when I do

GRAPHTYPE=DATA,
COLUMN=N1,
GRAPHCOLOR='GREEN',

there I need to skip last line. I need no graph and I need exclude it from calculation.

i.e. I meed

Berlin, 5, XXXXX
Paris , 3, XXX
Total, 7
(one person was in both Berlin and Paris, no need fro visualization on total line)

Not like I am getting now
Berlin, 5, XXX
Paris , 3, X
Total, 7 ,XXXXXXX

May be that is impossible? And I need to make DEFINE (but define will for for HTML only)?


Wf7704/WF8,Win64/32
May 02, 2016, 11:02 AM
MartinY
This is styling as same as for the drill down not applicable to total.

Add the same macro condition

GRAPHTYPE=DATA,
 COLUMN=N1,
 GRAPHCOLOR='GREEN',
 MACRO=NODRILL,


In this case you may want to rename your macro condition to a more meaningful name such as "TOTNOAPLY"

Should work


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
May 02, 2016, 11:06 AM
Piter
I will check one more time, but looks that is not working, have tried several times. hate tried to use WHEN as consition too Frowner. Graph diapear from whole column


Wf7704/WF8,Win64/32
May 02, 2016, 11:54 AM
MartinY
Here is the trick...
TABLE FILE CAR
SUM SEATS
BY TOTAL COMPUTE ROWID /I1  = 1;
BY CAR
WHERE CAR NE 'AUDI';
ON TABLE HOLD AS TMPDET FORMAT FOCUS
END
-RUN

TABLE FILE CAR
SUM SEATS
BY TOTAL COMPUTE ROWID /I1  = 2;
BY TOTAL COMPUTE CAR   /A16 = 'TOTAL';
ON TABLE HOLD AS TMPTOT FORMAT FOCUS
END
-RUN

TABLE FILE TMPDET
PRINT SEATS
BY ROWID
BY CAR
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE TMPTOT
END
-RUN

TABLE FILE RPTDATA
PRINT SEATS
BY  ROWID NOPRINT
BY  CAR
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
     DEFMACRO=TOTNOTAPLY,
     MACTYPE=RULE,
     WHEN=N1 NE 2,
$
     GRAPHTYPE=DATA,
     COLUMN=N3,
     GRAPHCOLOR='WHITE',
$
     GRAPHTYPE=DATA,
     COLUMN=N3,
     GRAPHCOLOR='GREEN',
     MACRO=TOTNOTAPLY,
$
TYPE=DATA,
     COLUMN=N2,
     TARGET='_BLANK',
     MACRO=TOTNOTAPLY,
     FOCEXEC=cardrill(CNTRY=CAR),
$
ENDSTYLE
END
-RUN



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007