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     Having issue setting variable in Heading

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Having issue setting variable in Heading
 Login/Join
 
<M_RIOS75>
posted
Hello all,

I have a report that has some security logic within the code. Simply, the person running the report can only see the figures for their sales office OR they can see ALL the sales offices if they have no one sales office associated to their user (sales office blank on extract). Also if user not found, report does not generate.

I would like the Heading of my graph to display as follows:

"Account Receivable Amount By Age For Sales Office
Where K3SLSO equals the Sales Office. This works as the sales office is pulled correctly, but I run into an issue when the person running the report has access to all sales offices.(field is blank) I need this variable to actually read as "All" or "All Offices". What is happening is, the first sales office is being pulled in, and depending on the data it could be any! Heres my code.. any help??

-*-SET &ECHO=ALL;
-SET &USERID=' ';
-SET &USERID = UPCASE(10, &IBI_REPORT_USER, &USERID);
-TYPE &USERID
TABLE FILE FRUDF
PRINT TFDSLO
BY TFUSER
WHERE TFUSER EQ '&USERID';
ON TABLE HOLD
END
-IF &LINES EQ 0 THEN GOTO ALL;
-SET &USER=' ';
-SET &SLSO=' ';
-READ HOLD &USER &SLSO
-TYPE &USER &SLSO
-IF &SLSO EQ ' ' GOTO ALL;
-RUN
-GOTO OKIT
-NOJOY
-HTMLFORM BEGIN
Error: User Id not found or user error


User id is not found on the reference file or is not assigned to a sales office



Please contact you System Administrator for assistance
-HTMLFORM END
-RUN
-EXIT
-OKIT
-TYPE you're in!!!!!!
-TYPE &SLSO
-TYPE &SLSO.LENGTH
-TYPE &SLSO.TYPE
-SET &K3SLSO = IF EDIT(&SLSO,'$$9') EQ ' ' THEN EDIT(&SLSO,'$$$9') ELSE
- IF EDIT(&SLSO,'$9') EQ ' ' THEN EDIT(&SLSO,'$$99') ELSE
- IF EDIT(&SLSO,'9') EQ ' ' THEN EDIT(&SLSO,'$999') ELSE &SLSO ;
-TYPE &K3SLSO
-TYPE &K3SLSO.LENGTH
-RUN
-GOTO JOINEM
-ALL
-TYPE you have global access
-SET &K3SLSO='$*';
-RUN
-JOINEM
JOIN DBARD.DBARD.K3AGEC IN DBARD TO UNIQUE DBAGE.DBAGE.HDAGEC IN DBAGE AS J0
GRAPH FILE DBARD
SUM K3AMT AS 'Extended Amount'
ACROSS HDHEAD AS 'Age'
HEADING CENTER
"Account Receivable Amount By Age For Sales Office WHERE K3SLSO EQ '&K3SLSO';

 
Report This Post
susannah
Expert
posted Hide Post
M_RIO, do you have a fixed number of sales offices?
if so,
-SET &USERID=' ';
-SET &USERID = UPCASE(10, &IBI_REPORT_USER, &USERID);
-TYPE &USERID
TABLE FILE FRUDF
PRINT TFDSLO
BY TFUSER
WHERE TFUSER EQ '&USERID';
ON TABLE HOLD
END
-IF &LINES EQ 0 THEN GOTO ALL;
-SET &USER=' ';
-SET &SLSO=' ';
-READ HOLD &USER &SLSO
-TYPE &USER &SLSO
-SET &FILTER = IF &SLSO EQ ' ' THEN ' ' ELSE
-         'WHERE K3SLSO EQ ' | &K3SLSO ;
-SET &HEADER = IF &SLSO EQ ' ' THEN 'for all offices' ELSE 'for sales  office' | &K3SLO ;
 
-IF &SLSO EQ ' ' GOTO ALL;


then use the &FILTER in your extract code,
and use &HEADER in your report header.
HEADING CENTER
"A/R amount... &HEADER "


Work for you?



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
<M_RIOS75>
posted
Thanks! I was able to get my heading to display correctly with the following text:

-SET &FILTER = IF &SLSO EQ ' ' THEN ' ' ELSE
-              'WHERE K3SLSO EQ ' | &K3SLSO ;
-SET &HEADER = IF &SLSO EQ ' ' THEN 'All Sales Offices' ELSE 'Sales Office: ' | &K3SLSO ;
-IF &SLSO EQ ' ' GOTO ALL;
-RUN
-GOTO JOINEM
-ALL
-TYPE you have global access
-SET &K3SLSO='$*';
-RUN
-JOINEM
JOIN DBARD.DBARD.K3AGEC IN DBARD TO UNIQUE DBAGE.DBAGE.HDAGEC IN DBAGE AS J0
GRAPH FILE DBARD
SUM K3AMT AS 'Extended  Amount'
ACROSS HDHEAD AS 'Age'
HEADING CENTER
"Account Receivable Amount By Age For &HEADER"


Only issue I have now is making the drill down continue to work.

The syntax in my FOCEXEC is
TYPE=DATA,
ACROSSCOLUMN=N1,
FOCEXEC=app/kirarsd1.fex(HDHEAD='A1' K3SLSO ='&K3SLSO'),$

With the next focexec starting off as
TABLE FILE DBARD
SUM
K3AMT
K3IDTE
K3SLSO
BY
K3CNSK
BY
HDHEAD
BY
K3AGED
WHERE K3SLSO EQ '&K3SLSO';
WHERE HDHEAD EQ '&HDHEAD';
ON TABLE NOTOTAL
ON TABLE HOLD
END
TABLE FILE HOLD
SUM
K3AMT AS 'Extended Amount'
K3IDTE AS 'Invoice Date'
BY HIGHEST K3AMT NOPRINT
BY K3CNSK AS 'Customer'
BY K3AGED AS 'Days'
HEADING
"Sales Office

I am not sure how to get these 2 back on track. Any suggestions??

This message has been edited. Last edited by: Kerry,
 
Report 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     Having issue setting variable in Heading

Copyright © 1996-2020 Information Builders