Dynamic Query Formation in Crystal Reports

[Videla]

Hi All,

We are using CR XI and ASP.NET. I am trying to frame the WHERE clause of a query dynamically based on user input.

Selection criteria in asp.net page are divided into four sections.
compnay related (compnay name, branch, office etc.)
Product related (product line, sub product line etc.)
Representative (rep name, rep number etc.)

we have a fact table which has sales details at company, product, representative level.
Report output format is fixed. Initially it will show compnay name, sales. If we drill down on compnay name it will show branch, sales. Then to office, sales.

At present while creating the crystal report template, we fixed the joins. (fact to compnay using compnay id
to product using product id
to rep using rep id).

Assume if user does not select any filters, ideally we should not join with all dimensions but only fact and company. We found "Command" object where we could specify SQL query. But the question is how can we make it dynamic.

select company name, sales from
fact table,
company,
{if any product related attribute is chosen}
product
{if any rep related attribute is chosen}
rep
where
fact.compnay id = company.company id
{if any product related attribute is chosen}
and fact.productid = product.productid
{if any rep related attribute is chosen}
fact.repid = product.repid

Appreciate your help in acheiving this functionality.

Thanks
Ravi