Can we add All/NA to the list of Values

system · August 26, 2007, 6:01pm

Hi,

Can we add " All/NA " to the list of Values. Please advise. Also it should work. Requirement - I want to use this in a promt and if I select All, then all the values from the LOV should be selected.

Thanks
GM

Gouri_mishra (BOB member since 2007-06-19)

system · August 26, 2007, 8:02pm

Yes we can.

Have a look at 2 first entries in the Designer’s FAQ.

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

system · August 27, 2007, 9:46pm

Thank you Marek Chladny,

I have one more doubt. The code given below seems to be a @prompt function. So will it be a problem if I use this object in report output also. Please conform.

(@Select(Sales Geography\Country Group) IN @Prompt(‘Select Country Group or
enter * for all groups’,‘a’,‘Sales Geography\Country Group’,multi,free) ) OR
(’*’ in @Prompt(‘Select Country Group or enter * for all groups’,‘a’,‘Sales
Geography\Country Group’,multi,free))

Thanks
GM

Gouri_mishra (BOB member since 2007-06-19)

system · August 27, 2007, 9:56pm

Looks good to me.

Try it and see

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

system · August 28, 2007, 1:32pm

Hi

Can we add " All" to the list of Values for date object?

my code is

SELECT DISTINCT
xyz.dbo.table1.Appoinment_Date
FROM
xyz.dbo.table1
UNION
select ‘…All’

when i refresh the list of values in designer it showing error no:DA0003

Error Description:

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.State: 22007

can any one help me how to solve this…

Kernal (BOB member since 2007-07-12)

system · August 28, 2007, 1:40pm

All components of a union query have to share the same datatype. Since “All” is not a valid date, it will not be valid. A fairly standard approach here is to designate a “magic date” like 1/1/1900 and use that to trigger your “all” logic.

To be honest, most dates should not have a list of values anyway, so this is rarely an issue.

Dave Rathbun (BOB member since 2002-06-06)

system · August 29, 2007, 7:04am

Thank you DAVE

it’s Working Fine

Kernal (BOB member since 2007-07-12)

system · August 29, 2007, 7:24am

You are welcome, glad to have helped.

Dave Rathbun (BOB member since 2002-06-06)

system · August 30, 2007, 11:23am

Hi Marek Chladny/Dave,

I used the following query in the where box of the object properties. But while parsing it did not work. Please let me know if I need to any thing else to add ALL to the list. Again ALL is a character type of data. So can we add this to the LOVs of numeric data types? Please help me.

Note I am using BO XI R2 SP2.

Thanks
Gouri

(@Select(Sales Geography\Country Group) IN @Prompt(‘Select Country Group or
enter * for all groups’,‘a’,‘Sales Geography\Country Group’,multi,free) ) OR
(’*’ in @Prompt(‘Select Country Group or enter * for all groups’,‘a’,‘Sales
Geography\Country Group’,multi,free))

Gouri_mishra (BOB member since 2007-06-19)

system · August 30, 2007, 12:15pm

Hi Kernal,

Can I get the exact code you used to add “ALL” to the date field.

SELECT DISTINCT
xyz.dbo.table1.Appoinment_Date
FROM
xyz.dbo.table1
UNION
select ‘…All’

It is not working for me. I need to add N/A to the list instead of ALL.

Thanks
GM

Gouri_mishra (BOB member since 2007-06-19)

system · August 30, 2007, 5:31pm

Hi Marek Chladny,

This is working for me for char type data. But I am getting an error message when used for numeric data type. Instead of ALL I used a numeric data type 0 (Zero) to make this work as ALL did not work. Parcing is ok. I exported the universe. But when I try using it in my report I am getting the error message an internal error occurred while calling ANSWERPROMPTs API. (Error: WIS 30270). Find below the query used. I used it in pre-condition. Please let me know if I need to do something else.

(@Select(Sales Geography\Country Group) IN @Prompt(‘Select Country Group or
enter 0 for all groups’,‘N’,‘Sales Geography\Country Group’,multi,free) ) OR
(‘0’ in @Prompt(‘Select Country Group or enter 0 for all groups’,‘N’,‘Sales
Geography\Country Group’,multi,free))

Thanks
GM

Gouri_mishra (BOB member since 2007-06-19)

system · August 30, 2007, 6:40pm

Hi,

What type is object Sales Geography\Country Group of? Is it of a number type?

If yes then use 0 (right after OR clause) in the LOV without quotes. It is number without quotes and string if the quotes are there.

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

system · August 30, 2007, 7:28pm

Hi Marek,

Yes. It is numeric. See the code I used.

@Select(SHARED_ACCUM_STNDRD_CLM\CONTROL_NUMBER) IN @Prompt('Choose CONTROL NUMBER or Enter 00 for All CONTROL NUMBERS','N','SHARED_ACCUM_STNDRD_CLM\CONTROL_NUMBER',multi,free,NOT_PERSISTENT,) or 00 in @Prompt('Choose CONTROL NUMBER or Enter 00 for All CONTROL NUMBERS','N,'SHARED_ACCUM_STNDRD_CLM\CONTROL_NUMBER',multi,free,NOT_PERSISTENT,) or 00 in @Prompt('Choose CONTROL NUMBER or Enter 00 for All CONTROL NUMBERS','N','SHARED_ACCUM_STNDRD_CLM\CONTROL_NUMBER',multi,free,NOT_PERSISTENT,)

Parsing ok. But it does not work in reports. Please let me know in case I need to do some formating.

Again for Char type of data it is working fine. But the total # of records are different from the total # of records available in the Database. Please let me know if I am missing some where. I am using BO XI R2 SP2.

Note - Database used is DB2 V8

Thanks
GM

Gouri_mishra (BOB member since 2007-06-19)

system · August 30, 2007, 9:57pm

You have 2 almost identical OR parts in the condition. Is it on purpose or is it a typo? I write identical because the second part has ,'N, and the third one has ,‘N’,

What does it mean that it is not working in report? Do you get any error message?

I dont know DB2 V8 but I assume that 0 (zero) should be 0 and not 00.

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

system · August 30, 2007, 10:30pm

Thank you Marek.
It was a typo. Hence it was not working. Now it is working. But the problem is the total count is not matching.
Say I have 4 values.
0001, 0002, 0003 and 0004 and 0(for all). Individual counts are

0001 - 10(number of records)
0002 - 10(number of records)
0003 - 10(number of records)
0004 - 10(number of records)

Total- 40

So total # of records available is 40 (adding all four). So when I select 0 (meaning ALL) I should get all the 40 records. But I am getting less number of records. How can I resolve it?

Thanks
GM

Gouri_mishra (BOB member since 2007-06-19)

system · August 30, 2007, 10:42pm

Gouri_mishra,

It is really hard to help you if all that you provide is the syntax of a condition. If we dont know how the data looks like, how the universe objects are defined, how the SQL is constructed, how the report is formatted then nobody is able to do a magic and say why you are getting less rows and how you could resolve the problem

Just a thought:
How many rows do you see in Data Manager? Is not the problem due to the aggregation of the duplicate rows?

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

system · August 31, 2007, 3:38am

HI GOURI,
I am using BO XIR2, Sql Server

code i Have used in List of Values

SELECT DISTINCT
xyz.dbo.table1.Appoinment_Date
FROM
xyz.dbo.table1
UNION
select ‘1/1/1900 12:00:00 AM’

In Where clause of the Object

@Select(xyz dbo table1\Appointment Date) IN
@prompt(‘Enter values forAppointment Date’,‘A’,‘xyz Dbo table1\Appointment Date’,Multi,Free)
OR (‘1/1/1900 12:00:00 AM’) IN
@prompt(‘Enter values forAppointment Date’,‘A’,‘xyz Dbo table1\Appointment Date’,Multi,Free)

This gives me single prompt with all and multiple selection of list of values
select Magic date '1/1/1900 12:00:00 AM’i n list of values it acts as All

Kernal (BOB member since 2007-07-12)

system · September 1, 2007, 10:41am

Can we add All to the list of Values for cascading Prompt?

Kernal (BOB member since 2007-07-12)

system · September 4, 2007, 5:12pm

Hi Marek/Dave,

Thank you for your help. I am facing a problem in sorting/prioritizing the prompts. I used the formula mentioned above to create some pre defined conditions and used these as prompts with some normal prompts in query filters panel. But when I click on run query, I can see all the prompts and the pre defined conditions (that are used as prompts) but not in the way I arranged. They are seen in sorted order. Is there a way to arrange them differently (not in sorted order)?

Note - I clicked on the edit query panel, then properties and prompt order. Here I can see only the prompts I used but not the pre defined conditions that I used as query filters.

Appreciate any help.

Thanks
GM

Gouri_mishra (BOB member since 2007-06-19)

system · September 8, 2007, 8:01am

Hi
can we Set default value for the as ALL?

code i Have used in List of Values

SELECT DISTINCT
xyz.dbo.table1.Appoinment_Date
FROM
xyz.dbo.table1
UNION
select ‘1/1/1900 12:00:00 AM’

In Where clause of the Object

@Select(xyz dbo table1\Appointment Date) IN
@prompt(‘Enter values forAppointment Date’,‘A’,‘xyz Dbo table1\Appointment Date’,Multi,Free)
OR (‘1/1/1900 12:00:00 AM’) IN
@prompt(‘Enter values forAppointment Date’,‘A’,‘xyz Dbo table1\Appointment Date’,Multi,Free)

the above code is working fine, by default prompt calling with out giving any prompt in ( WEB I) query filters

for this code how to set keep last values selected ?

i know that when we have given prompt in quey filters in Web I by enabling option “Keep last values selected” check box at prompt properties.

Kernal (BOB member since 2007-07-12)

Can we add All/NA to the list of Values

0001 - 10(number of records) 0002 - 10(number of records) 0003 - 10(number of records) 0004 - 10(number of records)

0001 - 10(number of records)
0002 - 10(number of records)
0003 - 10(number of records)
0004 - 10(number of records)