Feb 16 2008
Making the Best Use of Your Time (in a Prompt)
One of the most frequent questions that comes out of either the “building reports” or “universe design” areas on BOB has to be: how can I get rid of the time on my date prompts? And it’s not a new question; I bet if I searched I could find the same questions dating all the way back to 1998, which are the oldest posts we have on the board.
The overwhelming complaint generally boils down to, “… but my users don’t like it.” I get that, certainly I do. But to be honest it’s easier to spend an hour or two of your time 😉 convincing the users that it is more cost effective to leave it than to try to change the way the tool works. It’s not worth the time to remove the time when it works perfectly fine if you ignore it.
Normally I am all about trying to figure out a creative solution and do the “outside of the box” thinking. People that know me understand that one of the best ways to motivate me to solve an issue is to say something along the lines of “… and Business Objects said it can’t be done.” Well, then I have to do it. 😆 But not in this case.
Change the Format
One common work-around is to convert the prompt to a character string where you have absolute control over how the date is presented. The primary issue with that solution is you no longer have a date. If you want to try this out, you can create an object in Oracle that uses this formula:
to_char(table.date,'YYYY-MM-DD')
When you use that as a list of values, it will not have any time element and it will sort in the proper order. It will also require users to enter the date in exactly that format; nothing else will do. Using this technique means that…
- Dates don’t sort in date order unless you use the YYYY-MM-DD format
- Can’t pick from a calendar (Web Intelligence Only, Desktop Intelligence never got the calendar)
- No date functions (like DaysBetween() and so on) will work
- Users must enter the date in exactly that format, since it really isn’t a date but a pattern of character data now
- … probably more, but those are what I came up with for now
Change the Code
Or you could dig into the calendar widget code and remove the time at the source.
Ouch. 😯
I don’t like this solution at all, for many reasons. First, changing the application code is or should be (in my opinion) the last resort. Everytime you get a patch or an upgrade you will have to retest your code. Every time you want to log a case or defect with technical support you will have to prove that the defect exists in a standard calendar and was not something introduced by your customization. And how much time would it take to figure out what to modify and then change it? I submit that the one-hour meeting with your users to explain that it is a feature and not a bug is a much better use of your time.
And one of the things that people probably don’t consider when they suggest this is… sometimes you want the time present in the prompt! Most of the time you probably don’t. But if you go the route of tweaking the application code to eliminate the time, it’s gone. Now if you have a prompt where you need to include a time value, you will have to allow the user to enter the full time as a string and convert it, or do something other than the standard process.
Enhancement Request
Business Objects does take enhancement requests. Some of them (ability to export to Excel) take a really long time before they show up in the product. Others (optional prompts) still are not a fully implemented feature yet. But they do take requests. Somewhere along the line Desktop Intelligence did lose the time portion of a date prompt… but it also does not have a calendar either.
User Education
There is no doubt that this is a popular request, based simply on the fact that there are lots of questions and/or discussion about this happening on BOB. It’s even a FAQ in the Universe Designer forum. My frank opinion is that it’s not worth the time to try to “fix” this, but the best choice is to present the cost / benefit analysis to your users and let them pick which route they want to go.
It might save you a lot of time. 😎
Not being a fan of customizing code at all, I would love if BusinessObjects introduced more time functions: be it prompts with TODAY, YESTERDAY, LAST WEEK, etc. functions/default values, or creating crosstabs with a fixed format, such as last 30 days, no matter if data is present or not, etc.
I know and implemented a few work-arounds, but it is just that: a work-around.
From a developers perspective, I can understand the phrase “not worth the time to try to fix this”.
But from BusinessObjects perspective I can’t understand it. When I would hear things like “frequently asked”, “users don’t like them”, “since 1998” … I would try to find a solution and not propose all kind of workarounds which will stay forever. If they would just look at Crystal Reports and take over some report functions in Webi (including a lot of time functions, date or DateTime Prompts), this would already be a good start.
But developing this means spending $$$’s with almost no return for BOBJ. This kind of issues are almost never a key-breaker in chosing a BI application.
Just try to visualize seconds in Webi (e.g. 3660) into a time format (e.g. 01:01:00). Yes, with complex formulas this is possible but you need to do it for each measure in each report. In e.g. excel, just right click and change format. Reported it, no idea what they will do with it.
Sometimes I have the impression that BOBJ is to busy with looking for new things and forget to improve/enhance (existing) things.
Foad, you are preaching to the choir, as they say. 🙂 I think your views are perfectly valid. Thank you for your comment.
Sigh. There is so much about Business Objects that makes me scream “WHY??? Who designed this thing? Nobody who has ever used it in the real world, that’s for sure!”
Heh, Hi, MissKris, and welcome. Your comment made me laugh, so thank you for that. 😆
It is fairly simple to use dates (instead of datetime) without any of the problems mentioned above.
LOV Object - Calendar Date (Char)
Select: convert(varchar(10), @Select(Dates\Calendar Date), 103)
Where: 1 = 1 )
order by @Select(Dates\Calendar Date) desc --
An alternative is to hard code the LOV SQL. If you are using SQL Server, then you have to remove the DISTINCT from the generated LOV query, because SQL Server will not sort on a column which is not in the select when performing a DISTINCT in the query (Oracle will work fine). The general idea is to produce a date as a char, but ordered by the real date column.
Example Prompt
@Select(Date Time) >= convert(smalldatetime, @Prompt('1. Enter the start date:','D','LOV\Calendar Date (Char)',mono,free), 103)
and @Select(Date Time) <= dateadd(d, 0, convert(smalldatetime, @Prompt('2. Enter the end date:','D','LOV\Calendar Date (Char)',mono,free), 103))
The result will be that the date values will be displayed as dates (without the time element), and you will be able to use the WebI inbuilt calendar selection method.