Hi Folks,
Please help me I am stuck, I searched everywhere for answer and finally posting..

Environment: Crystal Reports 2011, DB2, AS400 server
Issue: Trying to display 'All' in drop-down list of a Dynamic parameter. But drop-down shows only 'All' value else if I change sql in command, it shows only lovs, but not 'ALL'
SQL: Displays just 'All' in drop-down list
select 'All' as customer, Col2, col3, col4 from Tab1 where condition1
Union all
       select col1 as customer, col2, col3, col4 from Tab1 where condition1
(OR)
SQL: Displays just col1 values but not 'All'
select col1 as customer, col2, col3, col4 from Tab1 where condition1
Union all
select 'All' as customer, Col2, col3, col4 from Tab1 where condition1

More info: Added condition in Record selection formula - included brackets
({?Customer} = "ALL" or {Command.CUSTOMER} = {?Customer})

But I am not sure why it is not displaying value 'All' and lovs together, please help me if I am missing something. Thanks in Advance.


Edited by shanth - 27 Aug 2012 at 5:10am

Thank you for quick response..
I removed where clause(condition) for first statement befor Union all. But still it shows only '..All' shows up in drop-down list.
Let me give you exact sql, maybe I am missing something..
         select '...All' as Customer, col2, col3, col4, col5, col6 from
                   tab1 join tab2 on colx=coly
                   tab2 join tab3 on coly=colz
          Union all
         select col1 as Customer, col2, col3, col4, col5, col6 from
                   tab1 join tab2 on colx=coly
                   tab2 join tab3 on coly=colz
                   where condition1, condition2

col1 has values : cust1, cust2, cust3
I need: in drop-down list: ..All, cust1, cust2, cust3
if I reverse sql statements back n forth to (union all), parameter displays whatever is first, if is '..All' first, then it displays only ..All else if col1 is first befor union all, then displays only values of col1(cust1, cust2, cust3)
 

Not sure it didnt worked in my case, maybe due to DB2
But with below approach it worked,
I used below SQL in new command2 and joined Customer to Col1 in Command 1:
select '..ALL' as customer from SYSIBM.SYSDUMMY1
union all
select distinct col1 as customer from tab1 where col1 in ('X', 'Y', 'Z')

 Report selection Formula: (If {?Customer}='ALL' Then col1 like '*' Else col1 = {?Customer})
Thanks for your help!!