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,

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

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!

MacGyver is how I would do it.

Glad to see that helps.

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

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

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.

Too bad there isn't a magical MacGyver button in the GUI for all the young ones that don't know what "code" is...

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

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;
...

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)?

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

I will check one more time, but looks that is not working, have tried several times. hate tried to use WHEN as consition too . Graph diapear from whole column

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