on 11-19-2014 6:04 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
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
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
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
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
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
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
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.