SP4.1 Patch 1 - Date Prompt Issue (SAP CONFIRMED BUG)

server administration XI Server Discussion
1

EDIT - CONFIRMED ISSUE WITH SAP. DO NOT INSTALL 4.1 PATCH 1

Ok has anyone see this yet in their BI4 installation?
We just upgraded to SP1, Patch 1 and today a user contacted me with what appeared to be a bizarre problem.

I’ve been able to duplicate this problem down in a simple webi report.

I have a calendar table (most companies do), with a DATE field. It’s a smalldatetime, but in the universe I convert it to a Date data type . (MS SQL, Cast(Calendar.date as date) ).

Then my WEBI report has a simple query:

I have one display object: “Date”
I have one Query Filter: Where Date BETWEEN FROM:PROMPT1 and TO:PROMPT2.

Sure enough if I run the report and select a date range of 12/31/2012 through today, I initially get all the days… no issue.

But if I hit REFRESH without changing anything, about 7 times… I get a date list instead of 1/1/2013 through tomorrow.

What is happening is the PROMPT box in the WEBI seems to be adding 5 hours to my date selection in the “FROM” side of the between, and 4 hours to the “To” side of the between, each time I hit refresh.

This only happens if I VIEW the report. In Modify/developer mode, the prompt behaves just fine and keeps the date prompt at 00:00:00 timestamp.

It’s really freaky and quite annoying as ALL of our sales numbers are not off since our sales transactions are recorded at the minute level and we have sales 24 hours a day.

I’ve opened a ticket with SAP, but wondered if anyone else has seen this. Below are two screenshots…

UPDATE:
Ok SAP has confirmed that Patch 1 does in fact break the date controls. They have confirmed it happens with UNV universes but seem to think that UNX universe sourced WEBIs might not be affected. (I doubt that myself).

Either way I do not recommend anyone to install 4.1 Patch1. (4.1 itself is OK, it’s only Patch 1 that breaks things).
They still need to do a bit of testing and will get with me tomorrow as to when they might expect a fix, but since this bug affects every date prompt in WEBI for all clients, the tech seemed to think this might end up being an extremely high escalation and response time. (Or they might recall Patch 1 and fix at their leisure I’m thinking)
Query Before.JPG
Query After.JPG

JPetlev (BOB member since 2006-11-01)

2

Hi JPetlev,

Normally I double check the preferences of user and system when the “date problem” appears, every project, every system installation, and almost every day…
Please review the user preferences in the CMC and database too. For me this error sounds like different time zone in database vs user.
But it’s only an idea

Juan_Lu :es: (BOB member since 2005-05-11)

3

Which specific settings are you referring to? The time zone in the CMC admin preferences (Under CMC Preferences when logged in as admin) is set to DEFAULT, as are our users. I’ve verified that nothing there has changed.

SAP and I both verified that the client machines and the server were both set for the same timezone as well.

What’s really odd is it keeps adding time to the prompt, it’s not just 5 hours off. The first time it’s 5, then if you hit refresh it’s 10, then 15, etc… really strange. SAP is looking into it now but I have a feeling I’m going to end up having to attempt to revert our system back to R4.0.

If anyone else is on 4.1 base or 4.1 Patch 1 and could chime in as to whether they see this issue or not on their system, it’d help me determine how far back I’d have to roll, or even if it’s something in my installation specifically and not a general SAP Bug.

JPetlev (BOB member since 2006-11-01)

4

Here’s the latest:

  1. SAP is still working on the issue, and hasn’t really gotten back to me other than to say they need more time.
  2. The control panel ‘uninstall’ method worked just fine for patch one. This is the first time I’ve been able to uninstall something via the control panel and have it go right.

A few items such as custom logos and whatnot will need to be redeployed, but it didn’t break single sign on or anything else that I can notice.

JPetlev (BOB member since 2006-11-01)

5

New Update:
Per SAP:

Hopefully This will see daylight this year.
Link to SAP Note (May need to log in to view)

JPetlev (BOB member since 2006-11-01)

6

I have a similar issue that should be fixed since version 4.0 Support Package 2 Patch 18, known as ADAPT01590478 :
In Web Interactive viewers, calendar for the Date object in a prompt selects the previous day instead of the date chosen.

I reproduced it in 4.1 SP1 and 4.1 Sp3.

I may have to come back to 4.0 (SP6) as you preconized.

SebTronchet (BOB member since 2005-01-10)

7

Note:
They’ve moved the fix to a different patch,
per SAP:

Looks like I’ll be stuck on 4.1 base for quite a while at this rate.

JPetlev (BOB member since 2006-11-01)

8

I’ve just finished installing 4.1 SP02 and came across this problem today. I thought I was up to date on patches, but I’m hoping I just missed one. Has this problem been resolved for you?

nkmoore (BOB member since 2012-03-07)

9

According to the kb article linked-to above, the fix is included in:

BI 4.0 SP06 Patch11
BI 4.0 SP07 Patch7
BI 4.0 SP09 Patch1
BI 4.0 SP10

BI 4.1 SP01 Patch05
BI 4.1 SP02 Patch01
BI 4.1 SP03
BI 4.1 SP03 Patch1
BI 4.1 SP04

joepeters :us: (BOB member since 2002-08-29)

10

We had the same issue and we are on 4.1 SP1 Patch 3.
For a Work-around, have the user select Applet in the user preferences for both View and Modify under Web Intelligence. While we see 12:00:00 when you select the Date, under the covers, the HTML popup Calendar delivers a different time under the covers to the SQL with a UMT time difference which for us was 5 or 6 hours. The SAP Note 1930605 is related and suggests this

pcgeekus :us: (BOB member since 2003-10-16)

11

We are seeing a similar behaviour in BI 4.1 SP2.

This occurs when:-

:!: Entering a date into either a universe prompt or a prompt created in the HTML panel and running using the HTML panel, Java works fine.
:!: Scheduling and entering the date into a prompt.

Date entered into prompt:-

31/12/2013 00:00:00

Appears in schedule, once applied, as:-

30/12/2013 23:00:00

The amount of time offset can also vary, as the OPs, above.

:arrow_forward: Interestingly enough this seems to affect one date column in the universe, other date columns run fine!!!
:arrow_forward: They are defined the same in trhe universe.
:arrow_forward: The prompts are identical.
:arrow_forward: The objects have no formatting applied at the object property level.
:arrow_forward: Database definition is the same.
:arrow_forward: BI Launchpad Preferences are fine.
:arrow_forward: Works on Dev server based in UK, UAT is based in Switzerland.
:arrow_forward: App server is set for UTC time.

I’m guessing this is something to do with the application server. We use a JAP (Java Application Server) rather than Tomcat.

Mak 1 :uk: (BOB member since 2005-01-06)

12

Sadly, I’ve not been able to update my system for quite some time, so I cannot see if the old behavior reverted or something, but at first glance, your issue appears to simply be off by 1 hour? (or 23 hours depending on how you look at it)…

Does it keep moving 1 hour each time you refresh without changing the prompt?

If so, that’s exactly what was happening before… if not and it’s just off 1 hour from the 00:00:00 timeframe, then I’d suspect it has something to do with Daylight savings time setting… almost all 1 hour differences I’ve seen are due to either a bug or a setting with DST somewhere.

The US is currently in daylight savings time as of March 2nd. If that’s when your bug started, then it’s a DST issue somewhere.

I believe UK still uses DST as well but if not it still could be a setting applying it incorrectly perhaps.

If you can test in a full dev environment, try changing the date of the servers themselves to a point when DST is not active and see if the bug still occurs.

JPetlev (BOB member since 2006-11-01)

13

This offset seems to vary between different users, with the same settings, same location etc. 1 hour, 2 hours.
We had thought about DST, but I don’t believe this is the issue in this instance.

Edit:-

Seems like this was fixed in Patch 01 for BI 4.1 SP02. I will update this if we patch up to SP3, which should include Patch 01, and it works.

Mak 1 :uk: (BOB member since 2005-01-06)

14

We recently upgraded to 4.1 SP2 and had this same issue and SAP confirmed that there is a fix for this in SP3. We got SP3 on a sandbox but its not resolved. Waiting for SAP to get back.

azmathmirza (BOB member since 2014-04-30)

15

Hi all,

What I did was change the preference of CMC and selected Default-Local to Webserver in the time zone (CMC Preference).

It could solve my problem and created another one.

If I pass by link two dates (between - 03/20/2014 and 03/21/2014), I have only 03/20/2014 but I have data for bouth dates.

I have created a variable to do swhow my selection and it shows bouth dates (Initial: 03/20/2014 and Final: 03/21/2014).

Does anybody has any clue about it?

Tahnk you very much!!

defaro :brazil: (BOB member since 2006-09-01)

16

I’d need to see the Actual SQL your report generates to be sure… but my guess is it’s due to your timestamps on the dates…

SQL handles timestamps differently depending on whether you enter a time or not…

Example:
3/20/2014 thru 3/21/2014 without any timestamp (or 00:00:00) = 48 hours of time.
3/20/2014 12:00:00 thru 3/21/2014 12:00:00 (with timestamp) = 24 hours of time…

There used to be a bug in older versions of BOBJ where SQL would show timestamps but then ignore them depending on the control you were using… this was fixed in a patch long ago, but it sounds like your bug could be similar.

See This Link for more information

JPetlev (BOB member since 2006-11-01)

17

Guys,
I was wrong! Still the same problem. I pass date plus 5h and it works.

Is there any solution ? Upgrade?

Thank you!

defaro :brazil: (BOB member since 2006-09-01)

18

Hi JPetLev,

Thank you for your answer.

Actually my problem is a little bit diferent becasuse I send by link two dates (between operator) and the problem is that I have 5h minus.

I mean, if I send in the link 02/20/2014 12:00:00 AM and 02/25/2014 12:00:00 AM, the result is 02/25/2014 12:00:00 AM and 02/24/2014 07:00:00 PM.

It is the same problem that you had in the begining of this thread.

How could you solve your problem? Can you halep, please?

Thank you very much!!
Gustavo

defaro :brazil: (BOB member since 2006-09-01)

19

I haven’t solved my problem yet… Still waiting on a server from my network guys so that I can properly test patches.

After the last time patching on our LIVE server and the issues it caused, I gave them an ultimatum… we stay on 4.1 SP1 forever, or they get me a development server to properly test patches… Still waiting on the patch server :slight_smile:

Ohh and according to them, “I’ll have it by the end of the week…”. I stopped holding my breath a few months ago.

Therefore still on 4.1 SP1 for now.

JPetlev (BOB member since 2006-11-01)

20

our prompt issues, although a little different, were solved by the application of 4.1 SP3.

Mak 1 :uk: (BOB member since 2005-01-06)