Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE LOADED] How to get the report name to show up in the Focexec Name
Go
New
Search
Notify
Tools
Reply
  
[CASE LOADED] How to get the report name to show up in the Focexec Name
 Login/Join
 
Gold member
posted
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
 
Posts: 63 | Location: Liberty Lake, WA - USA | Registered: June 23, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1946 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.


Production: WebFOCUS 8.202M
QA: WebFOCUS 8.206.01
Windows/SQL
 
Posts: 37 | Registered: May 24, 2017Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.


Production: WebFOCUS 8.202M
QA: WebFOCUS 8.206.01
Windows/SQL
 
Posts: 37 | Registered: May 24, 2017Reply With QuoteReport This Post
Gold member
posted Hide Post
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


Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html
 
Posts: 63 | Location: Liberty Lake, WA - USA | Registered: June 23, 2016Reply With QuoteReport This Post
Silver Member
posted Hide Post
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


Webfocus 8.1
Windows x64
Excel
 
Posts: 39 | Location: Pittsburgh | Registered: July 25, 2017Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.


Production: WebFOCUS 8.202M
QA: WebFOCUS 8.206.01
Windows/SQL
 
Posts: 37 | Registered: May 24, 2017Reply With QuoteReport This Post
Gold member
posted Hide Post
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


Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html
 
Posts: 63 | Location: Liberty Lake, WA - USA | Registered: June 23, 2016Reply With QuoteReport This Post
Silver Member
posted Hide Post
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


Webfocus 8.1
Windows x64
Excel
 
Posts: 39 | Location: Pittsburgh | Registered: July 25, 2017Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE LOADED] How to get the report name to show up in the Focexec Name

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.