[CLOSED] Guided Ad Hoc Report with 1 list of fields

Elizabeth,

Your users want one list box. How will they differentiate between dimensions and measures? Or, do you mean that you know which fields are dimensions and which fields are measures?
In the latter case, let them choose the fields and then you build your TABLE request by separating the dimensions from the measures and prefix the dimensions with BYs.

Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

j.gross

Virtuoso

posted

Even if the report code can distinguish between dimensions and measures, you still need to determine the priority amongst the former, and the column-order of the latter.

Perhaps a dual listbox control, so the user can move the columns over in the order corresponding to the desired report configuration.

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Platinum Member

posted

We use double list box controls on html pages with with Report variables we name one &MEASURES and the other &SORT and there we prompt for specific columnr variables

When you refernce this as an extrernal report the HTML page will aske what type of controls you wish to buold abd we choose double listbox for our measures

it looks something like this below but you only have to work with a single parameter.

PRINT
&MEASURES.(AND(,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,)).Measures.
BY &SORTA.(BY(,,,,,,,,,,,)).Sort1.
BY &SORTB.(BY(,,,,,,,,,,,)).Sort1.
BY &SORTC.(BY(,,,,,,,,,,,)).Sort1.
HEADING

"Undergraduate Admissions Profile Report"
"For Term &TERM_CODE_KEY Student Type &STYP_CODE "
"Record Count
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.TERM_CODE_KEY EQ &TERM_CODE_KEY.(OR(FIND SUBTERM.SEG01.STVTERM_CODE,SUBTERM.SEG01.STVTERM_DESC IN subterm)).TERM_CODE_KEY.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.STYP_CODE EQ &STYP_CODE.(OR(FIND STVSTYP.STVSTYP.STVSTYP_CODE,STVSTYP.STVSTYP.STVSTYP_DESC IN stvstyp)).STYP_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.ADMT_CODE EQ &ADMT_CODE.(OR(FIND STVADMT.STVADMT.STVADMT_CODE,STVADMT.STVADMT.STVADMT_DESC IN stvadmt)).ADMT_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.DECEASED_IND EQ MISSING;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.APST_CODE EQ &APST_CODE.(OR(FIND STVAPST.STVAPST.STVAPST_CODE,STVAPST.STVAPST.STVAPST_DESC IN stvapst)).APST_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.COLL_CODE1 EQ &COLL_CODE1.(OR(FIND STVCOLL.STVCOLL.STVCOLL_CODE,STVCOLL.STVCOLL.STVCOLL_DESC IN stvcoll)).COLL_CODE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.MAJR_CODE1 EQ &MAJR_CODE1.(OR(FIND STVMAJR.STVMAJR.STVMAJR_CODE,STVMAJR.STVMAJR.STVMAJR_DESC IN stvmajr)).MAJR_CODE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.STATE1 EQ &STATE1.(OR(FIND STVSTAT.STVSTAT.STVSTAT_CODE,STVSTAT.STVSTAT.STVSTAT_DESC IN stvstat)).STATE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.APDC_CODE1 EQ &STVAPDC_CODE.(OR(FIND STVAPDC.STVAPDC.STVAPDC_CODE,STVAPDC.STVAPDC.STVAPDC_DESC IN stvapdc)).STVAPDC_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.LEVL_CODE NE 'G';

809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML

Posts: 171 | Registered: April 28, 2008

Ignored post by Geoff Fish posted

Platinum Member

posted

quote:
Originally posted by Geoff Fish:
We use double list box controls on html pages with with Report variables we name one &MEASURES and the other &SORT and there we prompt for specific columnr variables

When you refernce this as an extrernal report the HTML page will aske what type of controls you wish to build and we choose double listbox for our measures

it looks something like this below but you only have to work with a single parameter.

PRINT
&MEASURES.(AND(,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,)).Measures.
BY &SORTA.(BY(,,,,,,,,,,,)).Sort1.
BY &SORTB.(BY(,,,,,,,,,,,)).Sort1.
BY &SORTC.(BY(,,,,,,,,,,,)).Sort1.
HEADING

"Undergraduate Admissions Profile Report"
"For Term &TERM_CODE_KEY Student Type &STYP_CODE "
"Record Count
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.TERM_CODE_KEY EQ &TERM_CODE_KEY.(OR(FIND SUBTERM.SEG01.STVTERM_CODE,SUBTERM.SEG01.STVTERM_DESC IN subterm)).TERM_CODE_KEY.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.STYP_CODE EQ &STYP_CODE.(OR(FIND STVSTYP.STVSTYP.STVSTYP_CODE,STVSTYP.STVSTYP.STVSTYP_DESC IN stvstyp)).STYP_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.ADMT_CODE EQ &ADMT_CODE.(OR(FIND STVADMT.STVADMT.STVADMT_CODE,STVADMT.STVADMT.STVADMT_DESC IN stvadmt)).ADMT_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.DECEASED_IND EQ MISSING;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.APST_CODE EQ &APST_CODE.(OR(FIND STVAPST.STVAPST.STVAPST_CODE,STVAPST.STVAPST.STVAPST_DESC IN stvapst)).APST_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.COLL_CODE1 EQ &COLL_CODE1.(OR(FIND STVCOLL.STVCOLL.STVCOLL_CODE,STVCOLL.STVCOLL.STVCOLL_DESC IN stvcoll)).COLL_CODE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.MAJR_CODE1 EQ &MAJR_CODE1.(OR(FIND STVMAJR.STVMAJR.STVMAJR_CODE,STVMAJR.STVMAJR.STVMAJR_DESC IN stvmajr)).MAJR_CODE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.STATE1 EQ &STATE1.(OR(FIND STVSTAT.STVSTAT.STVSTAT_CODE,STVSTAT.STVSTAT.STVSTAT_DESC IN stvstat)).STATE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.APDC_CODE1 EQ &STVAPDC_CODE.(OR(FIND STVAPDC.STVAPDC.STVAPDC_CODE,STVAPDC.STVAPDC.STVAPDC_DESC IN stvapdc)).STVAPDC_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.LEVL_CODE NE 'G';

809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML

Posts: 171 | Registered: April 28, 2008

Ignored post by Geoff Fish posted

Platinum Member

posted

quote:
Originally posted by Geoff Fish:
quote:
Originally posted by Geoff Fish:
We use double list box controls on html pages with with Report variables we name one &MEASURES and the other &SORT and there we prompt for specific columnr variables

When you refernce this as an extrernal report the HTML page will aske what type of controls you wish to build and we choose double listbox for our measures

it looks something like this below but you only have to work with a single parameter. this allows a lot of flexiibility and gives the use thousands of choices in what columns they get to print , how they are sortrd etc.

PRINT
&MEASURES.(AND(,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,)).Measures.
BY &SORTA.(BY(,,,,,,,,,,,)).Sort1.
BY &SORTB.(BY(,,,,,,,,,,,)).Sort1.
BY &SORTC.(BY(,,,,,,,,,,,)).Sort1.
HEADING

"Undergraduate Admissions Profile Report"
"For Term &TERM_CODE_KEY Student Type &STYP_CODE "
"Record Count
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.TERM_CODE_KEY EQ &TERM_CODE_KEY.(OR(FIND SUBTERM.SEG01.STVTERM_CODE,SUBTERM.SEG01.STVTERM_DESC IN subterm)).TERM_CODE_KEY.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.STYP_CODE EQ &STYP_CODE.(OR(FIND STVSTYP.STVSTYP.STVSTYP_CODE,STVSTYP.STVSTYP.STVSTYP_DESC IN stvstyp)).STYP_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.ADMT_CODE EQ &ADMT_CODE.(OR(FIND STVADMT.STVADMT.STVADMT_CODE,STVADMT.STVADMT.STVADMT_DESC IN stvadmt)).ADMT_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.DECEASED_IND EQ MISSING;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.APST_CODE EQ &APST_CODE.(OR(FIND STVAPST.STVAPST.STVAPST_CODE,STVAPST.STVAPST.STVAPST_DESC IN stvapst)).APST_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.COLL_CODE1 EQ &COLL_CODE1.(OR(FIND STVCOLL.STVCOLL.STVCOLL_CODE,STVCOLL.STVCOLL.STVCOLL_DESC IN stvcoll)).COLL_CODE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.MAJR_CODE1 EQ &MAJR_CODE1.(OR(FIND STVMAJR.STVMAJR.STVMAJR_CODE,STVMAJR.STVMAJR.STVMAJR_DESC IN stvmajr)).MAJR_CODE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.STATE1 EQ &STATE1.(OR(FIND STVSTAT.STVSTAT.STVSTAT_CODE,STVSTAT.STVSTAT.STVSTAT_DESC IN stvstat)).STATE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.APDC_CODE1 EQ &STVAPDC_CODE.(OR(FIND STVAPDC.STVAPDC.STVAPDC_CODE,STVAPDC.STVAPDC.STVAPDC_DESC IN stvapdc)).STVAPDC_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.LEVL_CODE NE 'G';

809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML

Posts: 171 | Registered: April 28, 2008

Ignored post by Geoff Fish posted

Platinum Member

posted

quote:
Originally posted by Geoff Fish:
quote:
Originally posted by Geoff Fish:
quote:
Originally posted by Geoff Fish:
We use double list box controls on html pages with with Report variables we name one &MEASURES and the other &SORT and there we prompt for specific columnr variables

When you refernce this as an extrernal report the HTML page will aske what type of controls you wish to build and we choose double listbox for our measures

it looks something like this below but you only have to work with a single parameter. this allows a lot of flexiibility and gives the use thousands of choices in what columns they get to print , how they are sorted etc. there are about 30 possibilities to be selcted and you can sort the order in which the fields display. we also preselect a couple with the HTML GUI

PRINT
&MEASURES.(AND(,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,)).Measures.
BY &SORTA.(BY(,,,,,,,,,,,)).Sort1.
BY &SORTB.(BY(,,,,,,,,,,,)).Sort1.
BY &SORTC.(BY(,,,,,,,,,,,)).Sort1.
HEADING

"Undergraduate Admissions Profile Report"
"For Term &TERM_CODE_KEY Student Type &STYP_CODE "
"Record Count
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.TERM_CODE_KEY EQ &TERM_CODE_KEY.(OR(FIND SUBTERM.SEG01.STVTERM_CODE,SUBTERM.SEG01.STVTERM_DESC IN subterm)).TERM_CODE_KEY.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.STYP_CODE EQ &STYP_CODE.(OR(FIND STVSTYP.STVSTYP.STVSTYP_CODE,STVSTYP.STVSTYP.STVSTYP_DESC IN stvstyp)).STYP_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.ADMT_CODE EQ &ADMT_CODE.(OR(FIND STVADMT.STVADMT.STVADMT_CODE,STVADMT.STVADMT.STVADMT_DESC IN stvadmt)).ADMT_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.DECEASED_IND EQ MISSING;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.APST_CODE EQ &APST_CODE.(OR(FIND STVAPST.STVAPST.STVAPST_CODE,STVAPST.STVAPST.STVAPST_DESC IN stvapst)).APST_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.COLL_CODE1 EQ &COLL_CODE1.(OR(FIND STVCOLL.STVCOLL.STVCOLL_CODE,STVCOLL.STVCOLL.STVCOLL_DESC IN stvcoll)).COLL_CODE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.MAJR_CODE1 EQ &MAJR_CODE1.(OR(FIND STVMAJR.STVMAJR.STVMAJR_CODE,STVMAJR.STVMAJR.STVMAJR_DESC IN stvmajr)).MAJR_CODE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.STATE1 EQ &STATE1.(OR(FIND STVSTAT.STVSTAT.STVSTAT_CODE,STVSTAT.STVSTAT.STVSTAT_DESC IN stvstat)).STATE1.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.APDC_CODE1 EQ &STVAPDC_CODE.(OR(FIND STVAPDC.STVAPDC.STVAPDC_CODE,STVAPDC.STVAPDC.STVAPDC_DESC IN stvapdc)).STVAPDC_CODE.;
WHERE AS_ADMISSIONS_APPLICANT.AS_ADMISSIONS_APPLICANT.LEVL_CODE NE 'G';

809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML

Posts: 171 | Registered: April 28, 2008

Ignored post by Geoff Fish posted

roy

Silver Member

posted

This might help...

 



What iF you create your user selection as a  AND list with NOPRINT  

Example

&USERSELECT.(AND(<TEAM_NAME,TEAM_NAME NOPRINT>,<COUNTRY,COUNTRY NOPRINT>,<STATEPROVINCE,STATEPROVINCE NOPRINT>,<CITY,CITY NOPRINT>,<ZIPCODE,ZIPCODE NOPRINT>,<ADDRESS,ADDRESS NOPRINT>,<DATE_APPROVED_BIA,DATE_APPROVED_BIA NOPRINT>,<THE_PROCESS,THE_PROCESS NOPRINT>,<DEFAULT_BP_CRITICALITY,DEFAULT_BP_CRITICALITY NOPRINT>,<TEAM_REQUIRED_CRITICALITY,TEAM_REQUIRED_CRITICALITY NOPRINT>,<REJECTREASON,REJECTREASON NOPRINT>,<COMMENTS,COMMENTS NOPRINT>,<DAILYREVENUELOSS,DAILYREVENUELOSS NOPRINT>,<DAILYPENALTIES,DAILYPENALTIES NOPRINT>,<AVERAGETRANSACTVALUES,AVERAGETRANSACTVALUES NOPRINT>,<PEAKTRANSACTVALUES,PEAKTRANSACTVALUES NOPRINT>,<APPLICATIONCODE,APPLICATIONCODE NOPRINT>,<SERVICE_APPLICATION_NAME,SERVICE_APPLICATION_NAME NOPRINT>,<LEVEL3,LEVEL3 NOPRINT>,<R_ADDRESS,R_ADDRESS NOPRINT>,<RecoverySeat,RecoverySeat NOPRINT>,<R_CITY,R_CITY NOPRINT>,<R_STATE,R_STATE NOPRINT>,<R_COUNTRY,R_COUNTRY NOPRINT>,<R_ZIP,R_ZIP NOPRINT>,<R_NAME,R_NAME NOPRINT>)).1st PART BODY OF REPORT.

Then Strip away the AND replace with BY  

&USERSELECT = '&USERSELECT'
-SET &TEXT2 = STRREP(&USERSELECT.LENGTH,&USERSELECT,3,'AND',2,'BY',&USERSELECT.LENGTH,'&USERSELECT');
-SET &THEBY = STRIP(&TEXT2.LENGTH,&TEXT2,'''',&TEXT2);


Strip away the NOPRINT 

-SET &THEPSUM = STRREP(&USERSELECT.LENGTH,&USERSELECT,7,'NOPRINT',1,'''',&USERSELECT.LENGTH,'&USERSELECT');
-SET &THESUMP = STRIP(&THEPSUM.LENGTH,&THEPSUM,'''',&THEPSUM);
-* -TYPE &THEPSUM;
-TYPE &THESUMP
-TYPE &THEBY

Your Query here 


Now sum what was selected by the BY 


TABLE FILE SQLOUT
SUM
FST THE_TEAM_#
&THESUMP
BY THE_TEAM_# NOPRINT
BY &THEBY NOPRINT
HEADING

WebFOCUS 7.6
Windows, All Outputs

Posts: 37 | Registered: December 03, 2010

Ignored post by roy posted

Danny-SRL

Virtuoso

posted

Jack,

quote:
Even if the report code can distinguish between dimensions and measures, you still need to determine the priority amongst the former, and the column-order of the latter.

The order can be assumed to be the order the user chooses. It shouldn't be too difficult to separate the dimensions from the measures:
each dimension name returns BY dimension field, each measure name returns just the measure field. Then loop through and separate into 2 &variables. It should do the trick.

Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

j.gross

Virtuoso

posted

Danny: Yes, that's obvious provided it's a dual list box. But

quote:
our users only want to display 1 list box for column selection

which I understood as a multiple-select simple listbox controland that provides no avenue for varying the column order.

- Jack Gross
WF through 8.1.05

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Guru

posted

Jack I think what Danny meant was

-use a single double list control (in this case there would be an order)
-using a define on fields you know are by fields, parse the values in two separate variables. (could use a big ol' decode as well).

Have the display values as one thing and the actual values as another or handle all that logic in the fex.

WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs

Posts: 402 | Location: Upland, IN | Registered: June 08, 2012

Ignored post by J posted

ECosain

Member

posted

Hello,

Thanks for all your input. I will try to implement a few of your suggestions.

I am on vacation next week so will post my findings the week after.

Elizabeth

WebFOCUS 7.7.3
DevStudio, MRE

Posts: 27 | Registered: May 24, 2011

Ignored post by ECosain posted

Danny-SRL

Virtuoso

posted