I would need to add a new field called “ALL” at the top of the list of values of a Date Dimension.
The “ALL” field should contain all the dates in the list and it should get up to date daily.
My problem is to understand how to create the new field, I can manage a list of values of an object, but not enough to create a new field inside the list.
The aim is to allow the user to select “ALL” in the prompt built around this date dimension.
The FAQ entry does show how to add “All” to a list of values, but it won’t work for dates. The data types have to be consistent, so you can’t mix “All” with date values and expect it to work unfortunately. You can consider using a special “magic” date and code accordingly. I often use something like 1/1/1900 or some other date that will never appear in the database.
I tried to follow the FAQ entry but (as Dave notified) it doesnt work.
Then I tried to convert the data type of my date object (transform it into a caracter type object), but BO 6.5 restitutes me the error:
Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.State: 22007
Dave, I have not understood very well how to use the special “magic” date 1/1/1900
(even if it is not in the DB, I wouldnt like to force the users to digit, or to show in the list of values of my prompt, a date like 1/1/1900).
First suggestion: skip providing a list of values for the date. The calendar widget provides a much nicer input method, and in most cases a user can type a date faster than they can find it on a list anyway.
Regarding the magic date process, I saved my thoughts in a blog post some time back.
(BOB member since 2008-05-12)
(BOB member since 2002-06-06)
The calendar widget provides a much nicer input method, and in most cases a user can type a date faster than they can find it on a list anyway.