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

 

[GJParker]

Ravi

There shouldn't be any problem in leaving all of the tables linked.

As long as none of the fields from table b and table c are used in the report then crystal will build the query using only tables with fields used in the report.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[Videla]

Hi,

Pls note that crystal is not framing the query, We framed the query using new command. After creating a connection, we have option to add "command". We framed the query there. Now my whole question how can i 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

If we use crystal syntax {if any product related attribute is chosen} then it will give syntax error. Is there any alternative to acheive this functionality.

Thanks
Ravi

 

[GJParker]

If I were you I would abandon this method and look at building the SQL in your application and passign the recordset to the report.

Take a look at the .NET samples abvailable here

http://www.businessobjects.com/products/dev_zone/

Good Luck

Gary Parker
MIS Data Analyst
Manchester, England

 

[Videla]

Hi,

Can you give me more details so that i can look at exact documentation...There are lot of things in this dev zone and i am not able identify the right link.

When you say "Build SQL in applicaiton", is it building the sql in asp.net itself? Then in that case how can i pass this sql to crystal report and How can i desing the template in crystal designer. Appreciate your help is resolving this.

Thanks
Ravi

 

[GJParker]

Hi Videla

Unfortunately .NET programming and crystal isn't my area of expertise so I really can't help with the specifics.

I do know that you can pass the SQL directly via a program or you can also create the recordset via .NET and pass that to the report.

There is a seperate forum for crystal development you may get better assistance by posting a new thread in there.

forum768

Good Luck

Gary Parker
MIS Data Analyst
Manchester, England