DataSet Filter

Hi,

I am converting a crystal report to BIRT, in which the crystal report has a report selection filter condition as below :

if {Customer.Country} = "USA" then {Customer.Customer Name} = ["7 Bikes For 7 Brothers","Against The Wind Bikes","Bicycle Races","Bikefest"]
else if {Customer.Country} = "England" then {Customer.Region} = ["Greater Manchester","Devon","West Yorkshire"]
else if {Customer.Country} = "Switzerland" then {Orders.Order Date} < date(2004,1,1) and {Orders.Order Amount} < 1000
else
{Customer.Country} in ["Australia", "Brazil", "Canada", "China", "France", "Germany", "Italy", "Japan", "Mexico", "New Zealand", "Singapore", "Switzerland", "Portugal"] and
{Customer.Region} <> "Ile de France" and
{Customer.Customer Name} <> "Fahrkraft R?der"

In BIRT report, I am unble to set this filter condition in the dataset filter.

Please help if anybody have idea on such type of dataset filtering.

Thanks in advance.

Rajani Gunda.

Find more posts tagged with

Comments

Hans_vd

Hi Rajanig, 
 
What is the meaning of this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>if {Customer.Country} = "USA" then {Customer.Customer Name} = ["7 Bikes For 7 Brothers","Against The Wind Bikes","Bicycle Races","Bikefest"]</pre>
 
Can you explain this condition? 
 
Regards 
Hans

rajani_interwove

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="107652" data-time="1343633227" data-date="30 July 2012 - 12:27 AM">
Hi Rajanig, 
 
What is the meaning of this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>if {Customer.Country} = "USA" then {Customer.Customer Name} = ["7 Bikes For 7 Brothers","Against The Wind Bikes","Bicycle Races","Bikefest"]</pre>
 
Can you explain this condition? 
 
Regards 
Hans </blockquote>

rajani_interwove

Here, I have to filter the Customer table.

if country="USA" then all the rows with CustomerName="7 Bikes For 7 Brothers","Against The Wind Bikes", "Bicycle Races", "Bikefest" should be picked from the database.

else if country="England" then Region="Greater Manchester", "Devon","West Yorkshire" should be picked.

else if country="Switzerland" then OrderDate should be lessthan 2004-01-01 and OrderAmount <1000

and so on.....

Here I am unable to write a filter condition with if-then-else condition in the filter expression of the dataset.

Hopes this may help you...

Thanks
Rajani

Hans_vd

If you can modify the SQL, you could do it like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>WHERE ( Customer.Country = 'USA' AND
Customer."Customer Name" IN ('7 Bikes For 7 Brothers','Against The Wind Bikes','Bicycle Races','Bikefest')
)
OR ( Customer.Country = 'England' AND
Customer.Region IN ('Greater Manchester','Devon','West Yorkshire')
)
OR ( Customer.Country = 'Switzerland' AND
Orders."Order Date" < date(2004,1,1) AND
Orders."Order Amount" < 1000
)
OR ( Customer.Country IN ('Australia', 'Brazil', 'Canada', 'China', 'France', 'Germany', 'Italy', 'Japan', 'Mexico', 'New Zealand', 'Singapore', 'Switzerland', 'Portugal') AND
Customer.Region <> 'Ile de France' AND
Customer."Customer Name" <> 'Fahrkraft R?der'</pre>
 
 
 
or if you can't change the SQL, you could create 4 tables, each with 1 of the groups of conditions. 
 
Hope this helps 
Hans

rajani_interwove

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="107659" data-time="1343652024" data-date="30 July 2012 - 05:40 AM">
If you can modify the SQL, you could do it like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>WHERE ( Customer.Country = 'USA' AND
Customer."Customer Name" IN ('7 Bikes For 7 Brothers','Against The Wind Bikes','Bicycle Races','Bikefest')
)
OR ( Customer.Country = 'England' AND
Customer.Region IN ('Greater Manchester','Devon','West Yorkshire')
)
OR ( Customer.Country = 'Switzerland' AND
Orders."Order Date" < date(2004,1,1) AND
Orders."Order Amount" < 1000
)
OR ( Customer.Country IN ('Australia', 'Brazil', 'Canada', 'China', 'France', 'Germany', 'Italy', 'Japan', 'Mexico', 'New Zealand', 'Singapore', 'Switzerland', 'Portugal') AND
Customer.Region <> 'Ile de France' AND
Customer."Customer Name" <> 'Fahrkraft R?der'</pre>
 
 
 
or if you can't change the SQL, you could create 4 tables, each with 1 of the groups of conditions. 
 
Hope this helps 
Hans </blockquote>
 
 
 
Thank you Hans. 
I have created SQL query as you suggested. Its working fine. 
 
Thanks again for the quick response. 
 
-Rajani Gunda

Hans_vd

You're welcome