Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Summary line

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Summary line
 Login/Join
 
Gold member
posted
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
 
Posts: 68 | Registered: February 20, 2004Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
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
 
Posts: 68 | Registered: February 20, 2004Report This Post
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
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
 
Posts: 68 | Registered: February 20, 2004Report This Post
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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
 
Posts: 68 | Registered: February 20, 2004Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
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
 
Posts: 68 | Registered: February 20, 2004Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
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
 
Posts: 68 | Registered: February 20, 2004Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Summary line

Copyright © 1996-2020 Information Builders