I’m new to Business Objects & Enterprise, although an old timer with regards to Crystal Reports.
I’m having an issue in BOXIR2 with regards to the display format of some Deskii Reports. The date format within some of the reports displays mm/dd/yyyy but it needs to show dd/mm/yyyy.
I realise i can modify the date format of each cell to the one I require. However as there are a number of reports this would take come time. Is there a global setting i can change to display the correct date? I’ve tried playing with server setting, local machine settings and internet settings without much luck.
However since the post i’ve found a couple of posts on BO KB stating that any issues with American date format can be modified in the ControlPanel/International registry setting for the relevant user.
I decided to modify this after noticing some strange results following the change of date format within Deski. I changed one report to the correct format… and it worked fine.
I then scheduled another report which hadn’t been modified, and now this works! To say i’m confused would be a slight understatement.
I’ve just scheduled a report on a seperate server with just the registry modifcation… so i’ll let you know the results.
There are a couple of other things that you can do.
In Deski, go to Tools/Options. On the General tab, there is a Language dialog box and next to that is a button. This button should be the language of your location. In my case English(Australian) which is one of the unsupported languages.
Do the same thing in Designer.
In Infoview, within the parameter window, change the locale to your country of choice or set it to ‘Use browser locale’
Lastly and this needs to be done on the server as well as your local PC is to modify the PRM files for you database language to ensure they also have the correct format. If these have not been changed since deploying XI, then they are set to American date format as default.
All of this will solve your date format issues and you won’t need to change the date format on the individual reports.
You can also look at some of the other information on BOB look for PRM files, or American date formats and you should get some more clarification.
I had a similar problem (after plessiusa’s suggestions were applied).
If this report is prompt driven, the data type for prompt had to be changed. Instead of @Prompt(‘message’, ‘D’, , mono, free), I used @Prompt(‘message’, ‘A’, , mono, free) and it was handing the dd/mm/yyyy format correctly.
You are running this as a webi report on the server, did you change the PRM file on the server. My guess is that this is what your problem is.
For a quick test, create a date field as a prompt and have a look at the format that it is expecting, if this is in US format then your PRM on the server needs to change.
I have made the change in the sqlsrv.prm file under odbc and olddb directory of C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer
both on the server and the client.I am using SQL Server 2000.
After i modified the registry on the server, and ensured the correct language settings i rescheduled a report. Guess what? yup, the same problem reared it’s ugly head.
I too have looked at the .prm files on the server and at present it displays the date in American format. However after looking at namitrs post i’m not convinced that this will cure the problem. I’m attempting to replicate the issue on a clean test machine to try and pinpoint the exact problem. There just seems to be too many variables to this issue, from user browser settings to server registry, and i feel like I’m going round in circles.
Here’s a checklist I’ve compiled that sorts out the problem in most cases
XiR2 Date format checklist
Solution Details ## Environment ##
Server: Windows
BO: XiR2 Server and Desktop Products
DB: n/a
Client: n/a
Web/App server: n/a
Symptoms
A. Date prompts in infoview and Deski are displayed in American format.
B. When running a query a datetime error is generated:
Example with SQL server
“[Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]”
converting a Char data type to a DateTime data type
Resolution
CHECKLIST
Ensure that the universe is using an ODBC connection
Ensure stored procedures are using an ODBC connection
Set the ODBC connection to use British/English as the default language
Migration forced UK English has been applied in the designer module:
Tools>Options>Tick ‘migration forced’
Click the grey ‘English’ option > Unsupported Languages > Select “English
(United Kingdom)”
Force migration in Desktop Intelligence
Regional settings on the server and client machines are set to UK English
Object used in the prompt
In Designer edit the properties of the date object > Advanced Tab > Ensure
that the Database Format is left blank.
PRM file setting: User_Input_Date_Format should be set to dd/MM/yy or
dd/MM/yyyy
(The timestamp applied is not known to have an impact on the problem)
SQL Server example:
C:\Program Files\Business Objects\BusinessObjects Enterprise
11.5\win32_x86\ dataAccess\connectionServer\odbc\sqlsrv.prm
‘mm/dd/yyyy HH:m:s’
Change to
‘dd/MM/yyyy HH:m:s’
If all of the above fails - delete the user prefs folder in the registry for
the BO user account experiencing the problem
HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\default\BusinessObjects\ “username” user prefs
Log back into Business Objects and rerun the report.
Applying all of the above changes will in most cases resolve the problem when the database holds values in an American format and it is possible to use an ODBC connection.
If the problem persists an additional step would be to modify settings directly on the server that hosts the database.
!! This should only be performed with the permission of the Database Administrator !!
Change following keys in HKUSERS.default\control panel\International\iDate
change to match HKUSERS\S-1-5-21-85988526-949360888-199955091-500\control panel\International\iDate
Key US UK
iCountry 1 44
iDate 0 1
iFirstDayOfWeek 6 0
iMeasure 1 0
iNegCurr 0 1
iTime 0 1
iTLZero 0 1
Locale 00000409 00000809
sCountry United States United Kingdom
sCurrency $ £
sLanguage ENU ENG
sLongDate dddd, MMMM dd, yyyy dd MMMM yyyy
sShortDate M/d/yyyy dd/MM/yyyy
sTimeFormat h:mm:ss tt HH:mm:ss
MORE
When using ODBC connections the date format will be determined on the Business Objects server – I always advise using ODBC
OLEDB connections will take the date format from the machine hosting the Database
Hmmm it should be working after all that.
Additional steps to take.
1.Check the list of values for one of the date objects in the universe.
Are they definately returned in UK format?
either way, delete all local copies of the universe and reimport it from
the CMS
Check that your regional/language settings on the client machine are
UK/English
I also found on one client site that despite all this they had the problem
Oddly it was solved by creating a new system DSN on the client
machine and defining a new connection for the universe to use.
I have also seen problems with AD authentication, not got to the bottom of this one yet
Can I suggest that you log a case with BO tech support in Sydney. (If you can’t, get someone who can). Your organisation will have a maintenance contract with BO, you pay these guys a lot of money every year, why don’t you use there knowledge and update this post with the result.
The information that I gave you earlier in the thread was what they did for me and it worked perfectly. They can Webex into your machine and see everything that you are doing, making it extremely easy to find the fault.
I have already spoken to BO about this and according to them they are looking at it. But, as you would expect, our BOB forum provides more info then the BO guys do…thanks to all you guys.
Keep onto BO, ring them everyday if you have to and ask for updates. If you don’t get any response escalate it through the appropriate channels.
I think however I may have put you a little crook in my earlier post. I have just gone and done a check on my PRM file for SQL Server on both my machine and the server, and this is what I see.
Steps I’ve found that will get XI R2 deski working happily in UK date formats on SQL 2000 :
Make sure default language for SQL login is set to British English
Make sure Deski option is set to English (United Kingdom), in unsupported languages.
Change USER_INPUT_DATE_FORMAT parameter in sqlsrv.prm file to dd/mm/yyy
Make sure WAS servers have correct regional setting.
I guess that shows the difference in the way that BO can be setup.
For starters. I did change my MS SQL PRM to be in dd/mm/yyyy format, this worked for a couple of reports, but not for the bulk, especially if you have third party applications. By leaving the date in US format and changing the locale to English Australia, I was able to solve my problem.
I also did not need to reboot the server when changing the PRM, simply close and reopen Deski will automatically pickup the latest PRM.
(BOB member since 2007-05-18)
(BOB member since 2006-06-27)
(BOB member since 2006-11-28)
(BOB member since 2004-03-22)
(BOB member since 2006-11-28)
(BOB member since 2006-06-08)
(BOB member since 2004-03-22)
(BOB member since 2006-11-28)
(BOB member since 2007-05-18)