Hello all,

I am trying to create a usage report based on the resource analyzer SMQUERY table. I am counting the SMKEY field by procedure name.

My rpoblem is I need to divide the count by an integer depending on which procedure it is because when a report is ran, it may show as running the procedure up to 12 times, so I want to divide that total count by 12 to get the real number of times the report was ran.

My thought was:
If SMRPCNAME EQ 'proc1' then ((CNT.SMKEY)/#) ELSE etc.

but I cannot get it to count up first and then divide. As a note, the SMKEY is an alphanumberic field with a legnth of 40 (it is a date/time stamp with some other info at the end).

Here is my code so far wtihout this. Does anyone have knowledge in creating custom resource analyzer reports? Thanks in advance for any assistance.

DEFINE FILE SMQUERY
proc_name/A50=
DECODE SMRPCNAME (
'mits_zip_report' 'MITS Accounts & Stratum Analysis w/ Zip Code'
'registra' 'Registration Report'
'topfile' 'MITS Accounts & Stratum Report'
'waiver_report' 'Waiver & Open Audits Report'
'seperate_entity_report' 'Separate Entity Report'
'findings_and_collections_report' 'Closed Audit Activity Report'
'fc_analysis_report' 'Findings & Collections Report'
'fcb_corporate_report' 'FCB Corporate Report' ELSE 'Error-Call Admin'
);
DTCVT/MDYY=DATECVT(SMDATE, 'A8YYMD', 'MDYY');
END
TABLE FILE SMQUERY
SUM
CNT.SMKEY AS 'Times,Accessed'
MAX.DTCVT AS 'Last Access Date'
BY SMUSERID AS 'User'
BY proc_name AS 'Procedure Name'
HEADING
"WebFocus Usage Report"
"Report Date: <+0>&DATEtrMDYY <+0> "
"Server: "Date Range: <+0>&STDATE<+0> - <+0>&ENDDATE<+0> "
FOOTING
""
WHERE ( SMRPCNAME NE ' ' ) AND ( SMRPCNAME IN ('mits_zip_report','registra','waiver_report','seperate_entity_report','findings_and_collections_report','fc_analysis_report','topfile','fcb_corporate_report') );
WHERE ( NOT SMUSERID IN ('BAUERL','BECKD','SCHMOK1',' ','MAYOM','HICKMK','SHOCKJ','WFADMIN','ADMIN','admin','bauerl','NCR') );
WHERE ( DTCVT GE '&STDATE' ) AND ( DTCVT LE '&ENDDATE' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,).Select type of display output.

A multi-verb request might work here.

Example:

TABLE FILE CAR
SUM
CNT.MODEL

BY COUNTRY
SUM
SALES

COMPUTE XX/D5 = SALES / C1;

BY COUNTRY
BY MODEL
END

C1 refers to CNT.MODEL in the first SUM, it's the first non-by field.

Thanks, that got me pointed in the right direction. I ended up adding the follwing compute field to the report in place of the cnt.smkey:

SUM
COMPUTE REALCNT/I3 = IF SMRPCNAME EQ 'mits_zip_report' THEN ((CNT.SMKEY) / 3) ELSE IF SMRPCNAME EQ 'seperate_entity_report' THEN ((CNT.SMKEY) / 12) ELSE IF SMRPCNAME EQ 'waiver_report' THEN ((CNT.SMKEY) / 2) ELSE IF SMRPCNAME EQ 'findings_and_collections_report' THEN ((CNT.SMKEY) / 3) ELSE IF SMRPCNAME EQ 'fc_analysis_report' THEN ((CNT.SMKEY) / 7) ELSE IF SMRPCNAME EQ 'topfile' THEN ((CNT.SMKEY) / 1) ELSE IF SMRPCNAME EQ 'fcb_corporate_report' THEN ((CNT.SMKEY) / 6) ELSE IF SMRPCNAME EQ 'registra' THEN ((CNT.SMKEY) / 1) ELSE 999;
AS 'Times,Accessed,(Real)'