LOV with ALL and None

[system]

HI Gurus,

We are in the process of converting CRs to Universes. For the CR reports, the parameter Fields have ALL and None values. Which not avilable in the LOV

I want to include ALL and None in my Object LOV. Please help me out in changing the SQL for the LOV in the Select distinct. Back end is SQL Server 2008.

Thanks
RUC

---

RUC (BOB member since 2010-05-03)

[system]

Hi,

This Designer’s FAQ describes it:

FAQ: Designer

Now that my LOV lets me use ALL for all values, how do I get the ALL in the list? Author: Dwayne Hoffpauir There are two approaches - overriding the LOV SQL, and creating a universe object. Overriding the LOV SQL Press the SQL button in the query panel of the LOV Add the phrase UNION SELECT ‘ALL’ FROM DUAL or similar dummy table for your particular database. For SQL Server (if you don’t care if the object parses or not) add SELECT xx From xx UNION SELECT ‘ALL’ (You don’t need a FRO…

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks for the Reply and the link,

I have gone through the Link where it shows only ALL but not for None.

Do I need to include Union for ALL and None both ?? I a newbie Help me out

Thanks
RUC

---

RUC (BOB member since 2010-05-03)

[system]

Hi,

The FAQ describes how to add ALL into an LOV. If you need also NONE to be added into the LOV then just do the same what FAQ talks about but for NONE now.

By the way, what does a report should do with a condition when NONE is selected?

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Add None also in the object query. So it will fetch all LOV’s, All and None but in prompt only use LOV’s and All.

So None will appear in the LOV’s, but when you will select None, prompt condition will fail.

---

Rakesh_K (BOB member since 2007-12-11)

[system]

Hi Rakesh,

Thanks for replying me, Is there any other possibility to add None which it should not bring any LOV’s

Thanks
RUC

---

RUC (BOB member since 2010-05-03)

[system]

Here is my SQL in the LOV, Please help me out to add ‘ALL’ and ‘None’

SELECT DISTINCT
CLARITY.dbo.PAT_ENC.CONTACT_DATE
FROM
CLARITY.dbo.PAT_ENC

Thanks
RUC

---

RUC (BOB member since 2010-05-03)

[system]

Hi,

Have you read the Designer’s FAQ It’s mentioned there how to add values into an LOV if you have SQL server

SELECT DISTINCT
CLARITY.dbo.PAT_ENC.CONTACT_DATE
FROM
CLARITY.dbo.PAT_ENC 
UNION 
SELECT 'ALL'
UNION 
SELECT 'None'

---

Marek Chladny (BOB member since 2003-11-27)

[system]

I tried using the code
SELECT DISTINCT
CLARITY.dbo.PAT_ENC.CONTACT_DATE
FROM
CLARITY.dbo.PAT_ENC
UNION
SELECT ‘ALL’
UNION
SELECT ‘None’

But I when run and test the Lov object, I am only seeing the ALL and None Values.

In my scenario, I need like when I select 'ALL ’ >>>> should include all the list of values and for ‘None’ >>>> should not include any list of values.

Please help me out with any other solution

---

RUC (BOB member since 2010-05-03)

[system]

RUC Did you find out the solution for your problem?

---

BOB newbie (BOB member since 2011-02-15)

[system]

What is your prompt condition.

---

Rakesh_K (BOB member since 2007-12-11)