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.
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))
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.
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))
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))
@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.
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.
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?
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?
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
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.