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     [SOLVED] Passing user-input as parameter in drilldown report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Passing user-input as parameter in drilldown report
 Login/Join
 
Nova27
Platinum Member
posted
Hello,

I am working on a drilldown report where I prompt the user for a date - I have to pass this date and another field as parameters to my drill down report.

Note: Both my reports are SQL pass thru reports.

Main report displays just 2 columns: APP_ID , CUST_NAME
I am creating a drilldown link on APP_ID column which when clicked, should run the DETAIL report based on the APP_ID value and the date entered by the user on main report's prompt page.

My drilldown code (in main report) looks like this:
TYPE=DATA,
	COLUMN=APP_ID,
	FOCEXEC=REPORT_DETAIL(APP_ID=APP_ID \ SEARCH_PERIOD=&REQDATE.EVAL),$


Here, &REQDATE is the user-input date which is entered (by the user) on the prompt page when the main report is executed.

The DETAIL report also has APP_ID & SEARCH_PERIOD in the WHERE clause of the SQL and I am trying to pass these values through my drilldown link.

I am unable to pass &REQDATE (date entered by user) to the DETAIL report - it throws this error:
(FOC295) A VALUE IS MISSING FOR: &REQDATE

Please help!

Thanks in advance!

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


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
MartinY
Virtuoso
posted Hide Post
It may have several reasons why you lose the &REQDATE value.

One reason could be that you may have, after the SQL pass thru, a
-DEFAULTH &REQDATE =

Another thing is :
I suppose that you have a fex that has the main SQL pass thru for the main report.
Before the execution of the SQL pass thru, add the following:
-SET &DREQDATE = &REQDATE;

Then change for
TYPE=DATA,
     COLUMN=APP_ID,
     FOCEXEC=REPORT_DETAIL(APP_ID=APP_ID \
     SEARCH_PERIOD=&DREQDATE.EVAL),$

You can also see, from the browser status bar, the hyperlink value when you hover the drillable value from the main report.
Take a look at the passed values.

You may also may need to use a global variable instead of a local variable
-SET &&DREQDATE = &REQDATE;


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Nova27
Platinum Member
posted Hide Post
Hi Martin,

I tried using -SET &DREQDATE = &REQDATE; in both my fex procedures before the SQL executes, but I still get the same error.

And the browser status bar shows correct value passed for the APP_ID column (as expected), but for the SEARCH_PERIOD it does not pass anything Frowner

Just shows SEARCH_PERIOD = (there's nothing after the '=' sign)

I am not sure how to use the global variable - do I set it in both fexes? When I set it in the main fex, I was prompted to enter values for both 'DREQDATE' & 'REQDATE' - clearly I am not doing something right.

Also, in this case do I need a global variable? Seems like that might be what I need, the user-entered date to be passed to main report, used it in the where clause of the main report's SQL and then pass it along to DETAIL report and use it in the where clause of the DETAIL report's SQL.

Please advise! Thanks!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
OPALTOSH
Platinum Member
posted Hide Post
You have changed the name of the variable that is passed to the detail report. If you want the variable in the detail report to be called &REQDATE then use REQDATE='&REQDATE' in the drill down link.
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
OPALTOSH
Platinum Member
posted Hide Post
Try this
quote:
TYPE=DATA,
COLUMN=APP_ID,
FOCEXEC=REPORT_DETAIL(APP_ID=APP_ID \ &REQDATE='&REQDATE'),$
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
OPALTOSH
Platinum Member
posted Hide Post
quote:
Sorry my typo
Try this:
TYPE=DATA, COLUMN=APP_ID, FOCEXEC=REPORT_DETAIL(APP_ID=APP_ID \ REQDATE='&REQDATE'),$
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
MartinY
Virtuoso
posted Hide Post
quote:

I tried using -SET &DREQDATE = &REQDATE; in both my fex procedures before the SQL executes

Do not perform the -SET command in both fex. It only has to be done in the main one.

quote:

TYPE=DATA, COLUMN=APP_ID, FOCEXEC=REPORT_DETAIL(APP_ID=APP_ID \ REQDATE='&REQDATE'),$

The above will change nothing. The variable's name doesn't need to be the same as the variable that assign the value. Until the variable's name (before the equal sign) is the same as used in REPORT_DETAIL fex, it's ok.
Below should work :
TYPE=DATA,
     COLUMN=APP_ID,
     FOCEXEC=REPORT_DETAIL(APP_ID=APP_ID \
     SEARCH_PERIOD=&DREQDATE.EVAL),$

Pay attention on fact that the backslash ( \ ) is used to tell focus that the FOCEXEC command line continue on the next one.
So be sure to have a carriage return after the slash as above sample otherwise it has to be as below :
TYPE=DATA,
     COLUMN=APP_ID,
     FOCEXEC=REPORT_DETAIL(APP_ID=APP_ID SEARCH_PERIOD=&DREQDATE.EVAL),$


quote:

And the browser status bar shows correct value passed for the APP_ID column (as expected), but for the SEARCH_PERIOD it does not pass anything

Just shows SEARCH_PERIOD = (there's nothing after the '=' sign)

If it's the case I will first look at why the REQDATE variable is not passed. The issue is not at the drilled report but before the main fex.

Try the following just BEFORE the first SQL and look at the result.
-SET &ECHO = ALL;
-TYPE REDATE = &REQDATE
-EXIT

If no value of REQDATE is displayed you may need to look at the way you do assign and receive that variable.

Post your fex code it may help to figure what you're doing wrong. Now I'm just assuming things...


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Nova27
Platinum Member
posted Hide Post
Thank you all for your help & comments!

I finally could pass the date to the detail report - had to use REQDATE = &REQDATE.EVAL

Also I had to change the date format so that the SQL in the DETAIL report would accept it correctly.

Thanks again everyone! Appreciate your help!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report 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     [SOLVED] Passing user-input as parameter in drilldown report

Copyright © 1996-2020 Information Builders