Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Guided Ad Hoc Report with 1 list of fields
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Guided Ad Hoc Report with 1 list of fields
 Login/Join
 
Member
posted
Hello,

We are creating cusomizable reports (using Managed Reporting) that will allow users to include/exclude columns, order the columns and save the reports for future use. I was able to create a guided ad hoc report that seperates the measure and the 'By' options (meaning they select from 2 different list boxes). But our users only want to display 1 list box for column selection. Meaning that the group/sum columns will be comingled with the detail columns. Has anyone seen this be done?

We are using an Oracle database so I thought maybe a stored procdure to populate the columns may work. But I wanted to see if anyone has already done this before.

Thanks in advance for your help.

Elizabeth Cosain

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


WebFOCUS 7.7.3
DevStudio, MRE
 
Posts: 27 | Registered: May 24, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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: 1976 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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, 2008Reply With QuoteReport This Post
Silver Member
posted Hide Post
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, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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: 1976 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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, 2012Reply With QuoteReport This Post
Member
posted Hide Post
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, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
J,

Yes, you got me right.
One can even imagine a single list box where the order is preset and immuable.


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

 
Posts: 1976 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Guided Ad Hoc Report with 1 list of fields

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.