Hi all,

I would like to get some input from you all. I have a report that includes 2 drop downs in HTML forms. What I would like to do is populate the second drop down without having to refresh the entire report itself, with the values filtered by the first selection. I am pretty sure it can be done but I can’t seem to get it to work.

Any suggestions?

 

-SET &CHKTIME = HHMMSS('A8');
-TYPE ========================================================================================================
-TYPE &CHKTIME STEP 1: Level 2 PMIX Locate                                          Hold as PMIXLEVTWO
-TYPE ========================================================================================================
-*
SQL
SELECT &OracleHint.EVAL
       PMX.HIER_ID                AS HIER_ID,
       PMX.LEVEL_2_CODE           AS PROD_LEVEL2,
       PMX.LEVEL_2_DESCR          AS PROD_DESCR2,
       PMX.LEVEL_4_CODE           AS PROD_LEVEL4,
       PMX.LEVEL_4_DESCR          AS PROD_DESCR4,

       SUM(COALESCE(PMD.DI_PROD_SALES_AMT,0) + COALESCE(PMD.CO_PROD_SALES_AMT,0) + COALESCE(PMD.PU_PROD_SALES_AMT,0) + COALESCE(PMD.MI_PROD_SALES_AMT,0) + COALESCE(PMD.MO_PROD_SALES_AMT,0) + COALESCE(PMD.MW_PROD_SALES_AMT,0) + COALESCE(PMD.KI_PROD_SALES_AMT,0) + COALESCE(PMD.KO_PROD_SALES_AMT,0)) AS PROD_SALES_AMT

  FROM ODSADM.FACT_DP_PMX_DETAIL_DAILY PMD,
       ODSADM.FACT_HIERARCHY_MV2       STR,
       ODSADM.ODS_HIERARCHY_DATA       PMX
 WHERE STR.STORE_ID           = PMD.STORE_ID
   AND STR.HIER_ID           IN (&HIERID)
   AND PMD.PROD_NUM           = PMX.LEVEL_6_CODE
   AND PMD.COMBO_COMPONENT_ID = PMX.HIER_ATTRIB_ID1
   AND PMX.HIER_ID           = 930
-*    OR  PMX.HIER_ID     BETWEEN 315000 AND 315999)
&DayWTDWhere
-*&PMIXLevelFilter
&DPFilter

&MV2STR_Region_WHERE.EVAL
&MV2STR_Division_WHERE.EVAL
&MV2STR_Area_WHERE.EVAL
&MV2STR_District_WHERE.EVAL
&MV2STR_Site_WHERE.EVAL

-INCLUDE mrd_common_attribute_filters

-INCLUDE mrd_common_entitlement_sql_str

GROUP BY PMX.HIER_ID,
         PMX.LEVEL_2_CODE,
         PMX.LEVEL_2_DESCR,
         PMX.LEVEL_4_CODE,
         PMX.LEVEL_4_DESCR


UNION ALL
SELECT  930 HIER_ID,
							'T00000' LEVEL_2_CODE,
							 'Select' LEVEL_2_DESCR,
							'T00000' LEVEL_4_CODE,
							 'Select' LEVEL_4_DESCR,
								1 PROD_SALES_AMT
FROM DUAL

;
TABLE
ON TABLE HOLD AS PMIXLOCDATA
END
-RUN
-*
-*
-IF &FOCERRNUM NE 0 GOTO PGM_ERROR;
-*
-PMIXRECS
-*
-*-SET &CHKTIME = HHMMSS('A8');
-*TABLE FILE PMIXLOCDATA
-*HEADING
-*"&FEXNAME"
-*"&CHKTIME STEP 1: Dump of the PMIXLOCDATA Hold File &CHKTIME"
-*PRINT *
-*WHERE RECORDLIMIT EQ 100
-*END
-*-RUN
-*-EXIT

-SET &CHKTIME = HHMMSS('A8');
-TYPE ========================================================================================================
-TYPE &CHKTIME STEP 2: Level 2 Sort                                         Hold as PMIXLOC1VAL
-TYPE ========================================================================================================


TABLE FILE PMIXLOCDATA
   PRINT DST.PROD_DESCR2
 BY PROD_LEVEL2
 WHERE PROD_SALES_AMT NE 0
ON TABLE HOLD AS PMIXLOC1VAL
END
-RUN



-SET &L1Lines = &LINES;

-*-SET &CHKTIME = HHMMSS('A8');
-*TABLE FILE PMIXLOC1VAL
-*HEADING
-*"&FEXNAME"
-*"&CHKTIME STEP 4: Dump of PMIXLOC1VAL Hold File"
-*PRINT *
-*WHERE RECORDLIMIT EQ 100
-*END
-*-EXIT



-SET &CHKTIME = HHMMSS('A8');
-TYPE ========================================================================================================
-TYPE &CHKTIME STEP 3: Level 2 & 4 Locate                                         Hold as PMIXDATA
-TYPE ========================================================================================================
-*




-* \/ 08/31/2015  Add new dropdown PMIX Locate
-*
-SET &PmixLoc1DropTop  = IF &FEXNAME IN ('mrd_daily_pmix')  THEN 'TOP: 110px;' ELSE 'TOP: 110px;' ;
-*
-SET &PmixLoc2DropTop  = IF &FEXNAME IN ('mrd_daily_pmix')  THEN 'TOP: 140px;' ELSE 'TOP: 140px;' ;

-*-SET &PmixLoc1TextLeft = IF &FEXNAME IN ('mrd_daily_pmix') THEN 'LEFT: 650px;' ELSE 'LEFT: 550px;';
-*-SET &PmixLoc1DropLeft = IF &FEXNAME IN ('mrd_daily_pmix') THEN 'LEFT: 700px;' ELSE 'LEFT: 600px;';
-*
-HTMLFORM BEGIN
<HTML>
<HEAD>
<TITLE>PMIX Loc1 Value dropdown</title>
</HEAD>
<BODY>

<FORM action="/ibi_apps/WFServlet" method="GET" name="Locate1ValDrop" target="_parent">
<INPUT type=hidden name="IBIAPP_app"     value="!IBI.AMP.XIBIAPP;">
<INPUT type=hidden name="IBIF_ex"        value="!IBI.AMP.FULLPAGEFEX;">
<INPUT type=hidden name="AllView"        value="!IBI.AMP.AllView;">
<INPUT type=hidden name="Area"           value="!IBI.AMP.Area;">
<INPUT type=hidden name="CalledFrom"     value="!IBI.AMP.CalledFrom;">
<INPUT type=hidden name="CheckView"      value="!IBI.AMP.CheckView;">
<INPUT type=hidden name="DashID"         value="!IBI.AMP.DashID;">
<INPUT type=hidden name="District"       value="!IBI.AMP.District;">
<INPUT type=hidden name="Division"       value="!IBI.AMP.Division;">
<INPUT type=hidden name="DLRORPCT"       value="!IBI.AMP.DLRORPCT;">
<INPUT type=hidden name="DPVIEW"         value="!IBI.AMP.DPVIEW;">
<INPUT type=hidden name="DRILLED"        value="YES">
<INPUT type=hidden name="DRILLVALUE"     value="!IBI.AMP.DRILLVALUE;">
<INPUT type=hidden name="FCatRpt"        value="!IBI.AMP.FCatRpt;">
<INPUT type=hidden name="FEXNAME"        value="!IBI.AMP.FEXNAME;">
<INPUT type=hidden name="FilterValue1"   value="!IBI.AMP.FilterValue1;">
<INPUT type=hidden name="FilterValue2"   value="!IBI.AMP.FilterValue2;">
<INPUT type=hidden name="FilterValue3"   value="!IBI.AMP.FilterValue3;">
<INPUT type=hidden name="FilterValue4"   value="!IBI.AMP.FilterValue4;">
<INPUT type=hidden name="FilterValue5"   value="!IBI.AMP.FilterValue5;">
<INPUT type=hidden name="FilterValue6"   value="!IBI.AMP.FilterValue6;">
<INPUT type=hidden name="FilterValue7"   value="!IBI.AMP.FilterValue7;">
<INPUT type=hidden name="FilterValue8"   value="!IBI.AMP.FilterValue8;">
<INPUT type=hidden name="FilterValue9"   value="!IBI.AMP.FilterValue9;">
<INPUT type=hidden name="FilterValue10"  value="!IBI.AMP.FilterValue10;">
<INPUT type=hidden name="FIRSTTIME"      value="N">
<INPUT type=hidden name="GLACCTID"       value="!IBI.AMP.GLACCTID;">
<INPUT type=hidden name="JobCode"        value="!IBI.AMP.JobCode;">
<INPUT type=hidden name="METRIC"         value="!IBI.AMP.METRIC;">
<INPUT type=hidden name="ONLEVEL"        value="!IBI.AMP.ONLEVEL;">
<INPUT type=hidden name="OModeType"      value="!IBI.AMP.OModeType;">
<INPUT type=hidden name="OModeLoc"       value="!IBI.AMP.OModeLoc;">
<INPUT type=hidden name="PARMDATE"       value="!IBI.AMP.PARMDATE;">
<INPUT type=hidden name="PayrollID"      value="!IBI.AMP.PayrollID;">
<INPUT type=hidden name="PERIOD"         value="!IBI.AMP.PERIOD;">
<INPUT type=hidden name="PMIXHier"       value="!IBI.AMP.PMIXHier;">
<INPUT type=hidden name="PPNLReport"     value="!IBI.AMP.PPNLReport;">
<INPUT type=hidden name="PMIX_Loc2"      value="!IBI.AMP.PMIX_Loc2;">
<INPUT type=hidden name="PrevMetric"     value="!IBI.AMP.PrevMetric;">
<INPUT type=hidden name="PrevSeq"        value="!IBI.AMP.PrevSeq;">
<INPUT type=hidden name="PRINTOPT"       value=" ">
<INPUT type=hidden name="PURPOSE"        value="!IBI.AMP.PURPOSE;">
<INPUT type=hidden name="Region"         value="!IBI.AMP.Region;">
<INPUT type=hidden name="RPTNMDP"        value="!IBI.AMP.RPTNMDP;">
<INPUT type=hidden name="RptTime"        value="!IBI.AMP.RptTime;">
<INPUT type=hidden name="RptTimeVal"     value="!IBI.AMP.RptTimeVal;">
<INPUT type=hidden name="RPTTYPE"        value="!IBI.AMP.RPTTYPE;">
<INPUT type=hidden name="SearchLevel"    value="!IBI.AMP.SearchLevel;">
<INPUT type=hidden name="SearchLevelVal" value="!IBI.AMP.SearchLevelVal;">
<INPUT type=hidden name="Section"        value="!IBI.AMP.Section;">
<INPUT type=hidden name="SelHIERID"      value="!IBI.AMP.SelHIERID;">
<INPUT type=hidden name="Site"           value="!IBI.AMP.Site;">
<INPUT type=hidden name="SortCol"        value="!IBI.AMP.SortCol;">
<INPUT type=hidden name="SortOrder"      value="!IBI.AMP.SortOrder;">
<INPUT type=hidden name="SummaryView"    value="!IBI.AMP.SummaryView;">
<INPUT type=hidden name="TRENDTYPE"      value="!IBI.AMP.TRENDTYPE;">
<INPUT type=hidden name="TrngList"       value="!IBI.AMP.TrngList;">
<INPUT type=hidden name="VIEW"           value="!IBI.AMP.VIEW;">
<INPUT type=hidden name="WFFMT"          value="!IBI.AMP.WFFMT;">

<INPUT  name=LocateValText   id=LocateValText     value="Locate:"                                       style="&PmixLoc1DropTop.EVAL LEFT: 650px; WIDTH:  50px; POSITION: absolute; Z-INDEX: 1;   FONT-FAMILY: Arial; FONT-SIZE: 8pt; BORDER-STYLE: none; BACKGROUND-COLOR: transparent;" tabIndex=1 maxLength=5 size=5>
<SELECT name=PMIX_Loc1       id=LocateVal1 onchange='LocateVal1OnChange(Locate1ValDrop.PMIX_Loc1);'     style="&PmixLoc1DropTop.EVAL LEFT: 700px; WIDTH: 200px; POSITION: absolute; Z-INDEX: 125; FONT-FAMILY: Arial; FONT-SIZE: 8pt;" tabIndex=125 ElementToPaste="true" operation>



-REPEAT PL1DROP FOR &L2Cntr FROM 1 TO &L1Lines
-READFILE PMIXLOC1VAL
-SET &PROD_DESCR2     = TRUNCATE(&PROD_DESCR2);
-SET &Prod2Length  = &PROD_LEVEL2.LENGTH;
-SET &PROD_LEVEL2 = TRIM('L', &PROD_LEVEL2, &Prod2Length, ' ', 1, 'A&Prod2Length.EVAL');
-SET &PROD_LEVEL2 = TRUNCATE(&PROD_LEVEL2);
-SET &Lev2Sel   = IF &PMIX_Loc1 EQ &PROD_LEVEL2 THEN 'SELECTED' ELSE '';
<OPTION value="&PROD_LEVEL2.EVAL" &Lev2Sel.EVAL displaytext="&PROD_DESCR2.EVAL">&PROD_DESCR2.EVAL</OPTION>
-PL1DROP
-CLOSE PMIXLOC1VAL


</SELECT>
</FORM>

<SCRIPT LANGUAGE="javascript">
<!--
function LocateVal1OnChange(dropdown)
{

    Locate1ValDrop.submit()
;
}
//-->
</SCRIPT>

</BODY>
</HTML>
-HTMLFORM END



TABLE FILE PMIXLOCDATA
PRINT DST.PROD_DESCR4
BY PROD_LEVEL4
WHERE PROD_SALES_AMT NE 0
WHERE PROD_LEVEL2 IN ('&PMIX_Loc1.EVAL', 'T00000')
ON TABLE HOLD AS PMIXLOC2VAL
END
-RUN
-*
-*-*
-*-*
-SET &L2Lines = &LINES;

-*-SET &CHKTIME = HHMMSS('A8');
-*TABLE FILE PMIXLOC2VAL
-*HEADING
-*"&FEXNAME"
-*"&CHKTIME STEP 4: Dump of PMIXLOC2VAL Hold File"
-*PRINT *
-*WHERE RECORDLIMIT EQ 100
-*END
-*-EXIT



-*-*
-*-* \/ 08/31/2015  Add new dropdown PMIX Locate
-*-*
-HTMLFORM BEGIN
<HTML>
<HEAD>
<TITLE>PMIX Loc2 Value dropdown</title>
</HEAD>
<BODY>

<FORM action="/ibi_apps/WFServlet" method="GET" name="Locate2ValDrop" target="_parent">
<INPUT type=hidden name="IBIAPP_app"     value="!IBI.AMP.XIBIAPP;">
<INPUT type=hidden name="IBIF_ex"        value="!IBI.AMP.FULLPAGEFEX;">
<INPUT type=hidden name="AllView"        value="!IBI.AMP.AllView;">
<INPUT type=hidden name="Area"           value="!IBI.AMP.Area;">
<INPUT type=hidden name="CalledFrom"     value="!IBI.AMP.CalledFrom;">
<INPUT type=hidden name="CheckView"      value="!IBI.AMP.CheckView;">
<INPUT type=hidden name="DashID"         value="!IBI.AMP.DashID;">
<INPUT type=hidden name="District"       value="!IBI.AMP.District;">
<INPUT type=hidden name="Division"       value="!IBI.AMP.Division;">
<INPUT type=hidden name="DLRORPCT"       value="!IBI.AMP.DLRORPCT;">
<INPUT type=hidden name="DPVIEW"         value="!IBI.AMP.DPVIEW;">
<INPUT type=hidden name="DRILLED"        value="YES">
<INPUT type=hidden name="DRILLVALUE"     value="!IBI.AMP.DRILLVALUE;">
<INPUT type=hidden name="FCatRpt"        value="!IBI.AMP.FCatRpt;">
<INPUT type=hidden name="FEXNAME"        value="!IBI.AMP.FEXNAME;">
<INPUT type=hidden name="FilterValue1"   value="!IBI.AMP.FilterValue1;">
<INPUT type=hidden name="FilterValue2"   value="!IBI.AMP.FilterValue2;">
<INPUT type=hidden name="FilterValue3"   value="!IBI.AMP.FilterValue3;">
<INPUT type=hidden name="FilterValue4"   value="!IBI.AMP.FilterValue4;">
<INPUT type=hidden name="FilterValue5"   value="!IBI.AMP.FilterValue5;">
<INPUT type=hidden name="FilterValue6"   value="!IBI.AMP.FilterValue6;">
<INPUT type=hidden name="FilterValue7"   value="!IBI.AMP.FilterValue7;">
<INPUT type=hidden name="FilterValue8"   value="!IBI.AMP.FilterValue8;">
<INPUT type=hidden name="FilterValue9"   value="!IBI.AMP.FilterValue9;">
<INPUT type=hidden name="FilterValue10"  value="!IBI.AMP.FilterValue10;">
<INPUT type=hidden name="FIRSTTIME"      value="N">
<INPUT type=hidden name="GLACCTID"       value="!IBI.AMP.GLACCTID;">
<INPUT type=hidden name="JobCode"        value="!IBI.AMP.JobCode;">
<INPUT type=hidden name="METRIC"         value="!IBI.AMP.METRIC;">
<INPUT type=hidden name="ONLEVEL"        value="!IBI.AMP.ONLEVEL;">
<INPUT type=hidden name="OModeType"      value="!IBI.AMP.OModeType;">
<INPUT type=hidden name="OModeLoc"       value="!IBI.AMP.OModeLoc;">
<INPUT type=hidden name="PARMDATE"       value="!IBI.AMP.PARMDATE;">
<INPUT type=hidden name="PayrollID"      value="!IBI.AMP.PayrollID;">
<INPUT type=hidden name="PERIOD"         value="!IBI.AMP.PERIOD;">
<INPUT type=hidden name="PPNLReport"     value="!IBI.AMP.PPNLReport;">
<INPUT type=hidden name="PMIXHier"       value="!IBI.AMP.PMIXHier;">
<INPUT type=hidden name="PMIX_Loc1"      value="!IBI.AMP.PMIX_Loc1;">
<INPUT type=hidden name="PrevMetric"     value="!IBI.AMP.PrevMetric;">
<INPUT type=hidden name="PrevSeq"        value="!IBI.AMP.PrevSeq;">
<INPUT type=hidden name="PRINTOPT"       value=" ">
<INPUT type=hidden name="PURPOSE"        value="!IBI.AMP.PURPOSE;">
<INPUT type=hidden name="Region"         value="!IBI.AMP.Region;">
<INPUT type=hidden name="RPTNMDP"        value="!IBI.AMP.RPTNMDP;">
<INPUT type=hidden name="RptTime"        value="!IBI.AMP.RptTime;">
<INPUT type=hidden name="RptTimeVal"     value="!IBI.AMP.RptTimeVal;">
<INPUT type=hidden name="RPTTYPE"        value="!IBI.AMP.RPTTYPE;">
<INPUT type=hidden name="SearchLevel"    value="!IBI.AMP.SearchLevel;">
<INPUT type=hidden name="SearchLevelVal" value="!IBI.AMP.SearchLevelVal;">
<INPUT type=hidden name="Section"        value="!IBI.AMP.Section;">
<INPUT type=hidden name="SelHIERID"      value="!IBI.AMP.SelHIERID;">
<INPUT type=hidden name="Site"           value="!IBI.AMP.Site;">
<INPUT type=hidden name="SortCol"        value="!IBI.AMP.SortCol;">
<INPUT type=hidden name="SortOrder"      value="!IBI.AMP.SortOrder;">
<INPUT type=hidden name="SummaryView"    value="!IBI.AMP.SummaryView;">
<INPUT type=hidden name="TRENDTYPE"      value="!IBI.AMP.TRENDTYPE;">
<INPUT type=hidden name="TrngList"       value="!IBI.AMP.TrngList;">
<INPUT type=hidden name="VIEW"           value="!IBI.AMP.VIEW;">
<INPUT type=hidden name="WFFMT"          value="!IBI.AMP.WFFMT;">

<INPUT  name=LocateValText id=LocateValText     value="Product:"                                            style="&PmixLoc2DropTop.EVAL LEFT: 650px; WIDTH:  650px; POSITION: absolute; Z-INDEX: 1;   FONT-FAMILY: Arial; FONT-SIZE: 8pt; BORDER-STYLE: none; BACKGROUND-COLOR: transparent;" tabIndex=1 maxLength=5 size=5>
<SELECT name=PMIX_Loc2       id=LocateVal1ComboBox onchange='LocateVal2OnChange(Locate2ValDrop.PMIX_Loc2);' style="&PmixLoc2DropTop.EVAL LEFT: 700px; WIDTH: 200px; POSITION: absolute; Z-INDEX: 125; FONT-FAMILY: Arial; FONT-SIZE: 8pt;" tabIndex=125 ElementToPaste="true" operation>

-REPEAT PL2DROP FOR &L2Cntr FROM 1 TO &L2Lines
-READFILE PMIXLOC2VAL
-SET &PROD_DESCR4     = TRUNCATE(&PROD_DESCR4);
-SET &Prod4Length  = &PROD_LEVEL4.LENGTH;
-SET &PROD_LEVEL4 = TRIM('L', &PROD_LEVEL4, &Prod4Length, ' ', 1, 'A&Prod4Length.EVAL');
-SET &PROD_LEVEL4 = TRUNCATE(&PROD_LEVEL4);
-SET &Lev4Sel   = IF &PMIX_Loc2 EQ &PROD_LEVEL4 THEN 'SELECTED' ELSE '';
<OPTION value="&PROD_LEVEL4.EVAL" &Lev4Sel.EVAL displaytext="&PROD_DESCR4.EVAL">&PROD_DESCR4.EVAL</OPTION>
-PL2DROP
-CLOSE PMIXLOC1VAL


-*-EXIT


</SELECT>
</FORM>

<SCRIPT LANGUAGE="javascript">
<!--
function LocateVal2OnChange(dropdown)
{
    Locate2ValDrop.submit();
}
//-->
</SCRIPT>

</BODY>
</HTML>
-HTMLFORM END

 

This message has been edited. Last edited by: <Kathryn Henning>,

Look into chaining controls (drop-downs in your case) and external population requests (to populate the controls) within the HTML Composer/Canvas. There are recorded webinars going over this on IBI's website where they dub such "magical filters".

Chaining is exactly what I want, but this dashboard is a little more complicated then that and not built using Composer.

I am in process of trying to build a new html page with these controls chained and try and pass the correct parameter into my existing HTML.

So, you are basically doing what I and probably others are doing and attempting to avoid IBI's HTML Composer/Canvas if at all possible, due to the obvious. lol There's probably a web standards way of doing what you want, but I haven't ventured that far with my experience as of yet. But I would love to hear back from you here if you figure out a way to get this to work. Good luck!

Look into using jQuery Ajax. If you are doing this yourself then you will need to understand and be able to code the read of the DOM object and then build the necessary URL. Once you have the returned data, you will also need to be able to code the delivery to your new object.

It can be very easy but depends upon your understanding and knowledge.

T

quote:

It can be very easy but depends upon your understanding and knowledge.

Or difficult with the lack there of.


I changed approach. I created a new HTML with Composer, set the chaining to get the the desired affect.

Now I know I can't take a JS var and turn it into a Webfocus var, but I was poking around and it may look like that you can take that JS var and stick it into a hiddeninput on the html and then grab the value from that hiddeninput and turn that into a Webfocus var.

If that is true, then I was thinking I could stick my new HTML using a -INCLUDE in a HTMLFORM inside my procedure and try and grab the value I need.

I haven't looked at it today, but I tinkered with it late yesterday and I was having trouble getting that value from my new HTML into my current program.