Select 'ALL' 1

[byteias]

I created a dynamic LOV and I will like to add 'ALL' to it, so the user can pick one, many or 'ALL' projects.
Any idea of how to do it?
Also, how do i get rid of the commas in my project (numeric field0 when they select from my LOV?
Thanks.
Ivan.

 

[Madawc]

Assuming this isn't a spoof (dynamic LOV) is a shade suspicious, but I've seen odder things in database names):

Create a formula

@paramA = "ALL"
or 
{project} in [@paramA to @paramB]

Best to put the test beside the raw data before using it for selection.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Dynamic LOV" means dynamic list of values.

You would have to create a command like:

Select 'All', 'All'
From Table
Union
Select Table.`Field`, Table.`Desc`
From Table

Then use the command when referencing the fields for your list of values. The second field is for your description value.

-LB

 

[synapsevampire]

For the sake of safety, that shuld be:

Select 'All', 'All'
From Table
Union ALL
Select Table.`Field`, Table.`Desc`
From Table

-k

 

[synapsevampire]

OOops, should have read:

Select '...All', '...All'
From Table
Union ALL
Select Table.`Field`, Table.`Desc`
From Table

-k

 

[lbass]

SV,

I don't follow what adding the ellipsis does. I also was unable to get the command to work correctly when making it a "union all" instead of a "union". So I'm not sure what safety aspect you are referencing.

-LB

 

[fsreport]

hi
try this
(if @param <> 'ALL"
then
{project} = @param
else if
@param = 'ALL'
then @param in [@paramA,@paramB]


fsreport

 

[synapsevampire]

The ellipses will assurethat it comes out in front foreasy selection.

The Union ALL statesbring all data from both sides, but you'reright, the ALL shouldn't matter here.

Idon't understand what " also was unable to get the command to work correctly when making it a "union all" instead of a "union". "

Perhaps youcouldstate whether it errored, orreturned wrong data, or the monitor fell over and now you can't read it...

UNION says give me both sets of data less dupes, UNION ALL says bring everything.

-k

 

[lbass]

I know the difference between a union and union all, but have found that in this situation (where only a hard value is used on one side of the union, i.e., "All"), a union all returns only the "All" while a union will return "All" plus the values from the other side of the union.

-LB

 

[synapsevampire]

What?!?!?!

Makes no sense to me, LB, and I do this often (in command objects.

I'mworking on an Ants database and even it supportsit:

SELECT '...All' USER_NAME FROM "INFORMATION_SCHEMA"."ANTS_ALL_USERS" "ANTS_ALL_USERS"
UNION ALL
SELECT "ANTS_ALL_USERS"."USER_NAME"
FROM "INFORMATION_SCHEMA"."ANTS_ALL_USERS" "ANTS_ALL_USERS"

The Ellipses then purdy much guarantee first placement in the list.

Not sure why you have different results, obviously SQL should perform as I've indicated.

Perhaps it's a LOV thang... Note that I avoid tproprietary database layers and have as little experience as is possible with Universes, LOVs, etc...

-k

 

[lbass]

SV-,

Using the Xtreme database, create a command:

Select 'All'
From Orders
Union All
Select Orders.`Ship Via`
From Orders

Then use this to populate a dynamic LOV. Only the "All" will appear in the LOV when you refresh the report. If you change it to "Union" instead of "Union All", all shipper values and "All" appear in the dynamic LOV.

-LB

 

[buck149]

why don't you try to put the following in your selection?

iif({?yourparameter}="ALL",TRUE,{project}IN{?yourparameter})

 

[drgsolutions]

This is all very helpful except that I already have used a command as the source of my report. When I attempt to write a 2nd command that would be the source of my parameter list the Database Expert sends up a second dialog box asking me to link the command statements together.

Am I missing something.

BTW, I am using Crystal Reports 11/XI

 

[lbass]

Do not link the commands. Just ignore the message. Be sure that you only reference the second command when setting up the dynamic list of values--do not reference it in the body of your report.

-LB