If you use
prompts asking end-user to enter the dates, you may have faced this issue with
Business Objects Date LOVs (List-of-Values). LOVs show complete timestamp
(MM/DD/YYYY HH:MM:SS AM/PM) instead of just date (MM/DD/YYYY) even though date
is not defined as timestamp in your database.
Following
example explains the problem as well as the solution:-
2) One of the solutions that many forums
suggest is to convert the date prompted in character format. However this leads
to three problems: - 1) The “WHERE” condition will give you wrong results. 2)
You will loose ability to use calendar while selecting a custom date. 3) I get
wrong results as the dates in ranges give incorrect results.
3) My requirement is to show date list
of values as dates without timestamp as well as not to loose ability to use
calendar while selecting a custom date. Off course correct results too!!
4) I create one more object in the
Universe and name it as Time Id Prompt. In this object I will convert the date
into character and use required appropriate format.
5) Now I go to properties tab and name
the list of values as TIMEIDPRMPT.
6) Now edit the list of values, go to
SQL for the List Of Values and you should see something like below :-
Change
the list of values SQL so that you can order the list of values in ASC/DESC
order. Don’t forget to check the button of not generating SQL again:-
7) Now go to your original object Time
Id and just link the list of values by List Name as follows:-
This will
ensure that your list of values is actually pointing to Time Id Prompt List Of
Values.
8) Export the changes and generate a
sample report :-
9) So this helps you accomplish three
things:-
a) Do not display timestamp along with
list date of values in Business Objects.
b) Still have the ability to pick custom
date from calendar.
c) Your results are not wrong as the
underlying value is still in date format.
