system
October 3, 2010, 2:38pm
#1
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
October 3, 2010, 2:55pm
#2
Hi,
This Designer’s FAQ describes it:
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
October 3, 2010, 8:48pm
#3
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
October 3, 2010, 9:22pm
#4
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
October 4, 2010, 2:40am
#5
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
October 4, 2010, 2:27pm
#6
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
October 4, 2010, 2:59pm
#7
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
October 4, 2010, 7:21pm
#8
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
October 4, 2010, 10:39pm
#9
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
July 5, 2011, 6:06pm
#10
RUC Did you find out the solution for your problem?
BOB newbie (BOB member since 2011-02-15)
system
July 6, 2011, 4:56am
#11
What is your prompt condition.
Rakesh_K (BOB member since 2007-12-11)