Creating 'Dynamic' queries -

[Former Member]

Hi there,

I am use dto using Actuate reports where I can build up some SQL as a string before processing it.

Doing this allows me to totally alter the SQL that is run based on various parameters (using IF THEN ELSE statements to build up the SQL string)

How can I do this in Crystal Reports (if I can at all)?

I know I can create parameters which add to the WHERE clause.

But is there a way I can change the rest of the SQL that is run based on parameters?

Thanks in advance,

Matt

[Former Member · Accepted solution]

Hi Matthew

If I understand correctly, you want to change the sql based on the value of the parameter that an user selects at the run time.

Something that you are trying to achieve is curently not possible directly by design.

However, you may use nested subqueries to achieve this and specify a 'Where' condition at every level of a nested subquery.The parameter can again be used in the where condition.

Alternatively, you can take care of this at the database end(which could be more appropriate)by writing a stored procedure and collecting the result of the select query in a variable and firing another one based on the value of the variable.

Thanks

[Former Member]

Hi Matthew

You can use a subreport to pass the main report's parameter values to a parameter in the query on which the subreport is based.

Let me know if you do not understand this.

Regards

Nikhil