How to pass SQL where clause to report

[veronic]

I have SQL statement in Command which looks like

Select a,b,c,d
from atable where
a = "parameter1" and
b = "parameter2" and
c = "parameter3"

Is it possible to pass it to Crystal as one 'where clause' instead of passing three different parameters??

 

[tomk01]

Im not sure what you are trying to do. but you might want to try putting somthing in the edit selection formula

HTH

 

[rhinok]

There is only one WHERE clause in your statement. Is the issue that you want to pass multiple parameter values as part of a single parameter, instead of having three parameters, each with a distinct value?

SQL Commands can't handle multiple value parameters natively. The only way to do what you want with a SQL Command is to create a single string parameter into which you will input all possible parameter values, separating each parameter value with a delimiter such as a tilde (~). You would then have to parse the values out of the parameter string in your SQL.

All that being said, this is probably much too complicated given the simplicity of your SQL Command. Instead, you could just link the tables in Crystal, use a Crystal Reports Multiple-value parameter (native functionality) and handle the WHERE clause via your Record Selection Criteria:

{table.field} IN {?MULTIVALUEPARAM}

If you do this correctly, Crystal will pass the Record Selection Criteria to the DB as the WHERE clause in the SQL it generates (Database|Show SQL Query).

~Kurt

 

[veronic]

I did not explain my question correctly.
I have a report done on VB where user select number of different options of information he wants to see and on that page link to Printer friendly version(which is in Crystal). I need to be able to send this to Crystal as where clause in SQL in run time.

 

[vidru]

The Report object has a RecordSelectionFormula property that can be set at runtime:

Report.RecordSelectionFormula = "{Table.Field} = " & strValue

-dave

 

[veronic]

But it only apply to one table and one field. I have many different parameters.
For example:
User select information for Bob Smith, only close cases, o in Dev, high priority...etc. It is all done in VB for the HTML report.In this case Sql where clause looks like:
where name = "Bob Smeth" and
status = " Close" and
priority = "High".....
If report should be printed it is link to Printer friendly version (in Crystal) and all information should be send to Crystal using VB
So, instead of sending number of different parameters I want to be able to send dynamicly changed where clause. Is it possible??

 

[vidru]

The passing of the RecordSelecionFormula is how you can alter the WHERE clause from within Crystal.

If you MUST see a WHERE clause in order to grasp this, then you should create your SQL and a dataset in VB, and set the Report's Data Source to the Dataset at runtime using the SetDataSource method.

-dave

 

[rana77]

Hi veronic ,

U can change the sql query of crystal report at run time.
Copy the sql and append Where clause at run time.

then change it at run time using SqlQueryString Property.
It will solve ur problem. In this case u dont need to pass any parameter or use any recordselection formula.

cheers
Lalit Rana

 

[veronic]

Thank you very much but could you please give me an example???

Thanks again