×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Conditional Link to the table in Crystal Reports 2011

Conditional Link to the table in Crystal Reports 2011

RE: Conditional Link to the table in Crystal Reports 2011

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

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
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

RE: Conditional Link to the table in Crystal Reports 2011

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

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
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?

RE: Conditional Link to the table in Crystal Reports 2011

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

-LB

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
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.


RE: Conditional Link to the table in Crystal Reports 2011

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

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
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}
)

RE: Conditional Link to the table in Crystal Reports 2011

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

-LB

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
select * from EMPL
where

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

RE: Conditional Link to the table in Crystal Reports 2011

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

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
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.

RE: Conditional Link to the table in Crystal Reports 2011

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

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
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'.

RE: Conditional Link to the table in Crystal Reports 2011

Can you verify that:

employee_id in {?Empl}

...works by itself?

-LB

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
Yes, it does.

RE: Conditional Link to the table in Crystal Reports 2011

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

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
Yes, it is string.

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

RE: Conditional Link to the table in Crystal Reports 2011

My current version (CR XI) does not allow multivalue parameters in a command, so I can’t test this. I also don’t currently have access to an Oracle database, and Xtreme is an Access database, which sometimes behaves differently. Were you testing this with the Oracle? Or with Xtreme?

I don’t understand why you would get multiple results without the ‘all’ clauses, but only single ones with. Maybe you can verify that again. Make sure you are testing with oracle.

-LB

RE: Conditional Link to the table in Crystal Reports 2011

Try this:

select * from EMPL
where
'All' in (?Empl} or
Employee_id in {?Empl}

-LB

RE: Conditional Link to the table in Crystal Reports 2011

(OP)
It WORKED! Thank you so much. That must be the only combination I did not try.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close