add where clause (filter at runtime) 2

[skitty123]

I am working with the CR version that ships with Visual studio .NET. I think its 8.1

I have a report that I need to be able to filter depending on a user supplied selection.
The report gives details of orders,
ordinarily it lists all ordernumbers. I have a table that has a subset of these order numbers. If the user selects a option on a web page ("show only subset"), I want the report to filter and show only the ordernumbers that exist in this subset table else show all ordernumbers.
(basically I need to add a where clause like
" where ordernumber in (select ordernumber from tbl_subsetOrderNumbers" )

Is this possible? Currently I have made 2 compeletly seperate reports and the correct one gets called depending on what the user selects, but it seems such a waste when I just need a small filter

 

[MColeman]

You could use an ADO dataset as the datasource for the report. You could conditionally populate the dataset based on the filter.

Here's a thread that might help. thread796-614266

 

[skitty123]

I looked at the push method but for some reason I cant seem to get the PUSH method to work when my SQL has a table join. the link to the example you sent also describes only 1 table in the dataset

please help?

 

[lbass]

Within CR, you could use a left join from the original table to the subset table, and then create a parameter and use a record selection statement like:

if {?parameter} = "Show only subset" then
{table.orderID} = {subset.orderID} else
{table.orderID} = {table.orderID}

Not sure if this quite meets your needs...

-LB

 

[Ngolem]

lbass

I would make it perhaps an equal join or at least try it that way between table and Subset based on orderID

if {?parameter} = "Show only subset" then
{table.orderID} = {subset.orderID}
else
true;

I think the link will be ignored if Subset table is not referenced. Also....when using the orderID field ALWAYS refer to the table.orderId value never refer to Subset values to make sure there is no link...unless you want to in the Selection Criteria...this will make Crystal ignore the link if you don't want to use the Subset values.

I think this might work...if it doesn't then use a left join on the link and see if that works.





Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Ngolem]

actually I'll change that formula to this in order to get it pushed down to the server


if {?parameter} = "Show only subset" then
{table.orderID} = {subset.orderID}
else if {?parameter} <> "Show only subset" then
true;


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[skitty123]

Nglom & lbass:
thanks so much!
question: how do I get crystal to do a left/ equal join rather than its regular inner join? I am new to crystal but from what I can see it does not really let you control the SQL in any way. ( I am using visual studio .net and crystal reports is shipped with it)

thanks!!

 

[lbass]

I am not familiar with the .net version, but in 8.0 you would go to Database->Visual Linking Expert->click on the link until it turns white->click on link options->choose "left outer."

-LB

 

[skitty123]

thanks!!
when I changed the link to right outer, I got a warning message that "please be advised your link config has multipkle starting points and this is generaly not supported." though it went ahead anyway..
what does this mean?

 

[lbass]

Well, it should be a left outer, not a right. The message about multiple starting points indicates that you might have a table (or a set of unlinked tables) that is unlinked with another set of tables. Try scrolling down and over in the visual linking expert to see if you find an "orphaned" table. Otherwise, try changing the right link to a left one to see if that eliminates the message.

-LB

 

[skitty123]

yes, there was an 'orphaned' table that I did not see earlier. I linked it and the message disappeared.
Thanks so much!!