Anyone have any ideas how to pass an optional filter to a drill down, if the filter is NOT on the report?

Report A is on a Portal page with F as an optional filter. Filter F is not on the "By" or "Across", because it has summarized metrics.

I would like to drill down to Report B, but can't pass the filter F down to Report B, since filter F isn't on the report.

thanks!

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

What version of WebFOCUS are you running?
Also, I think you can add this field to Report A NOPRINT. It won't show up on your report but the field will be available to pass to a drilldown.

By the way, I've heard this requirement will go away in the near future.

BabakNYC,

I'm running 8206.

"NOPRINT" does hide the field "F" and make it available for drill down, but unfortunately it is impacting my "Sum" metrics.

For example:
Field Value| Sum Metric Value
A| 5.0
A| 3.0

Instead of:
A| 8.0

Because even though NOPRINT field "F" isn't on the report:
Field Value:| NOPRINT field "F"| Sum Metric Value
A| one| 5.0
A| two| 3.0

This message has been edited. Last edited by: Henry Ma,

Henry

Field F does not have to be a BY or an ACROSS just reference it with NOPRINT in the SUM and it can still be passed through

FP Mod Chuck,

That works if I selected a specific value, but if I provide "ALL" to the field "F" in my Page, it's not passing the "ALL" down to the drilled report. It seems to pass one of the values in the field "F".

Henry

ALL should not pass a value it should pass _FOC_NULL which should be ignored. Put -SET &ECHO=ALL; at the beginning of the drill down report and then view the source of the html page displayed it will show you the actual code that was run and the value for that variable.

My field "F" below is "&ADMS_SHARE_CREATED_BRANCH_NAME". Looking at the source in the HTML, I see that it is not passing in _FOC_NULL, even though in my report, the value is "ALL". The "&ADMS_SHARE_CREATED_BRANCH_NAME" is an optional dynamic prompt. What am I doing wrong? This is kicking my butt...

Here's a snipit of my code in the report:

-DEFAULT &ADMS_SHARE_CREATED_BRANCH_NAME = _FOC_NULL;
.
.
.
TABLE FILE adms_cu_analytics/schemas/savings_sym_sj
SUM CNT.DST.SAVINGS_SYM_SJ.SAVINGS.SHARE_UNIQUE_SHARE_IDENTIFIER AS 'Count'
OVER SAVINGS_SYM_SJ.SAVINGS.ADMS_SYMSHARE_ORIGINALBALANCE/P21CM AS 'Orig. Balance'
OVER SAVINGS_SYM_SJ.SHARE_CREATED_DIMBRANCH_1.ADMS_SHARE_CREATED_BRANCH_NAME NOPRINT
BY SAVINGS_SYM_SJ.SHARE_CREATED_USERS.ADMS_SYMUSERS_SCREATED_NAME
ACROSS HIGHEST SAVINGS_SYM_SJ.SAVINGS.ADMS_SYMSHARE_OPENDATE_YEAR_M
WHERE SAVINGS_SYM_SJ.SHARE_CREATED_DIMBRANCH_1.ADMS_SHARE_CREATED_BRANCH_NAME EQ &ADMS_SHARE_CREATED_BRANCH_NAME.(FIND SAVINGS_SYM_SJ.SHARE_CREATED_DIMBRANCH_1.ADMS_SHARE_CREATED_BRANCH_NAME IN ADMS_CU_ANALYTICS/SCHEMAS/SAVINGS_SYM_SJ |FORMAT=A150V,SORT=ASCENDING).ADMS_SHARE_CREATED_BRANCH_NAME:.QUOTEDSTRING;
WHERE SAVINGS_SYM_SJ.SAVINGS.ADMS_SYMSHARE_OPENDATE FROM &&P12MONTH_S TO &&CMONTH_E;

.
.
.
TYPE=DATA, BACKCOLOR=(RGB(255 255 255) RGB(235 235 235)), $
TYPE=DATA, ACROSSCOLUMN=N1, ALT='Drill Shares_2', TARGET='_blank', FOCEXEC=IBFS:/WFC/Repository/ADMS_CU_Analytics/~hma/drilldowns/Drill_Shares_2.fex(ADMS_SYMUSERS_SCREATED_NAME=SAVINGS_SYM_SJ.SHARE_CREATED_USERS.ADMS_SYMUSERS_SCREATED_NAME ADMS_SYMSHARE_OPENDATE_YEAR_M=SAVINGS_SYM_SJ.SAVINGS.ADMS_SYMSHARE_OPENDATE_YEAR_M ADMS_SHARE_CREATED_BRANCH_NAME=SAVINGS_SYM_SJ.SHARE_CREATED_DIMBRANCH_1.ADMS_SHARE_CREATED_BRANCH_NAME), $
ENDSTYLE



Here's a snipit of my code in the drill down report:

-SET &ECHO=ALL;
.
.
.
-DEFAULT &ADMS_SHARE_CREATED_BRANCH_NAME = _FOC_NULL;
TABLE FILE adms_cu_analytics/schemas/savings_sym_sj
SUM SAVINGS_SYM_SJ.SAVINGS.ADMS_SYMSHARE_ORIGINALBALANCE
SAVINGS_SYM_SJ.SAVINGS.ADMS_SYMSHARE_BALANCE
BY SAVINGS_SYM_SJ.SHARE_CREATED_USERS.ADMS_SYMUSERS_SCREATED_NAME
BY SAVINGS_SYM_SJ.SHARE_CREATED_DIMBRANCH_1.ADMS_SHARE_CREATED_BRANCH_NAME
WHERE SAVINGS_SYM_SJ.SHARE_CREATED_DIMBRANCH_1.ADMS_SHARE_CREATED_BRANCH_NAME EQ &ADMS_SHARE_CREATED_BRANCH_NAME.(FIND SAVINGS_SYM_SJ.SHARE_CREATED_DIMBRANCH_1.ADMS_SHARE_CREATED_BRANCH_NAME IN ADMS_CU_ANALYTICS/SCHEMAS/SAVINGS_SYM_SJ |FORMAT=A150V,SORT=ASCENDING).ADMS_SHARE_CREATED_BRANCH_NAME:.QUOTEDSTRING;
HEADING
.
.
.

This is just a guess but change your where statement in the drill down to this.


WHERE SAVINGS_SYM_SJ.SHARE_CREATED_DIMBRANCH_1.ADMS_SHARE_CREATED_BRANCH_NAME EQ '&ADMS_SHARE_CREATED_BRANCH_NAME'


Didn't work, but thank you so much for taking the time and providing some thoughts though FP Mod Chuck!

I have to continue trying other things. I created both reports through InfoAssist. InfoAssist created the code for the "WHERE". I still think the problem is because I put the ADMS_SHARE_CREATED_BRANCH_NAME in the SUM, regardless if I'm showing the field on the report. It's like it can't have _FOC_NULL

Henry

I looked closer at your drill down syntax in the first report and you don't have a reference for ADMS_SHARE_CREATED_BRANCH_NAME so that parameter really isn't being passed.

thanks FP Mod Chuck! I didn't understand it until BabakNYC showed me. He had me change the piece of code that executes the FOCEXEC.

In the calling report from this:
ADMS_SHARE_CREATED_BRANCH_NAME=SAVINGS_SYM_SJ.SHARE_CREATED_DIMBRANCH_1.ADMS_SHARE_CREATED_BRANCH_NAME), $

to this:
ADMS_SHARE_CREATED_BRANCH_NAME=&ADMS_SHARE_CREATED_BRANCH_NAME.QUOTEDSTRING), $

This message has been edited. Last edited by: Henry Ma,