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.
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.
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.
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.
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
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.
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.
(
(
{?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'.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.