×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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.

Students Click Here

Business Objects: Crystal Reports 2 Data Access FAQ

Commands

How can I use a Multivalue or Date Range Parameter with a Command by RustyAfro
Posted: 20 Nov 07

This FAQ describes a technique in Crystal that gets around a major limitation with Commands.

The limitation is that CR Commands do not have a mechanism to allow for the use of multivalue or date range parameters.   You cannot simply put in the command sql "table.fieldname IN ({?Parameter})" because the parameter is technically an array in crystal that is not comma delimited in a parsable string by a database server.

One solution is to present the user with say 10 parameters (one for each item) that have the same 10 items that can be selected.  Then you insert each parameter into the SQL of the command with 10 OR commands for each parameters and perhaps an "ALL" or "None" option as well.  This can get messy though and will not support dynamic multi-value prompts.  There is also a method by presenting the user with a single value parameter and them manually input a comma delimited string û but then you lose CRÆs ability to present a List of Values for the user to select and you have to trust the user to enter the string properly or risk a scary error.

This technique will "inject" a multi-value or date range parameter directly into the SQL for a true IN statement which opened up a several interesting possibilities.  I would call the technique "SQL Injection" because that is in effect what it is doing (it sort of mimicks crystals sql generation ability).

Here is the technique as I was able to get to work.

1.  Create a master report that has no connections.
2.  Create a multi-value parameter in the master report as normal (ie: db_Products)
3.  Create a formula in the master report (ie: par_Products)

This "Formula Parameter" will be responsible to parse the "db_Products" parameter into a sql string that your server can understand.  The bottom of this FAQ has some examples of the formulas.

4.  Create your report in a separate .rpt file with your SQL Command.
5.  Insert Command parameters with the same name as your formula in the master report (ie: par_Products) for ease of understanding only.
6.  Insert the report in the master report as a sub report.
7.  Create a subreport link on the "Formula Parameter" in the master report to the Command Parameter (ie: par_Products -> par_Products)

8.  Refresh the report from the master.

The master report will prompt the user using the multi-value or date-range parameter giving the user access to Crystal full capabilities with presentable parameters.

The formula will "massage" the answer into a format suitable for your server

And pass it to the subreport parameter to be inserted into the Command sql.

For the date-range formula example, you can literally place the "BETWEEN" statement in the formula.  So in the command SQL you would just put the databasefield and the parameter side by side.  
AND PROPOSAL.RATE_DATE {?par_RateDateRange}
The date-range formula could therefore be cleaned up a little to use a ">=" or "<=" operators to dynamically handle the "No Upper Bound" selection instead of a very wide between statement as I implemented.

That's the whole thing!.  The major disadvantage is that you lose the ability to put subreports in your report (since subs cannot have subs).  But if subreports are not a factor, this produces clean sql to drive your reports.  With peer review I am sure the technique could be improved and probably have other uses.


Formula Examples:

If the parameter is a string, the formula would look like this:

CODE

//@par_Products
stringvar c;

IF COUNT({?db_Products}) > 1 Then
(    
    c:= join({?db_Products}, ",");
    c:= Replace (c,",","','"); //Wrap single quotes around every variable for DB2 to parse correctly
)
Else
 c:=(Minimum({?db_Products}));

//Return the variable
"'" &  c  & "'";
If the parameter is a number, the formula would look like this:

CODE

//@par_Products
IF COUNT({?db_Products}) > 1 Then
    join({?db_Products}, ",")
Else
    Minimum({?db_Products});
If the parameter is a date range, the formula would look like this:

CODE

//This formula builds a Between date string based on the user selection in the
//parameters, providing a way to share it across other subreports

Stringvar Range1;
Stringvar Range2;

//Change the date format depending on the server.  This works for db2.
    Range1:=Cstr(Minimum({?db_EffDate}), "yyyy-MM-dd");       
    Range2:=Cstr(Maximum({?db_EffDate}), "yyyy-MM-dd");     

//If user selected "No Upper Bound" then must provide a large enough range to accomodate
If Range1 = "" then
    Range1 = "1900-01-01";
ELSE IF Range2 = "" Then
    Range2 = "2099-12-31"

//Build the BETWEEN date string
"BETWEEN '" & Range1 & "' AND '" & Range2 & "'";

Back to Business Objects: Crystal Reports 2 Data Access FAQ Index
Back to Business Objects: Crystal Reports 2 Data Access Forum

My Archive

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