[SOLVED]Population of a Parameter with a Dropdown Using DM and Three Sections of Code

FocalPoint Forum Community:

I've done some research in manuals and herein on the forum. I know this can be done.

I have a fex with two sections. One section provides aggregated data for a region; the second section provides the same for a single store.

Within the HTML file, I use a static parameter and chain it to store names to limit the dropdown to only stores in that region. I also use FOC_Null with All Stores to obtain the aggregated totals.

I need a third section which will list all stores in a region and provide data for each store instead of aggregated totals for the region. So, in essence, it's a listing of all stores.

I've pulled the fex apart and tried each section. Each section works independently, and again, the first two sections work together.

I think the best solution is to write an independent fex and call it relying on the dropdown list. I already use a fex as an explicit parameter to limit my years. So, I think the same principle should apply here, but my execution thus far has been a little elusive.

Here's my code:

-IF &Store EQ '_FOC_NULL' THEN GOTO ALL ELSE IF &Store EQ '&Store' THEN GOTO STORE ELSE GOTO STORELIST;

-ALL
DEFINE FILE SALES
YRSET/I4 = EDIT(QUARTER_FISCAL_YEAR);
YR /YY = YRSET;
SORT_MODEL/A46 = IF MODEL EQ 'Subaru'  THEN '    Subaru' ELSE
	         IF MODEL EQ 'Honda' THEN '   Honda' ELSE
	         IF MODEL EQ 'Toyota'   THEN '  Toyota'    ELSE MODEL ;
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR-5;

TABLE FILE SALES

SUM
SUM.SALES.SALES.SALESQTY 
BY SALES.SALES.REGION_NAME
BY  SALES.SALES.QUARTER_FISCAL_YEAR
BY  SALES.SALES.YR
BY  LOWEST SALES.SALES.SORT_MODEL
WHERE ( SALES.SALES.SALES_PERIOD EQ 'Fiscal Year' )
WHERE ( SALES.SALES.REGION_NAME EQ '®ION_NAME.(<Southwest,Southwest>).Region:.' );
WHERE (SALES.SALES.YR GE &MINYR);
WHERE (SALES.SALES.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

DEFINE FILE EXTDATA
QUARTER_FISCAL_YEAR/A100 =EDIT(YR)
END

TABLE FILE EXTDATA
SUM
SALESQTY
BY REGION_NAME
BY QUARTER_FISCAL_YEAR
BY LOWEST SORT_MODEL
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS REPINPUT
ON TABLE SET STYLE *
ENDSTYLE
END

-SET &input   = 'repinput';
-SET &output  = 'finaldata';
-SET &dim1    = 'REGION_NAME';
-SET &dim1_id = 'SORT_MODEL';
-SET &dim2    = 'QUARTER_FISCAL_YEAR';
-SET &measure = 'SALESQTY';
-INCLUDE small_cell_protection file

TABLE FILE finaldata

ON TABLE SUBHEAD
"Julie's Stores"
"Sales by Model"
"Fiscal Years &MINYR - &MAXYR"
"All Southwest Region"
ON TABLE SUBFOOT
"Source: Sales Reports          "

    SUM &measure
	 BY &dim1._SORT NOPRINT
	 BY &dim1   AS 'Region'
	 BY LOWEST &dim1_id   AS 'Model'
 ACROSS &dim2      AS 'Fiscal Year'
 WHERE &dim2 NE 'Total'
ON &dim1  SUBFOOT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML Web Document (HTML),HTML>,<Excel XLSX (XLSX),XLSX>).Output type.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN
-GOTO LBL_END;

-STORE
DEFINE FILE SALES
YRSET/I4 = EDIT(QUARTER_FISCAL_YEAR);
YR /YY = YRSET;
SORT_MODEL/A46 = IF MODEL EQ 'Subaru'  THEN '    Subaru' ELSE
	         IF MODEL EQ 'Honda' THEN '   Honda' ELSE
	         IF MODEL EQ 'Toyota'   THEN '  Toyota'    ELSE MODEL ;
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR-5;

TABLE FILE SALES
SUM
SUM.SALES.SALES.SALESQTY 
BY  SALES.SALES.Store
BY  SALES.SALES.QUARTER_FISCAL_YEAR
BY  SALES.SALES.YR
BY  LOWEST SALES.SALES.SORT_MODEL
WHERE ( SALES.SALES.SALES_PERIOD EQ 'Fiscal Year' )
WHERE ( SALES.SALES.REGION_NAME EQ '®ION_NAME.(<Southwest,Southwest>).Region:.' );
WHERE ( SALES.SALES.Store EQ '&Store.(FIND SALES.SALES.Store IN SALES).Store:.' );
WHERE (SALES.SALES.YR GE &MINYR);
WHERE (SALES.SALES.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

DEFINE FILE EXTDATA
QUARTER_FISCAL_YEAR/A100 =EDIT(YR)
END

TABLE FILE EXTDATA
SUM
SALESQTY
BY Store
BY QUARTER_FISCAL_YEAR
BY LOWEST SORT_MODEL
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS REPINPUT
ON TABLE SET STYLE *
ENDSTYLE
END

-SET &input   = 'repinput';
-SET &output  = 'finaldata';
-SET &dim1    = 'Store';
-SET &dim1_id = 'SORT_MODEL';
-SET &dim2    = 'QUARTER_FISCAL_YEAR';
-SET &measure = 'SALESQTY';
-INCLUDE small_cell_protection

TABLE FILE finaldata

ON TABLE SUBHEAD
"Julie's Stores"
"Sales by Model"
"Fiscal Years &MINYR - &MAXYR"
"&Store"
ON TABLE SUBFOOT
"Source: Sales Data"
" "


 SUM &measure
	 BY &dim1._SORT NOPRINT
	 BY &dim1      AS 'Store'
	 BY LOWEST &dim1_id   AS 'Model'
-* ACROSS &dim2._SORT NOPRINT
 ACROSS &dim2 AS 'Fiscal Year'
 WHERE &dim2 NE 'Total'
 ON &dim1  SUBFOOT
ON TABLE SET PAGE-NUM NOLEAD
-*ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML Web Document (HTML),HTML>,<Excel XLSX (XLSX),XLSX>).Output type.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END
-RUN
-GOTO LBL_END;

-STORELIST
DEFINE FILE SALES
YRSET/I4 = EDIT(QUARTER_FISCAL_YEAR);
YR /YY = YRSET;
SORT_MODEL/A46 = IF MODEL EQ 'Subaru'  THEN '    Subaru' ELSE
	         IF MODEL EQ 'Honda' THEN '   Honda' ELSE
	         IF MODEL EQ 'Toyota'   THEN '  Toyota'    ELSE MODEL ;
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR-5;

TABLE FILE SALES

SUM
SUM.SALES.SALES.SALESQTY AS 'SALESQTY'
BY SALES.SALES.REGION_NAME
BY  SALES.SALES.QUARTER_FISCAL_YEAR
BY  SALES.SALES.YR
BY  LOWEST SALES.SALES.SORT_MODEL
WHERE ( SALES.SALES.SALES_PERIOD EQ 'Fiscal Year' )
WHERE ( SALES.SALES.REGION_NAME EQ '®ION_NAME.(<Southwest,Southwest>).Region:.' );
WHERE (SALES.SALES.YR GE &MINYR);
WHERE (SALES.SALES.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

DEFINE FILE EXTDATA
QUARTER_FISCAL_YEAR/A100 =EDIT(YR)
END

TABLE FILE EXTDATA
SUM
SALESQTY
BY REGION_NAME
BY QUARTER_FISCAL_YEAR
BY LOWEST SORT_MODEL
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS REPINPUT
ON TABLE SET STYLE *
ENDSTYLE
END

-* Some choices are optional.
-SET &input   = 'repinput';
-SET &output  = 'finaldata';
-SET &dim1    = 'REGION_NAME';
-SET &dim1_id = 'SORT_MODEL';
-SET &dim2    = 'QUARTER_FISCAL_YEAR';
-SET &measure = 'SALESQTY';
-INCLUDE small_cell_protection

TABLE FILE finaldata

ON TABLE SUBHEAD
"Julie's Stores"
"Sales by Model"
"Fiscal Years &MINYR - &MAXYR"
"All Southwest Stores"
ON TABLE SUBFOOT
"Source: Sales Reports          "
" "

    SUM &measure
	 BY &dim1._SORT NOPRINT
	 BY &dim1   AS 'Region'
	 BY LOWEST &dim1_id   AS 'Model'
 ACROSS &dim2      AS 'Fiscal Year'
 WHERE &dim2 NE 'Total'
ON &dim1  SUBFOOT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML Web Document (HTML),HTML>,<Excel XLSX (XLSX),XLSX>).Output type.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN
-LBL_END;

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

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

MartinY

Virtuoso

posted

Hide Post

From what I can understand it's kind of Adhoc report that will change depending on selection made.

So consider the following sample where you can have parameter almost every where so you only need one piece of code (report) to perform all layout.
The below sample is not as complete as I normally do and may not take in consideration all cases, but just to give you an idea.

-* Input parameters where &CNTRY is a single select and &CAR is a multi-selection

-* Case 1
-*-DEFAULTH &CNTRY = _FOC_NULL
-*-DEFAULTH &CAR   = _FOC_NULL

-* Case 2
-DEFAULTH &CNTRY = ENGLAND
-DEFAULTH &CAR   = '''JAGUAR'' OR ''TRIUMPH'''

-* Case 3
-*-DEFAULTH &CNTRY = ENGLAND
-*-DEFAULTH &CAR   = '''JAGUAR'''

-SET &BY01     = IF &CNTRY EQ       _FOC_NULL THEN 'COUNTRY' ELSE _FOC_NULL;
-SET &BY02     = IF &CNTRY EQ       _FOC_NULL THEN 'CAR'
-           ELSE IF &CAR   EQ       _FOC_NULL THEN 'CAR'
-           ELSE IF &CAR   CONTAINS 'OR'      THEN 'CAR'
-           ELSE                                   _FOC_NULL;
-SET &BY01T    = 'Region';
-SET &BY02T    = 'Car';
-SET &RPTTITLE = IF &CNTRY EQ            _FOC_NULL THEN 'Report By Country'
-           ELSE IF &CAR   EQ            _FOC_NULL THEN 'Report By All Car'
-           ELSE IF &CAR   CONTAINS 'OR'           THEN 'Report By Car For &CNTRY.EVAL'
-           ELSE                                        'Report For Selected Car <CAR';

TABLE FILE CAR
SUM SALES
BY &BY01 AS '&BY01T'
BY &BY02 AS '&BY02T'
WHERE COUNTRY EQ &CNTRY.QUOTEDSTRING;
WHERE CAR     EQ &CAR;

HEADING
"&RPTTITLE"

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN

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, 2013

Ignored post by MartinY posted

Show Post

JulieA

Platinum Member

posted

Hide Post

Yes, Martin, you understood correctly. The user makes a selection. The report's layout changes based on that selection.

The missing element (and maybe I didn't explain it well earlier) is the third section of code is a third and different layout but the name I need in the user dropdown list is not a choice listed in our synonym. It's similar to Section 1, but the layout is different.

What I mean is this:

Section 1 = STORE = _FOC_NULL
Section 2 = STORE = &STORE
Section 3 = STORE = How can I name this section of code as a user dropdown selection? STORELIST?

If I could get that in the dropdown as a user choice, I'd be set. My IF-THEN-ELSE statement would work.

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

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Show Post

FP Mod Chuck

Virtuoso

posted

Hide Post

Julie

The only way I can think of is to make that drop down list a static list with a 'No Selection' entry (which translates to _FOC_NULL) all the valid stores and add STORELIST as an entry.

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats

Posts: 2127 | Location: Customer Support | Registered: April 12, 2005

Ignored post by FP Mod Chuck posted

Show Post

MartinY

Virtuoso

posted

Hide Post

JulieA,

I already have gave you the solution.

Why would you need an option in the drop down list that will tell the user to provide a store list. He/she only have to select multiple one.

If you have you dropdown as multi select with All as an option, when user select All it's section 1 (WHERE STORE EQ _FOC_NULL), if user select only one store it's section 2 (WHERE STORE EQ &STORE), if user select multiple store (a store list) it's section 3 (WHERE STORE EQ &STORE).

There is no difference in the WHERE clause (except for quotes if they are needed. Must have the properties of the dropdown control set to multiple and Add Quotes = Yes) if the parameter do include one or multiple store.
The sample code that I provide you manage the multiple selection of store.

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, 2013

Ignored post by MartinY posted

Show Post

JulieA

Platinum Member

posted

Hide Post

Martin:

Thank you for the solution. I am not sure I articulated the business case for what I needed as well as I should have in terms of why I need the whole listing, that is all stores in one list; however, we finally figured out a solution that is working well. We wrote a separate fex and sorted it appropriately for all three sections of code. Then, we bound the HTML file to that for that parameter.

Thanks for the help. We are going to keep your solution in mind for a future business challenge.

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Show Post

MartinY

Virtuoso

posted

Hide Post

Your challenge was not only with the report but with the drop list where you want to be able to select "All Stores" option within a specific chosen Region.
Am I right ?

This can be perform in several ways such as using two fex to populate each list (Region and Store) and then have js with event test to load and update them : "manual chaining". Not easy to explain here without providing whole code sample.
Or with one fex that include all possible values and use the chaining feature. But you need to have the proper XML data. Not elegant but it works.
With the below, I can select on Country (Region) and select "All" Car (Store) for this country without having to select them one by one. Then passing CODE_CNTR and CODE_CAR field to the variables (in order &CNTRY, &CAR) my previous adhoc report will work. The LST_CNTR and LST_CAR field are for display purpose of the control where CODE_CNTR and CODE_CAR for value.

DEFINE FILE CAR
TRI1    /I1    = 1;
TRI2    /I1    = 2;
TRIA    /I1    = 0;
CODE1   /A16V  = COUNTRY;
CODE2   /A16V  = CAR;
CODEA   /A16V  = '_FOC_NULL';
LST1    /A16V  = LCWORD(16, COUNTRY, 'A16V');
LST2    /A16V  = LCWORD(16, CAR,     'A16V');
LSTA    /A16V  = 'All';
END

TABLE FILE CAR
SUM  LST1
     LSTA    AS 'LST2'
BY TRI1      AS 'TRI2'
BY COUNTRY 
BY CODE1
BY CODEA     AS 'CODE2'
ON TABLE HOLD AS CARALL
END
-RUN

TABLE FILE CAR
SUM  LSTA    AS 'LST1'
     LSTA    AS 'LST2'
BY TRIA      AS 'TRI2'
BY COUNTRY
BY CODEA     AS 'CODE1'
BY CODEA     AS 'CODE2'
WHERE READLIMIT   EQ 1;
WHERE RECORDLIMIT EQ 1;
ON TABLE HOLD AS ROWALL
END
-RUN

TABLE FILE CAR
SUM  LST1    AS 'LST_CNTR'
     LST2    AS 'LST_CAR'
BY COUNTRY   NOPRINT
BY TRI2      NOPRINT
BY CODE1     AS 'CODE_CNTR'
BY CODE2     AS 'CODE_CAR'
ON TABLE PCHOLD FORMAT XML
MORE
FILE ROWALL
MORE
FILE CARALL
END
-RUN

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, 2013

Ignored post by MartinY posted

Show Post

JulieA

Platinum Member

posted

Hide Post

Thanks, Martin, for the additional information. My challenge was more or less getting the all store listing section of the code to populate from the dropdown. We finally used a fex to sort the stores, a summary (aggregated) of all stores and a listing of all stores. That seemed to work just fine. I do appreciate all your suggestions and will remember them in the future. Thanks so much!

WebFocus 8.2.04
WebFocus 8.2.04

Posts: 191 | Registered: September 18, 2015

Ignored post by JulieA posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

[SOLVED]Population of a Parameter with a Dropdown Using DM and Three Sections of Code

Focal Point | Privacy Statement | Terms of Service