cancel
Showing results for 
Search instead for 
Did you mean: 

Post FixPack 4.4 install on BI 4.1 SP4 Wrong prompt values going in SQL query

Former Member
0 Kudos

Dear Experts need your guidance to resolve this.
 
We had initially 4.1 SP3, things were ok barring slow performance and some minor issues. We updated to SP4, problems started with AnswerExPrompts, split of merged prompts, crashing of APS server while refreshing WEBI reports based on multisource UNX. we were told these issues are fixed in FP4.4 and we applied it. Now we regret applying SP04 and FP4.4. It created following problems;
 
Environment : Windows 2012 Server with BI 4.1 SP04 FP4.4. datasource SQL Server 2012. ODBC DSN based on SQL Server
 
In single source universe;
(A) all date objects not parsing. When investigated we found database has date fields defined as datetime whereas in datafoundation in the structure it is showing as Timestamp. Since Timestamp gives binary values it is not parsing.
 
Change of date field structure in DF will be a big and risky task as there are quite a few date fields as well as if we add any additional table with dates we will have to do the same for it.
 
(B) When reports with date prompts based on the another UNX (which we did not check integrity or do anything post update) refreshed takes date correctly as dd/mm/yyyy from prompt calendar. but gives wrong result. Example if I take 01/01/2014 (Jan) to 10/01/2014 (Jan) for 10 days it gives result for 01/01/2014 to 01/10/2014 (9 months 1 day).
 
When we checked the SQL script within report post refresh it shows date being compared with mm/dd/yyyy format. Strange!!!
 
We have not defined any format for date objects in the universe. Server/Client Regional setting is English UK, PVL in CMC is set to English UK and -Duser.timezone=UTC set in Apache Tomcat Java Configuration. But with no success so far.
 

What is surprising that all these reports were validated in SP3, now all have gone for toss.


Please help as this is frustrating us and there is no option of reverting back to SP3 due to security permissions and involvement of multiple departments in carrying out the exercise.
 
Our UAT is delayed.


Thanks in Advance

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Guys,

Issue resolved. Hope this will help anyone else facing same issue.

What we discovered with SAP support team after 2 days web-ex that UNX is accessing .prm file from \\Install folder\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\oledb\extensions\legacy  folder where this .prm file was missing. We copied the sqlsrv.prm from another location and changed USER_INPUT_DATE_FORMAT and Prompt related issue got resolved.

Thanks

Piyush

Former Member
0 Kudos

Hi Piyush,

we are migrating to BI4.1 SP5 and facing the prompt timestamp issue. I need your help that how/what to and change the USER_INPUT_DATE_FORMAT. In that sqlsrv.prm file, i don't see USER_INPUT_DATE_FORMAT anywhere in the document. may be i am missing something... please help? please treat this as urgent.

Thank you!

Former Member
0 Kudos

Dear Santhosh,

We copied it from Installed Drive:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc\extensions\qt folder.

You also need to make changes in cs.cfg file in folder Installed Drive :\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer . In this EnableJNI parameter by default is "Yes", change it to "No" as below;

<Capabilities>

        <Capability Name="Local" EnableJNI="No" Active="Yes"/>

        <Capability Name="Remote" Active="Yes"/>

    </Capabilities>

Make sure when you are doing these changes SIA is down. After making changes restart the Server.

Hope this resolves your issue.

Regards

Piyush

Former Member
0 Kudos

Thanks Piyush for your prompt response. I will try it and let you know.

Former Member
0 Kudos

Piyush,

We tried with the following steps but still issue exists:

1) -Duser.timezone=UTC set in Apache Tomcat Java Configuration (Also tried without this step)

2) Copied sqlsrv.prm file

     from the location - Drive:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects                                    Enterprise XI 4.0\dataAccess\connectionServer\odbc\extensions\qt folder

     to the location - SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\oledb                               \extensions\legacy

3) made changes in cs.cfg file (<Capability Name="Local" EnableJNI="No" Active="Yes"/>)

4) restarted the server.

We did all above steps ... please let me know anything is missing?

Thanks,

Santhosh

Former Member
0 Kudos

Hi Santhosh,

Can you post your exact error and steps to reproduce the same? This will give more clarity to understand your issue and suggest accordingly.

Thanks

Piyush

Former Member
0 Kudos

Piyush,

This is related to publications using webintelligence report as dynamic recipients and facing date prompts issue.

We are using BI4.1 SP5 version.

1) We have created crystal report as a main report (Source Document) and using webi report as dynamic recipients to pass the values of 'Start Date' and 'End Date' in publications.

We are mapping Start Date and End Date from Webi Report to Start Date and End Date of Crystal Reports in personalization.

2) When we run the standalone Webi Report in Launchpad with 'Start date' = '01/01/2014' and 'End Date' =  '12/31/2014' and they are showing correctly.

3) But, when we schedule the publication with the values - 'Start date' = '01/01/2014' and 'End Date' ='12/31/2014',the values are going back 5 hours and it is showing Start Date = '12/31/2013 19:00:00' and End Date = 12/30/2014 19:00:00 in Dynamic Recipients.

4) After scheduling the publications, we are seeing the pdf output as '12/31/2013' and '12/30/2014', which is supposed to be as '01/01/2014' to '12/31/2014'

***** This issue is happening only when we use webi report as dynamic recipients but it is working when we use Crystal report as dynamic recipients *****

So far issue resolution, we tried the steps as you mentioned above but still not getting resolved. Please let me know if there is any other way to resolve.

Thanks,

Santhosh

Former Member
0 Kudos

Hi Piyush,

We copied the sqlsrv.prm file from qt folder and pasted it in legacy folder. Now, the date format shows up fine in Webi Rich Client on the server, but in BI Launchpad, the date format for user input still shows up as mm/dd/yyyy.

Any idea how the date format input settings for Launchpad can be changed. We suspect this could be a tomcat specific setting.

BO version - BI 4.1 SP01

Server - Windows Server 2008 R2 Enterprise 64-bit OS

Former Member
0 Kudos

Dear Satyajeet,

BI Launchpad is also impacted by Windows date setting. Please check that as well as settings in CMC for WEBI reports.

Please verify if these are same as the format you desire.

Generally this should solve your issue. However it is advisable to upgrade to SP03 or above as SP01 has quite a lot of issues.

Regards

Piyush

Former Member
0 Kudos

I have copied sqlsrv.prm file from qt to legacy folder.

Can you please confirm the date format which needs to be set for Database Date format and user input date format ?

I want to show dd/mm/yyyy hh:mm:ss

Please help

Below are my environment details :

SAP BI 4.1 SP1

OLEDB connection

MS SQL 2012 DB

Former Member
0 Kudos

Hi Satyajeet,

Did you get any solution to display dd/mm/yyyy for user prompt ?

Please share the resolution which you have applied.

I have same env details which u have mentioned.

Please help

TIA

Answers (0)