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

Andrew,

Could you post your code please?

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 *

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.

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.

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