Can CE be modified to provide a 'dynamic picklist' for report parameters?

<%
// This file provides the conditional ability to build picklist 
// parameter selection using a real-time query to the database. 
//
// This code is embedded into the middle of a routine in schedule.csp, using a #include statement.
//
// The structure of the parameter-droplist technique is explained below at the word DOCUMENTATION.
// (The core of the database access routine was found on the web; see the NOTE at the end of the file.)

// Establish a database connection (see the note at the end of the file)
var connSimple = Server.CreateObject("ADODB.Connection");
if ( APSName == "DEV" )
	connSimple.Open("DSN=LCWT", "EBRS", "EBRS");	// (ODBC Dev/Test Database, User, Password)
else
	connSimple.Open("DSN=LCW", "EBRS", "EBRS");	// (ODBC Production Database, User, Password)

// Determine if the current parameter has a picklist datasource
var query = "SELECT * FROM FN_PARAM_LIST WHERE PARAM_NAME = '" + prompti.ParameterName + "'";
var result = connSimple.Execute(query);

if ( result.EOF )
{
	picklisted = false;
	// Use the original <input> form control
//v9	Response.Write("<input onchange='checkSingleValue(this,"+vt+");' type='"+inputType+"' class='scheduleValue' name='discretesingle"+i+"' maxlength=254>");
//v10...    %>
	<input type="<%= inputType %>" class='schedule' name='discretesingle<%= i %>' value="" onchange="ResetDefaultList('discretedefault<%= i %>')">
    <%//...v10
}
else
{
	picklisted = true;
	// Use a <select> control, whose <option> elements will be populated from the database
	Response.Write("<select class='schedule' name='discretesingle"+i+"' maxlength=254 ");
	Response.Write(" onchange='checkSingleValue(this,"+vt+");'>");
	
/*	Response.Write(" onchange='checkSingleValue(this,"+vt+");AddValues("+i+",1);'>");
    The AddValues function can be a nice touch in single-value parameters, allowing the user 
	to skip the redundant Add button. But: pre-selected values are not automatically added; 
	multi-value parameters get overwritten when the function is auto-applied; and users are 
	less likely to err if trained that the Add and Add Range buttons are always required. */

	// Query the datasource for the current parameter options
	var ptbl = result.Fields("PARAM_TABLE").Value;
	var vcol = result.Fields("VALUE_COLUMN").Value;
	var ncol = result.Fields("NAME_COLUMN").Value;
	query = "SELECT " + vcol + ", " + ncol + " FROM " + ptbl;
	result.Close;
	result = connSimple.Execute(query);

	// Check for a preselected value for this parameter
	var presel = String(GetSubCookie("ePortfolio_prefs", prompti.ParameterName));

	// Loop through the rows and display them in <option> tags
	// (The variables are used to make the tag code legible; it may be less efficient.)
	var pval;
	var pname;
	while ( ! result.EOF )
	{
		pval  = result.Fields(vcol).Value;
		pname = result.Fields(ncol).Value;
		if ( pval == presel )
			Response.Write("<option value='" + pval + "' selected>" + pname);
		else
			Response.Write("<option value='" + pval + "'>" + pname);
		result.MoveNext
	}

	// Close the DB cursor and connection. Release memory
	result.Close;
	connSimple.Close;
	
	result = "";
	connSimple = "";

	// Close the <select> tag and set its display to the selected value
	Response.Write("</select>");
	var sval = Request.Form.Item("discretesingle"+i);
	Response.Write("<script> SetList('" + sval + "', document.sform.discretesingle"+i + ", '" + prompti.ParameterName + "'); </script>");
	if ( sval.count != 0 )
		SetSubCookie("ePortfolio_prefs", prompti.ParameterName, sval);
		// (The code used for this cookie is only minimally developed. Also, 
		//  the cookie should really be set when the schedule is applied, not 
		//  when the value is selected. More work is needed when time permits.)
}

/* DOCUMENTATION
This is a table-driven system that allows the report parameter interface to be switched 
from a text box (<input...text> tag) to a picklist (<select> tag) dynamically, and the 
picklist elements (<option> tags) are generated by a real-time call to the database.

Picklist generation is keyed to the parameter names in the reports, specifically as they 
are displayed in Crystal Enterprise. (See the prompti.ParameterName object/property.) 

To give a parameter a picklist, create a View in the FN schema of the LCW database. This 
View needs to provide a column of values to be passed to the report and values to display 
to the user in the picklist control. (Where these are the same values, the View would only 
need one column. Also, if an existing Table contains the parameter values in a usable form, 
a separate View may be unnecessary. However, any complexity in the display values should be 
defined in a View, for example, "...SELECT ORG, ORG||': '||ORG_NAME FROM FN_ORGS".

The View (or Table) is associated with its parameter and identified to Crystal Enterprise 
using the FN_PARAM_LIST table in the LCW.FN schema. That table is keyed by the parameter 
name and contains the associated View synonym and column names that have the parameter and 
display values. (As mentioned above, the same column name could be used for both.) 

The picklist code checks the FN_PARAM_LIST table for a row matching the current parameter. 
[If none is found, the standard <input> tag is generated.] Finding one, it creates a query 
to read the picklist view and columns specified for that parameter. Those rows are used to 
populate the picklist control as <option> tags.

The FN_PARAM_LIST table and the handling of the picklist rows is intended to be simple 
and, hopefully, straightforward. Where greater complexity is needed (formatting of display 
strings, use of DISTINCT or UNIQUE keywords, WHERE clauses, etc.) the View script provides 
that capability in the most flexible and accessible manner.
*/

/* NOTE
The code above was adapted from a "google" to the following web page:
http://listserv.sap.com/pipermail/sapdb.general/2002-November/010006.html

The content of that page was a mailing list post, which said:

Hi, I made this simple ASP page that connects to SAPDB... 
where 
test is the name of the DSN I had configured using SAPDB ODBC.
the "ADMIN" is the username and "a" is the password.

<%
Dim cnnSimple 
Dim rstSimple

set cnnSimple = Server.CreateObject("ADODB.Connection")
cnnSimple.Open "DSN=test", "ADMIN", "a"

set rstSimple = cnnSimple.Execute("Select * from temporal1")

Do While Not rstSimple.EOF 
        response.write (rstSimple.Fields("TOTAL").Value)
        rstSimple.MoveNext
Loop

rstSimple.Close

set rstSimple = Nothing
cnnSimple.Close

set cnnSimple = Nothing
% >

bye
Alex
TOMITA_ALEX_NONLILLY at Lilly.com 
*/

%>

          // IF PROMPT ALLOWS EDITING OF DEFAULT VALUES THEN PROVIDE A TEXT BOX
          if(prompti.DisallowEditing || vt == 2) {
            bEditBoxShown = false;
          %>  
            <input type='hidden' name='discretesingle<%= i %>' value="<%= Server.HTMLEncode(defaultInput) %>"> 
          <%
          }
          else {
            bEditBoxShown = true;
          %>
			<!-- Add conditional code to provide drop-list selection of parameter values. -->
			<!-- #include file="parametersLC.csp" -->
			
			<!-- The following line is included in the include-file above. -->
			<!-- <input type="<%= inputType %>" class='schedule' name='discretesingle<%= i %>' value="" onchange="ResetDefaultList('discretedefault<%= i %>')"> -->
          <%
			if ( ! picklisted)
            if (vt == 3 || vt == 5) {
              if (vt == 3)
                Response.Write ("<a href='javascript:doNothing();' onClick='setDateField(document.sform.discretesingle" +i+ ",\"" +lang+ "\",\"Date(yyyy,mm,dd)\"); LaunchCalendar();'>");
              else
                Response.Write("<a href='javascript:doNothing();' onClick='setDateField(document.sform.discretesingle" +i+ ",\"" +lang+ "\",\"DateTime(yyyy,mm,dd,hh,kk,ss)\"); LaunchCalendar();'>");
              Response.Write(" <img name='calendar' border='0' src='" + GetLinkPath() + "images/calendar.gif' height='21' width='21' align='absbottom' alt='" + Server.HTMLEncode(L_VBS_POPUP_CALENDAR) + "' title='" + Server.HTMLEncode(L_VBS_POPUP_CALENDAR) + "'>");
              Response.Write("</a>" + vbCRLF);
            }
          }

...

the following code modification occurs twice in rptschedule.csp

          // IF PROMPT ALLOWS EDITING OF DEFAULT VALUES THEN PROVIDE A TEXT BOX
          if(prompti.DisallowEditing) {
            bEditBoxShown = false;
          %>
            <input type='hidden' name='rangefromsingle<%= i %>' value="<%= Server.HTMLEncode(defaultInput) %>">
          <%            
          }
          else {
            bEditBoxShown = true;
			// Include code to provide conditional drop-list selection of parameter values
			inputNameType = 'rangefromsingle';
          %>
			<!-- Add conditional code to provide drop-list selection of parameter values. -->
			<!-- #include file="parametersRangeLC.csp" -->

			<!-- The following line is included in the include-file above. -->
            <!-- <input type='<%= inputType %>' class='schedule' name='rangefromsingle<%= i %>' value="" onchange="ResetDefaultList('rangefromdefault<%= i %>');"> -->
          <%
			if ( ! picklisted)
            if (vt == 3 || vt == 5) {
              if (vt == 3)
                Response.Write ("<a href='javascript:doNothing();' onClick='setDateField(document.sform.rangefromsingle" +i+ ",\"" +lang+ "\",\"Date(yyyy,mm,dd)\"); LaunchCalendar();'>");
              else
                Response.Write ("<a href='javascript:doNothing();' onClick='setDateField(document.sform.rangefromsingle" +i+ ",\"" +lang+ "\",\"DateTime(yyyy,mm,dd,hh,kk,ss)\"); LaunchCalendar();'>");
              Response.Write(" <img name='calendar' border='0' src='" + GetLinkPath() + "images/calendar.gif' height='21' width='21' align='absbottom' alt='" + Server.HTMLEncode(L_VBS_POPUP_CALENDAR) + "' title='" + Server.HTMLEncode(L_VBS_POPUP_CALENDAR) + "'>");
              Response.Write("</a>" + vbCRLF);
            }

          }

					// IF PROMPT ALLOWS EDITING OF DEFAULT VALUES THEN PROVIDE A TEXT BOX
					if(prompti.DisallowEditing || vt == 2)
						Response.Write("<input type='hidden' name='discretesingle"+i+"' value=''>");
					else {

						// Add conditional code to provide drop-list selection of parameter values. 11/18/2003
						%><!-- #include file="parametersLC.csp" --><%	// Moved surrounding code into include file 11/20/2003

						if ( ! picklisted)
						if (vt == 3 || vt == 5) {
							if (vt == 3)
								Response.Write("<a href='javascript:doNothing();' onClick='calDateFormat=\"Date(yyyy,mm,dd)\"; setDateField(document.sform.discretesingle"+i+",\""+lang+"\"); LaunchCalendar();'>");
							else
								Response.Write("<a href='javascript:doNothing();' onClick='calDateFormat=\"DateTime(yyyy,mm,dd,hh,kk,ss)\"; setDateField(document.sform.discretesingle"+i+",\""+lang+"\"); LaunchCalendar();'>");
							Response.Write(" <img name='calendar' border='0' src='" + GetLinkPath() + "images/calendar.gif' height='21' width='21' align='absbottom' alt='" + L_VBS_POPUP_CALENDAR + "'>");
							Response.Write("</a>" + vbCRLF);
						}
					}

...

the following code modification occurs twice in schedule.csp

					// IF PROMPT ALLOWS EDITING OF DEFAULT VALUES THEN PROVIDE A TEXT BOX
					if(prompti.DisallowEditing)
						Response.Write("<td class='schedule'><input type='hidden' name='rangefromsingle" + i +"' value=''>");
					else {

						// Include code to provide conditional drop-list selection of parameter values.
						// Some code has been moved into the 'include' file for conditional processing.
						inputNameType = 'rangefromsingle';
						%><!-- #include file="parametersRangeLC.csp" --><%

						if ( ! picklisted)
						if (vt == 3 || vt == 5) {
							if (vt == 3)
								Response.Write("<a href='javascript:doNothing();' onClick='calDateFormat=\"Date(yyyy,mm,dd)\"; setDateField(document.sform.rangefromsingle"+i+",\""+lang+"\"); LaunchCalendar();'>");
							else
								Response.Write("<a href='javascript:doNothing();' onClick='calDateFormat=\"DateTime(yyyy,mm,dd,hh,kk,ss)\"; setDateField(document.sform.rangefromsingle"+i+",\""+lang+"\"); LaunchCalendar();'>");
							Response.Write(" <img name='calendar' border='0' src='" + GetLinkPath() + "images/calendar.gif' height='21' width='21' align='absbottom' alt='" + L_VBS_POPUP_CALENDAR + "'>");
							Response.Write("</a>" + vbCRLF);
						}

					}