Dealing with Time Stamps on Dates in Business Objects

Dealing with Time Stamps on Dates in Business Objects

From Business Intelligence

Jump to: navigation, search


[edit] Some Issues with Using Dates in Prompt Responses:

When building a Business Objects application, it is often desirable to incorporate a User-provided date (prompt response) as an element within either a title or formula. Typically, in regards to titles, what the application builder and/or user finds is that they are limited to prompt-response date expressions that look like either 7/1/2008 or 7/1/2008 12:00:00 AM; and that the expression will appear in one format when you have just refreshed the query, and another one if you have just opened a previously refreshed/saved version. In none of these several cases are you able to see a date that looks like – July 1, 2008. And, as far as using Prompt response dates in formulas - i.e., If((UserResponse ([Rev/Exp Jnl's];" 3. Start Date (e.g. 07/1/2007):")>[PG Start Date];… - well…when was the last time you even tried. After all, who has time to figure out how to resolve invalid data type error messages.

After you finish reading the paragraphs below you will understand why date responses work the way they do; more importantly, you will also understand what you can do to make these date responses work they way you want them to, and you will even find a formula model that you can adapt for your own repeated use in the future.

[edit] How BusinessObject Treats the Dates You Enter in a Prompt:

Firstly, you need to understand that all of your prompt responses are used within query criteria in terms of what the responses indeed represent. So, if you respond to a prompt for a start date, your response date will be the criteria date against which all queried transactions will be measured against. HOWEVER, the actual prompted date response that you provided is saved within the application as a text string element. So, when you refer to the prompt response itself within the reporting application (as opposed to within the data provider query criteria), all the application “sees” is a batch of alpha-numeric characters. While BusinessObjects can express a functional date in a number of ways, it can only format a text string’s font. Similarly, a formula comparing one value to another requires that all value elements be expressed in the same terms, whenever you try to compare the text string of your date prompt response to a functional date, all you will ever get is an invalid data type error message.

Happily, Business Objects does provide a means of converting strings to dates (as well as strings to other number formats) and vice versa. So, to get what you want from a prompt response, all you basically need to do is turn that response into a real, live date. Easy enough in theory, except that you must have your conversion formula account for every individual character contained within your response; otherwise your efforts will bring back an #ERROR message. Unhappily, BusinessObjects can switch the number of stored characters in your response around on you – so sometimes you will see an #ERROR and sometimes you won’t.

One of the many quirky features of BusinessObjects is that whenever you open a saved query, all date-related prompt responses will have been cached with an Hours:Minutes:Seconds AM/PM (hh:mm:ss a) time stamp attached. However, when you refresh, you are able to hand enter date prompt responses with or without including the time stamp; if you pick a date directly from the prompt’s calendar, there will be no date stamp. The response will then be cached without a time stamp, until such time as you close out the application and reopen. When you reopen, you get the time stamp.

The tricky technical part then is that the textual elements of the prompt response may or may not have the hh:mm:ss:a stamp immediately after a refresh. If you don’t include hh:mm:ss a in the refresh answer and you “accounted” for hh:mm:ss a in your conversion formula; then as things presently stand, you would get back an error that would “go away” when you save and then reopen the file. Remember - the saved version always includes hh:mm:ss a characters, even though you might not have included them in your prompt response.

[edit] Formulas that work to Resolve the Issues with Date Prompt Responses:

The formulas below walk you through the thinking and processes to get to the type of end result you want. Note that the first three formulas all have a ToDate( at the beginning, and a date format at the end. These pieces are the formula components that turn a text string into a date. The date format is specifically what is used to “account” for all the text string’s characters.

1. =ToDate(UserResponse ([Rev/Exp Jnl's];"3. Start Date (e.g. 07/1/2007):");"MM/dd/yyyy hh:mm:ss a") – When refreshed with "7/1/2008", will return an error; but once saved and reopened will show the correct value. If refreshed with "7/1/2008 12:00:00 AM", will return values correctly before and after save/reopen.
2. =ToDate(UserResponse ([Rev/Exp Jnl's];"3. Start Date (e.g. 07/1/2007):");"MM/dd/yyyy”) – When refreshed with "7/1/2008", will show return value correctly; but once saved and reopened will show an error value. If refreshed with "7/1/2008 12:00:00 AM", will return an error before and after save/reopen.
3. =ToDate(UserResponse ([Rev/Exp Jnl's];" 3. Start Date (e.g. 07/1/2007):");If(Length(UserResponse ([Rev/Exp Jnl's];" 3. Start Date (e.g. 07/1/2007):"))>12;"MM/dd/yyyy hh:mm:ss a";"MM/dd/yyyy")) – no matter how you refresh, will return the correct value either before or after reopen.

The last option is the most versatile, and will stand up even if things shift later with how the BusinessObjects platform handles time stamps overall (which it might at some point!). The key here is the use of the If(Length(value)>x;result a;result b) formula component. The Length() function counts the number of characters including spaces and symbols, within the data variable, or formula result cited.

In the case of this last formula then, if there are more than 12 characters, the overall formula will convert the string to the date format that shows a full time stamp. If less than 12 characters are counted, then the overall formula will convert the string in the format the leaves out the full time stamp. Each time the formula is evaluated, it will reformat based on the length of the text string at that time - so, it will calculate one way right after a data refresh where a time stamp was not used, and a different way once you close and reopen the query. Do note that the If statement here was used only for determining which format component to use within the overall ToDate() formula.

Once you have used the last formula above to account for all the prompt response characters and thus transformed them into a functioning date, you can go ahead and either Format individual cells to reflect however you want to show the date, or you can envelope the ToDate() formula with a FormatDate() formula (which turns it back into a text string – but one that looks like what you want it to look like). This last piece is necessary if you plan to incorporate the “long date” within a larger title (i.e. For Journals From July 1, 2008). When turning a functional date into a text string, you do not have to account for all characters; functional dates are at heart just numbers.

4. =FormatDate(ToDate(UserResponse ([Rev/Exp Jnl's];" 3. Start Date (e.g. 07/1/2007):");If(Length(UserResponse ([Rev/Exp Jnl's];" 3. Start Date (e.g. 07/1/2007):"))>12;"MM/dd/yyyy hh:mm:ss a";"MM/dd/yyyy"));"Mmmm d, yyyy")

[edit] This is all way too cumbersome...

Absolutely… yes… this is indeed cumbersome to do; especially if you want to use this functionality throughout your report; and/or if you are likely to wind up changing the wording of a prompt later; and/or if you think you might want to use your application underpinnings in a different application which would necessitate using different data provider names or prompt wording

All of which is exactly why I wouldn’t bother with any of this – at least not directly within my reports and tables. Rather, I would convert either the third or fourth formula above into a PROMPT: Begin Date (an unlinked – yes…unlinked - detail) variable . A quick note here: by creating a variable out of my prompt response, when I change or recycle queries, variables, etc., I can recklessly change the wording around on my prompts, without worrying about having to separately locate all the places throughout my report that I might have referenced the values captured by any of my PROMPT variables. I can always change both the title, and the formula wording in the PROMPT: Begin Date variable, and all references to that variable will update.

In the case of my formulas above; if I was wanting to use the resulting date in logic formulas (i.e., IF([PROMPT: Begin Date]>[PG Start Date];1;0), I would convert the third formula into my a PROMPT: Begin Date variable. This choice would be all about keeping the functionality of the date itself. If I also wanted to be able to use the resulting date in titles, then I would go ahead and turn the date back into a string using a title formula like - ="For period beginning: "+FormatDate(PROMPT: Begin Date];"Mmmm d, yyyy"). If I only wanted to use the response date in titles, then I would go ahead and covert the fourth formula instead.

Bottom line – grab any of my formulas above, tailor them to the specifics of your application(s); then anytime you want to include dates from prompt responses, just insert your PROMPT variables instead. By the way – you are saving these types of useful formulas to your own personal BusinessObjects library in either a Word or Excel file somewhere…right? After all, who has time to keep reinventing wheels…