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,

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.

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?

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

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

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.

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.

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.

That's perfect! Thank you very much.

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