Conditional Link to the table in Crystal Reports 2011

[sgoyal11]

Hi,

I found this thread766-408341 closed thread that describes my problem exactly. Does anybody has any suggestion?

Thanks in advance for any help,
Shikha

 

[lbass]

That was a very old thread. One way to handle this is to use a command as your data source and then use a union all statement, like this:

Select “Transaction” as type, a.cust, b.date
From Customer a inner join Transaction b on
a.cust=b.cust and
b.date >= {?DateStart}

Union All

Select “OtherTrans” as type, a.cust, c.date
From Customer a inner join OtherTrans c on
a.cust=c.cust and
c.date >= {?DateStart}

The syntax depends on your data source. The inner joins assume you only want data where dates are not null in each table. If you want to show records with null dates AND those within the time frame, change it to a left join. By adding the “where” clause into the “From” section, the left join can be maintained even with a selection on the secondary table. Doing this also speeds up the command. You will end up with fields from the two secondary tables in the same field. You can distinguish the tables by using the {command.type} as a label for the records. If there are no records meeting the criteria in one section of the command, only the remaining section will be executed. Be sure to create the Parameter(s) within the command object.

-LB

 

[sgoyal11]

Thanks for you reply, but it doesn't solve the problem. Let me try to explain again.

As mentioned in the original thread, I have a parameter called Customers to include, which, if set to 'All', I don't even want to look in the table 2. If 'Current' is selected it's easy it looks for the selected date in table b and gives the records that matches the criteria.
This is what I have

if {?customerstoinclude} = "All" then true else
if {?customerstoinclude} = "Current" then {Table_2.date} = {?Date}


I tried the 'Union' query already but its slowing down the report even more as there are thousands of records in the table and its reading them twice now.

select A.*
union all
select A.*
left join B on A.id = B.id where {Table_2.date} = {?Date}

Date is a optional parameter.

Please let me know if this is not clear, I can try to explain again and thanks for any help you can provide.

-SG

 

[lbass]

Here is an example using the Extreme database. If you add in an impossible statement, the first half of the union will not execute.

SELECT `Customer`.`Customer ID`,
`Orders`.`Customer ID`,
`Orders`.`Order Date`,
`Customer`.`Customer Name`
FROM `Customer` `Customer`
left join `Orders` `Orders` on
`Customer`.`Customer ID`=`Orders`.`Customer ID`
where
(
(
'{?All or Current}'='All' and
1=2
)
or
(
'{?All or Current}'='Current' and
`Orders`.`Order Date`>={?StartDate}
)
)

union all

SELECT `Customer`.`Customer ID`,
null,
null,
`Customer`.`Customer Name`
FROM `Customer` `Customer`
where '{?All or Current}'='All'

You can adapt this to your needs.

It looks like you are didn't use only the command as your datasource. You must ONLY use the command, not a selection statement in the main report, and that is why I also mentioned that you must create the parameters within the command screen. If you use a command and also a selection statement in the main report, the report will only run locally with tons of records.

-LB

 

[sgoyal11]

Thank you so much, I played around it and finally got it it to work.

But, I have another issue now. Since I have to create all my parameters in Command, I am struggling with multi select parameter that should select all the employee ids if '*' is selected.

Earlier I had
if {?Empl}='*' then {employee_id} = {employee_id} else {employee_id} in {?Empl}

Now in command, I am trying do the same thing but it doesn't seems to work.

select * from empl
where employee_id in (case when {?Empl}='*' then employee_id else {?Empl} end)

It works for 1 or all but not if multiple values are entered.

Any suggestions?

 

[lbass]

Please take a look at faq766-6779.

-LB

 

[lbass]

PS. Later versions of CR support multi-value parameters in CR, so you might want to think about upgrading.

-LB

 

[sgoyal11]

I have the 2011 version that does support multi-value parameters, but it's working for multiple value or 'All'.

For example:

Option 1:
employee_id in {?Empl}
I can enter as many values as I want but there is no way to enter 'All'

Option 2;
employee_id = (case when {?Empl} = '*' then employee_id end)
I can get 'All' but cannot enter more than 1 specific values.

Tried
employee_id in (case when {?Empl}='*' then employee_id else {?Empl} end)
Throws error - Invalid Relational Operator

Unfortunately due the complexity of the report, I cannot try that other sub-report method.

 

[lbass]

Within the command screen, are you able to set the parameter to multivalue?

If so, then you would write it like:

(
(
{?Empl}<>’All’ and
Employee_id in {?Empl}
) or
{?Empl}=‘All’
)

You can add All and other values into the parameter screen in the main report AFTER you create the parameter in the command screen.

-LB

 

[sgoyal11]

Yes, I am able to set the parameter to multi value.

I tried the syntax but its throwing error: invalid relational operator, when more than 1 Empl ids are entered.

This part of the query doesn't work together.

(
{?Empl}<>’All’ and
Employee_id in {?Empl}
)

 

[lbass]

Please post a copy of the command that is not working.

-LB

 

[sgoyal11]

select * from EMPL
where

(
(
{?Empl}<>’All’ and
Employee_id in {?Empl}
) or
{?Empl}=‘All’
)

 

[lbass]

This looks nothing like your original post—-where is the second table? Are you just checking portions of the query or something? What type of database are you working with

-LB

 

[sgoyal11]

My original Query is too complicated. I do not want to complex the things. This is the other parameter that I need to use in the report. I am using Oracle DB.

So I was testing this portion of query in the simple test report. If I can get this to work, I will plug it in my original query.

 

[lbass]

I think you used my punctuation marks--they are from ipad and a character set that doesn't work in CR. Please replace with standard keyboard apostrophes.

-LB

 

[sgoyal11]

I did that, but posted wrong here. Sorry!

select * from EMPL
where

(
(
{?Empl}<> 'All' and
Employee_id in {?Empl}
) or
{?Empl}= 'All'
)

Crystal reports accepts only Employee_id in {?Empl} part, if I add anything else to this it fails.(Invalid relational Operator)
With the above syntax, I got only 'All' or 1 employee_id.
If I just use Employee_id in {?Empl} then I am getting the expected results but there is no way to get 'All'.

 

[lbass]

Can you verify that:

employee_id in {?Empl}

...works by itself?

-LB

 

[sgoyal11]

Yes, it does.

 

[lbass]

It should work then. Employee_id is a string, correct? Not sure what else to suggest. Maybe send the CR along to me to troubleshoot.

-LB

 

[sgoyal11]

Yes, it is string.

Can you please try to do the same with Xtreme Database with Customer ID and get it to work?