INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Crystal Enterprise

Can CE be modified to provide a 'dynamic picklist' for report parameters? by mdwyer
Posted: 30 Dec 04

parametersLC.csp

CODE

<%
// 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
*/

%>

parametersRangeLC.csp (not sure this is ported to v10[/code]
<%
// This file provides the conditional ability to build picklist
// parameter selection for Range parameters.
//

// 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 controls
    if ( inputNameType == 'rangefromsingle' )
    {
        // DISPLAY THE FORMAT TO USE FOR DATE PARAMETERS
        if ( vt == 3)
            Response.Write("<td valign='top' class='schedule' colspan=2>" + L_DATE_FORMAT + "</td></tr><tr>");
        
        Response.Write("<td class='schedule'><input onchange='checkSingleValue(this,"+vt+");' type='"+inputType+"' class='schedule' name='rangefromsingle" + i +"' maxlength=254>");
    }
    if ( inputNameType == 'rangetosingle' )
    {
        Response.Write("<td class='schedule'><input onchange='checkSingleValue(this,"+vt+");' type='"+inputType+"' class='schedule' name='rangetosingle" + i +"' maxlength=254>");
    }
}
else
{
    picklisted = true;
    // Use a <select> control, whose <option> elements will be populated from the database
    if ( inputNameType == 'rangefromsingle' )
    {
        Response.Write("<td class='schedule'><select class='schedule' name='rangefromsingle"+i+"' maxlength=254 ");
        Response.Write(" onchange='checkSingleValue(this,"+vt+");'>");
    }
    
    if ( inputNameType == 'rangetosingle' )
    {
        Response.Write("<td class='schedule'><select class='schedule' name='rangetosingle"+i+"' maxlength=254 ");
        Response.Write(" onchange='checkSingleValue(this,"+vt+");'>");
    }
    
    // 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
    // Discovered that database dates are converted to Javascript format in Fields().Value,
    // i.e., Day Mon DD HH:MI:SS TMZ YYYY (e.g., Tue Feb 10 02:29:15 MST 2004)
    }

    // 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(inputNameType + i);
    Response.Write("<script> SetList('" + sval + "', document.sform."+inputNameType+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.)
}
%>
[/code]

v10 rptschedule.csp snippets

CODE

          // 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);
            }

          }


v9 schedule.csp snippets

CODE

                    // 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);
                        }

                    }

Back to Business Objects:Crystal Enterprise FAQ Index
Back to Business Objects:Crystal Enterprise Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close