Prompt 'ALL'

[system]

Hi folks,

Hope you can help me with this one,

I have an ‘Object1’ which is of ‘Numeric’ datatype and it contains a where condition.

I want to create an ‘ALL’ object where if you select the ALL value will display all the LOV’s of the ‘Object1’.

I created an object called ‘Object2’ for the ALL object,with the sql given in FAQ’s in the forum like the following,

SELECT ‘ALL’ UNION SELECT Distinct CAST(Object1 as varchar) “since Object1 is of numeric value” from table1 where condition1.

Now I also created a condition for ‘ALL’ condition just like in FAQ’s,

(@Select(Class2\Object2) IN @Prompt(‘Enter …’,‘C’,‘Class2\Object2’,MULTI,free))
OR
( ‘ALL’ IN @Prompt(‘Enter…’,‘C’,‘Class2\Object2’,MULTI,free))

Apart from doing this I am getting an error message,

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value ‘[ALL]’ to a column of data type int.State: 22018.

How can i resolve it?

I am using MS SQL Server as my Database or back end .

---

kart1981 (BOB member since 2006-10-24)

[system]

The bottom line is that you must use the same datatype for a given prompt. I know this was asked recently, but couldn’t find it.

So, when you have a numeric object – you have to choose something for your “ALL” equivalent that is another numeric value. For example, if your numeric object cannot be a negative number, you could say “-1 for ALL”.

---

Anita Craig (BOB member since 2002-06-17)

[system]

Check if this link helps you…

Prompt with 'ALL'

Hi, I need to have a prompt, for example for object1.How can i add 'ALL' in the list of values and when I select 'ALL' all the LOV should be displayed. I tried the code below but not getting exactly what I needed.Its propmting twice for object1. (@Select(class\object1) IN @Prompt('Select .....enter ALL for all object1','A','class\object',MULTI,FREE) ) OR ('ALL' in @Prompt('Select..... or enter ALL for all groups','A','class\object',MULTI,FREE)) I tried with the code which is given in FAQ…

---

cris (BOB member since 2006-10-17)

[system]

yeah i am using the same datatype but still not able to figure out.

---

kart1981 (BOB member since 2006-10-24)

[system]

(CAST(@Select(Class2\Object2) as VARCHAR) IN @Prompt('Enter ..','C','Class2\Object2',MULTI,free)) 
OR 
( 'ALL' IN @Prompt('Enter..','C','Class2\Object2',MULTI,free)) 

That should do the trick.

Regards,

Gerard

---

highandstoned (BOB member since 2005-08-01)

[system]

Hi there,
Is it really required to have ‘ALL’ as the input when you need to choose all the values. I would recommend you to use 999999 in placed of ‘ALL’ when it is numeric else use ‘ALL’ when it is character as for date again we need to choose a date.
Cheers
-Arpan

---

arpan (BOB member since 2006-03-22)

[system]

I tried this code out.I dont get any error but, when I click the ALL value among the LOV’s in the report level and when I run the report,I am not able to see all the values,rather I an able to see only value that is the value ‘ALL’ by itself.

I am not able to get it show all the values ,when using ‘ALL’ object.Any help on this is very much appreciated.

---

kart1981 (BOB member since 2006-10-24)

[system]

If i am correct you want the ALL to be present in the LOV so that whenever you choose that you will get all the values.
I hope you are changing the LOV of the object by doing a union of the object with another object with ALL as value.
But for this you need to create an object with 999999 rather than ALL as you need numeric value rather than character.
After that need to do the union of the created object with the already existing object in the Edit (Object Properties).
After that need to create the filter as mentioned above .
I hope it would work after that
Cheers
-Arpan

---

arpan (BOB member since 2006-03-22)