Hey Focal Point,
I am using this below code to find when users are using the SQL Passthru Option (so we can get frequently used fields build out into our metadata);

SET SQLENGINE = SQLMSS
ENGINE SQLMSS SET DEFAULT_CONNECTION WF_Repository
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
T2.PRT_PATH + '/' + T2.OBJNAME AS WFCPATH,
T2.OBJNAME,
T2.PRT_PATH,
T1.OBJ_HANDLE,
CAST(CAST(BCONTENT AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS CODE
FROM dbo.WF_CONTENT_REVS T1
INNER JOIN dbo.WF_REPOSOBJ T2
ON T1.OBJ_HANDLE = T2.HANDLE
WHERE CAST(CAST(BCONTENT AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE '%&SEARCH_STRING|%';
END
DEFINE FILE SQLOUT
MYCOUNT/I5 WITH WFCPATH=1;
END
TABLE FILE SQLOUT
HEADING
"Locations containing the string: &SEARCH_STRING "
" COUNT = SUM MYCOUNT NOPRINT
PRINT
CODE/A255 NOPRINT
BY WFCPATH AS 'Location'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = repository/enblue_light1.sty,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=6.000000,
$
ENDSTYLE
END

The problem is that in the report output, the 'Focexec Name' column, it only brings back Ad-Hoc and not the actual report name that is being run.

Does anyone know how to get the actual Name or Title of the report to show up instead of Ad-Hoc?

Thanks!

This message has been edited. Last edited by: LarissaB,

Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html

Larissa

I ran this request against my 8.2 system and the fex names displayed no problem. In fact Ad_Hoc was not in the result set anywhere. I am not sure why you are seeing that and not the actual fex name.

I see what she is seeing and I'm on 8.202M.

Tried in IE and in Chrome and both have AdHocFex as the name of the tab.

I think I see the problem.

If you run the procedure while in the text editor it will show up as AdHocFex.

However, if you save and then right click and choose Run, it will show as the proper Fex Name.

Hey RyanIPG13, I tried your suggestion and still got the Ad-Hoc. I was really hoping that would work!
Thanks for the suggestions though, keep 'em coming if you have any others.
It's not a huge inconvenience to then run another report with the field names used to find the procedure name, but one less step i'd like to take.
I can always load a ticket with support as well.

Here is another report I have that does the same thing, just returns Ad-Hoc, does this one do the same for you all or do you get actual names back?

DEFINE FILE _EDACONF/CATALOG/RM/RMLDB
SXRPCNAME/A577V = IF SMRPCNAME EQ ' ' THEN 'Ad-Hoc' ELSE SMRPCNAME;
END
-*
DEFINE FILE _EDACONF/CATALOG/RM/RMLDATA
SXRPCNAME/A577V = IF SMRPCNAME EQ ' ' THEN 'Ad-Hoc' ELSE SMRPCNAME;
END
-*
TABLE FILE _EDACONF/CATALOG/RM/RMLDB
HEADING CENTER
"SQL Passthru Requests by User ID and Date"
PRINT
-*SMCMDNAME
-*SMRPCNAME
SMNAME
SMCOLUMN
-*SMSUFFIX
BY SESSKEY NOPRINT
BY SMUSER
BY STARTDATE
BY SMCSEGNUM NOPRINT
BY SXRPCNAME AS 'Focexec Name'
WHERE SESSKEY EQ SMKEY AND SMKEY EQ SMFRMKEY
WHERE SMFRMKEY EQ SMCOLKEY AND SMFRMNUM EQ SMCCMDNUM AND SMDSNUM EQ SMCDSNUM
WHERE SMCMDNAME EQ 'SELECT'
WHERE SMRPCNAME NE 'baseapp/global_settings'
WHERE SMSUFFIX EQ ' '
MORE
FILE _EDACONF/CATALOG/RM/RMLDATA
WHERE SESSKEY EQ SMKEY AND SMKEY EQ SMFRMKEY
WHERE SMFRMKEY EQ SMCOLKEY AND SMFRMNUM EQ SMCCMDNUM AND SMDSNUM EQ SMCDSNUM
-*WHERE SESSKEY EQ SMKEY
WHERE SMCMDNAME EQ 'SELECT'
WHERE SMRPCNAME NE 'baseapp/global_settings'
WHERE SMSUFFIX EQ ' '
END

I thought dev\app studio user's report are considered to be Ad-Hoc report in Resource Analyzer. Also report that are run on-the-fly using the infoassist and not saved in repository are also considered to be Ad-Hoc report.

I have 500k executions by Ad-Hoc report last year and there is no way i could trace it back to a fex files.

But i am able to get report names which are executed by the client.

Thanks,
Aswin Ravikumar

LarissaB,

I tried your code and I got a "WebFOCUS Report" in the tab, until I put in:

TYPE=REPORT,
TITLETEXT='REPORTNAME',
$

Where REPORTNAME is whatever you want to show in the tab.

Thanks for all the help guys, I did end up loading a case because regardless of what I do or change, I do still get Ad-Hoc. I do know that we only have three or four reports that use the SQL passthru, and they are all saved, so I do expect names to show up.
I'll update with any notes I get from tech support on how to resolve this.
Thanks again

quote:

I'll update with any notes I get from tech support on how to resolve this.

Please do. We always neglected Ad-Hoc but if you find resolution it would help me too.

Thanks,
Aswin Ravikumar