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     [SOLVED] Question: How do you set a prompt combo to an 1D array?
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Question: How do you set a prompt combo to an 1D array?
 Login/Join
 
Member
posted
I am very new to WebFOCUS and have only been programming in FOCUS for about 4 months.

Yesterday, I learned how to create an amper variable prompt on the launch screen and to change the prompt text box to a combo box. I work for a college and most of our queries depend on a semester variable. So, in 2013 we will have the following semesters: 20132, 20134, 20136. This 2, 4, 6 repeats every year. I need to populate the row source of my combo box with every term from 1999 to 2013. The only way I know to do it is literally type the semesters inside the parenthesis as such:


-PROMPT &TERMCYT.(19992,19994,19996,20002,20004,20006,20012,20014,20016,20022,20024,20026,20032,20034,20036,20042,20044,20046,20052,20054,20056,20062,20064,20066,20072,20074,20076,20082,20084,20086,20092,20094,20096,20102,20104,20106,20112,20114,20116,20122,20124,20126,20132).Enter year & term.

Is there any way I can create some sort of loop that will populate the rows?

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


Dev. Studio 7.6.11
Win 7
 
Posts: 21 | Location: Oklahoma | Registered: November 01, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If you have the semesters in a database in whatever format you can use that as the source of your select box.
That database can in fact also be a small text file with all the values in it.
That file is something you can create yourself, dynamical, each month, year or semester.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Member
posted Hide Post
I thought about using a text file or even another FEX file. Do you simply use an INCLUDE statement to pull in the semester data?


Dev. Studio 7.6.11
Win 7
 
Posts: 21 | Location: Oklahoma | Registered: November 01, 2012Reply With QuoteReport This Post
Master
posted Hide Post
Sam,
You can code a dynamic list of values the following way:

-PROMPT &VPSELCT.(FIND RPTVP IN VPDROPF).Please Select VP. );


This will require a table called VPDROPF with a field called RPTVP that can be referenced. You cannot do an ORDER BY, WHERE, DISTINCT, JOIN or concatenate values -- it is what it is.

That said, we use it in conjunction with another timed job in ReportCaster that does all that on the front-end. Our RC job loads this look up table.

- ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
Master
posted Hide Post
Or consider a Dynamic SQL view that calculates these values. MFD will never have to be updated (neither wi;; the view code).

SELECT
CONVERT(VARCHAR(4), GETDATE()+1, 111) + '01'  AS 'NEXTYEAR_Q1'
,CONVERT(VARCHAR(4), GETDATE()+1, 111) + '02' AS 'NEXTYEAR_Q2'
,CONVERT(VARCHAR(4), GETDATE()+1, 111) + '03' AS 'NEXTYEAR_Q3'
,CONVERT(VARCHAR(4), GETDATE()+1, 111) + '04' AS 'NEXTYEAR_Q4'

,CONVERT(VARCHAR(4), GETDATE(), 111) + '01' AS 'THISYEAR_Q1'
,CONVERT(VARCHAR(4), GETDATE(), 111) + '02' AS 'THISYEAR_Q2'
,CONVERT(VARCHAR(4), GETDATE(), 111) + '03' AS 'THISYEAR_Q3'
,CONVERT(VARCHAR(4), GETDATE(), 111) + '04' AS 'THISYEAR_Q4'

,CONVERT(VARCHAR(4), GETDATE()-1, 111) + '01' AS 'LASTYEAR_Q1'
,CONVERT(VARCHAR(4), GETDATE()-1, 111) + '02' AS 'LASTYEAR_Q2'
,CONVERT(VARCHAR(4), GETDATE()-1, 111) + '03' AS 'LASTYEAR_Q3'
,CONVERT(VARCHAR(4), GETDATE()-1, 111) + '04' AS 'LASTYEAR_Q4'


- ABT
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
Member
posted Hide Post
Thank you. These are great suggestions. Unfortunately, we work under pretty strict restrictions, connecting to a remote server. We are not allowed to create permanent tables.

This data is available in existing tables, however. I wonder whether I could simply launch a table read at the beginning of the program and hold the list with a PCHOLD? I am not sure how to pass a PCHOLD to the PROMPT command, though.


Dev. Studio 7.6.11
Win 7
 
Posts: 21 | Location: Oklahoma | Registered: November 01, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
This need not be a permanent table. It can be the result of "ON TABLE HOLD AS VPDROPF..." which is available for your session and can be used in ABT's "-PROMPT &VPSELCT.(FIND RPTVP IN VPDROPF).Please Select VP. );" statement.
 
Posts: 3103 | Location: Middle Tennessee | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If you do a PCHOLD on that table with semester info you should say PCHOLD format XML
This little program can be used in an HTML application and used in on of the drop down boxes.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Member
posted Hide Post
That's perfect! Thank you very much.


Dev. Studio 7.6.11
Win 7
 
Posts: 21 | Location: Oklahoma | Registered: November 01, 2012Reply With QuoteReport This Post
Member
posted Hide Post
Sam_R,
What Frank is mentions is exactly what we do.
 -* File xml_term.fex
TABLE FILE TERM_DIM
BY  LOWEST TERM_DIM.TERM_DIM.TERM_CODE
BY  LOWEST TERM_DIM.TERM_DIM.TERM_DESC
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XML
END 



If you wan to get fancy I have a .fex that also limits the number of terms returned:
 
TABLE FILE TERM_DIM
-*WHERE RECORDLIMIT EQ 10
PRINT
     TERM_DIM.TERM_DIM.TERM_DESC
BY  HIGHEST TERM_DIM.TERM_DIM.TERM_CODE
WHERE RECORDLIMIT EQ &RL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XML
END
 


I have a radio button that sets the variable &RL to 10 by default on the HTML launch page. The other choice is 'All' which is set to 99. For us, a Community College 10 records is three years and works well for a report that doesn't really need historical information.

We also have reports that do the same thing for Departments and Subjects with in each College. This is especially helpful with multiple people writing reports. We don't duplicate effort and the deployment is much easier.

~david


WebFOCUS 8.0.05
Windows- Excel, PDF
 
Posts: 21 | Location: Crystal City, VA & Kalamazoo, MI | Registered: October 11, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Question: How do you set a prompt combo to an 1D array?

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