I've created a synonym for an MS SQL stored procedure. The SP requires a username which I need to pass the &IBIMR_user value.
I've tried modifying the synonym to pass &IBIMR_user to the SP but no luck yet.

The synonym includes the following line in the INPUT segment.
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $

I tried adding the XDEFAULT parameter as follows but it still requires manually entering the username to request sample data.
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), XDEFAULT='&IBIMR_user.EVAL', $

I also tried adding the XDEFAULT parameter hard-coded with my username, still no luck.
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), XDEFAULT='teasley', $

Version 8.2.06

This message has been edited. Last edited by: FP Mod Chuck,

How are you calling the synonym ?

You should be able to supply a WHERE clause.

e.g.
TABLE FILE {sp master}
...
WHERE @USERNAME EQ '&IBIMR_user'
END

I can pass the username in a report no problem.
The problem I am having is that when I add filters in the report that are dynamic parameters to use in a page filter these parameters are not populated and my assumption is that the retrieval (follows) cannot get these values without the input parameter.

WHERE SP_SPONSORED_PROJECTS.DIM_INPUT.FH_DIVISION_NAM EQ &FH_DIVISION_NAM.(OR(FIND SP_SPONSORED_PROJECTS.DIM_INPUT.FH_DIVISION_NAM IN FMSBI/SP_SPONSORED_PROJECTS |FORMAT=A30,SORT=ASCENDING)).FH_DIVISION_NAM:.;

This is why I want the synonym to know who the user is.

I also tried creating a reporting object that passes the IBIMR_user as a WHERE clause, then built a report on the RO. The report works but same problem with trying to create dynamic filters, the values are not populated, only the All option appears.

This message has been edited. Last edited by: Tim Easley,

Some progress, I edited the INPUT segment in the .mas file to change the ACCESS_PROPERTY value from NEED_VALUE to INTERNAL, then added a mandatory filter. (below)

When I run my page now, all dynamic filter list boxes are populated and work correctly

SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
FILTER FILTER1=@USERNAME EQ 'teasley';
TITLE='INPUT,Filter', MANDATORY=YES, $

So the next hurdle is that WFRS does not know &IBIMR_user (that's why I had to hard-code my user ID above).
Since security is set to "Trusted: Pass WebFOCUS User ID and their Groups" I'm hoping WFRS might have my user ID in a variable but haven't yet found any documentation on this.

This message has been edited. Last edited by: Tim Easley,

Tim,
This may work for you.
On the Adminitration Console under Configuration, Custom Settings, add this:
<set>IBIC_user(pass)
<set>IBIMR_user(pass)

&IBMR_user will be available for all procedures...

hth

Update: in edasprof.prf I created a global variable on WFRS with my username:
-SET &&WFRSUSER = GET_TOKEN(&FOCSECUSER, '\', 2);
And it works on the server:
-TYPE WFRSUSER = &&WFRSUSER
WFRSUSER = teasley

However, when I replace the hard-coded username in the input filter with this variable I still get no results:

SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
FILTER FILTER1=@USERNAME EQ '&&WFRSUSER.EVAL' ;
TITLE='INPUT,Filter', MANDATORY=YES, $

I read the documentation on using parameters in the .acx but it only refers to using parameters for connection and table name, nothing about passing a parameter used by the SP.

Thanks Tom, when I took over this job I noticed the custom vars were set as follows:

IBIC_user = &IBIC_user (pass)
IBIMR_user = &IBIMR_user (pass)

I tried changing as you suggested but IBIMR_user is still not available in WFRS. So I added a new var to edasprof based on FOCSECUSER and it seems to work as a variable now (ie -TYPE) but not in the synonym.

Tim,
IBIMR_user is "ONLY" available to procedures run out of MRE/Content.
Never used in a synonym, never needed to, so, IBI needs to help/assist you there.
IBIC_user is for WFRS...

The prize goes to Marcy Miller for solving this problem.

Global variables have to be declared (VARIABLE NAME line below) in synonyms before they can be used.

Note, in this example &&WFRSUSER is created in EDASPROD.PRF and is the FOCSECUSER value with the domain name stripped off.

Thanks Marcy!


FILENAME=SP_SPONSORED_PROJECTS, SUFFIX=SQLMSS , $
VARIABLE NAME=&&WFRSUSER, $
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@USERNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(INTERNAL), $
FILTER FILTER1=@USERNAME EQ '&&WFRSUSER';
TITLE='INPUT,Filter', MANDATORY=YES, $

Excellent!