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     Field not totaling correctly

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Field not totaling correctly
 Login/Join
 
Member
posted
Hello All,

I am making a User log reporting by group name report. I am using two files. USERDETAILS and USELOG. USERDETAILS Contains a list of users who run reports. USELOG contains When a report was run, who ran it, how long it took, and what report it was. I separate the users into groups with a define. There are five groups. and For each group I need to show the Group name, the most popular report run, total times all report have been run, total times most popular report has run, and last run date.

I am having a problem getting the total times the most popular report has run correctly. I am only getting the most times the report has run from 1 user and not all users in a group.
I have a drill down by group to show which users ran which reports and how many times and that works fine.

A line looks like this:
Other | Purchases by Item | 5565 | 491 | 8/01/2008

I should be getting
Other | Purchases by Item | 5565 | 951 | 8/01/2008

In the 'Other' group there are two users who run the Purchases by Items report.
User 1 has run it 491 times and user 2 has run it 460 times. I can't find a way to show the popular report total. It just takes the Highest of the reports. I have tried a few different ways, and I am can't figure it out.

Any help will be greatly appreciated.
Thanks


-
WebFocus 7.6.5
On Windows 2003 Server
 
Posts: 5 | Registered: March 20, 2008Report This Post
Gold member
posted Hide Post
Andrew,

Could you post your code please?


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
Member
posted Hide Post
quote:
Originally posted by EWoerle:
Andrew,

Could you post your code please?


Yes here it is.

SET EMPTYREPORT = ANSI
SET LINES = 99999
-*SET &ECHO='ALL';
-DEFAULT &RPTFMT='HTML';
SET BYDISPLAY = ON
SET HOLDLIST = PRINTONLY

DEFINE FILE USERDETAILS
USERID/A20 = USERNAME;
END
TABLE FILE USERDETAILS
SUM USERID
BY USERNAME NOPRINT
ON TABLE SAVE AS SVUSERS
END


TABLE FILE USELOG
PRINT *
WHERE USERID IN FILE SVUSERS
ON TABLE HOLD AS HOLDLOG
END


JOIN INNER HOLDLOG.USERID IN HOLDLOG TO USERDETAILS.USERNAME IN USERDETAILS AS J0
END

DEFINE FILE HOLDLOG
NUMRUN/I4 WITH UTIME=1;
RUNTME/I3=ATODBL(RUNTIME, '03', 3);
RTME/I3=IF RUNTME LE 0 THEN 1 ELSE RUNTME;
COUNTER/I4 = 1;
END
TABLE FILE HOLDLOG
SUM
 MAX.UDATE
 MAX.RTME
 NUMRUN
 COUNTER
BY FEXNAME
BY USERID
BY FEXNAME
BY RSM
ON TABLE HOLD AS HOLDFINALA
END


TABLE FILE HOLDFINALA
SUM
 MAX.RTME
-*BY UDATE
 COUNTER
BY NUMRUN
BY FEXNAME
BY USERID
BY RSM
ON TABLE HOLD AS HOLDFINAL
END

DEFINE FILE HOLDFINALA
GROUP/A18 = IF USERID EQ 'S-SERVE' THEN 'CUSTOMER' ELSE IF USERID NE 'S-SERVE' AND EDIT(RSM,'9$$$$') EQ '0' THEN 'SALES' ELSE IF RSM EQ 'SAL' OR '$*' OR 'ALL' THEN 'OTHER' ELSE IF RSM EQ 'PUR' THEN 'PURCHASING' ELSE IF RSM EQ 'MKT' THEN 'MERCHANDISING' ELSE 'NATIONAL ACCOUNTS';
END

TABLE FILE HOLDFINALA
SUM
 MAX.UDATE
 MAX.NUMRUN AS 'NUMRUNB'
BY GROUP
ON TABLE HOLD AS HOLDFINALB
END

-RUN
DEFINE FILE HOLDFINAL
UDATEFT/A10 = EDIT(UDATE,'$$$$99') | '/' | EDIT(UDATE,'$$$$$$99') | '/' | EDIT(UDATE,'9999');
GROUP/A18 = IF USERID EQ 'S-SERVE' THEN 'CUSTOMER' ELSE IF USERID NE 'S-SERVE' AND EDIT(RSM,'9$$$$') EQ '0' THEN 'SALES' ELSE IF RSM EQ 'SAL' OR '$*' OR 'ALL' THEN 'OTHER' ELSE IF RSM EQ 'PUR' THEN 'PURCHASING' ELSE IF RSM EQ 'MKT' THEN 'MERCHANDISING' ELSE 'NATIONAL ACCOUNTS';
END

TABLE FILE HOLDFINAL
SUM
  COUNTER
  FEXNAME
  NUMRUN
  UDATE
BY GROUP
ON TABLE HOLD AS HOLDFINALC
END

JOIN HOLDFINALC.GROUP IN HOLDFINALC TO HOLDFINALB.GROUP IN HOLDFINALB AS J1 END

DEFINE FILE HOLDFINALC
UDATEFT/A10 = EDIT(UDATE,'$$$$99') | '/' | EDIT(UDATE,'$$$$$$99') | '/' | EDIT(UDATE,'9999');
END
TABLE FILE HOLDFINALC
SUM
  FEXNAME AS 'Most Popular Report'
  NUMRUN AS 'Total Reports, Times Run'
  NUMRUNB AS 'Popular Report, Times Run'
  UDATEFT AS 'Last Run'
  COUNTER
BY GROUP
ON TABLE SUBHEAD
"Log Report"
-IF &RPTFMT NE HTML GOTO SKPEXL;
HEADING
"Export to Excel"
-SKPEXL
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &RPTFMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *


-
WebFocus 7.6.5
On Windows 2003 Server
 
Posts: 5 | Registered: March 20, 2008Report This Post
Virtuoso
posted Hide Post
HOLDFINALA is sorted
BY FEXNAME
BY USERID
...

Hence in calculating NUMRUNB to serve as "times the group's most popular reports was run" ...
TABLE FILE HOLDFINALA
SUM
 MAX.UDATE
 MAX.NUMRUN AS 'NUMRUNB'  /* I guess you have ASNAMES=ON */
BY GROUP
ON TABLE HOLD AS HOLDFINALB
END   

... the MAX. operation is actually pulling the highest number of runs of a single fex by a single user within each Group.

Move your GROUP definition upstream, and replace BY USERID with BY GROUP in the hold file to which you will later apply MAX.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
quote:
Originally posted by j.g.:
HOLDFINALA is sorted
BY FEXNAME
BY USERID
...

Hence in calculating NUMRUNB to serve as "times the group's most popular reports was run" ...
TABLE FILE HOLDFINALA
SUM
 MAX.UDATE
 MAX.NUMRUN AS 'NUMRUNB'  /* I guess you have ASNAMES=ON */
BY GROUP
ON TABLE HOLD AS HOLDFINALB
END   

... the MAX. operation is actually pulling the highest number of runs of a single fex by a single user within each Group.

Move your GROUP definition upstream, and replace BY USERID with BY GROUP in the hold file to which you will later apply MAX.


This worked. Thank you for your help.


-
WebFocus 7.6.5
On Windows 2003 Server
 
Posts: 5 | Registered: March 20, 2008Report This Post
Gold member
posted Hide Post
Andrew,

I would take a look in your HOLDFINALB. I think the problem is your max.numrun because its look at the one max value in the group instead of summing up the value across the different users.

try doing something like a multi-verb request to do a sum within HOLDFINALA something along the lines of:

  
DEFINE FILE HOLDLOG
NUMRUN/I4 WITH UTIME=1;
RUNTME/I3=ATODBL(RUNTIME, '03', 3);
RTME/I3=IF RUNTME LE 0 THEN 1 ELSE RUNTME;
COUNTER/I4 = 1;
GROUP/A18 = IF USERID EQ 'S-SERVE' THEN 'CUSTOMER' ELSE IF USERID NE 'S-SERVE' AND EDIT(RSM,'9$$$$') EQ '0' THEN 'SALES' ELSE IF RSM EQ 'SAL' OR '$*' OR 'ALL' THEN 'OTHER' ELSE IF RSM EQ 'PUR' THEN 'PURCHASING' ELSE IF RSM EQ 'MKT' THEN 'MERCHANDISING' ELSE 'NATIONAL ACCOUNTS';
END
TABLE FILE HOLDLOG
SUM
 NUMRUN AS NUMRUNB
BY FEXNAME
BY GROUP
SUM
 NUMRUN
BY FEXNAME
BY GROUP
BY USERID
BY FEXNAME
BY RSM
ON TABLE HOLD AS HOLDFINALA
END


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report 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     Field not totaling correctly

Copyright © 1996-2020 Information Builders