Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 304 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Date formatting in Reports in NAV 2013 R2
engsiong75
Member Posts: 143
Hi everyone
One of my biggest difficulty in project is that I need to format the date in the reports to suit the customer. The users will request that as they are following dd/mm/yyyy, all the report need to show the date in dd/mm/yyyy. This is a real pain especially if the reports are formatted to mm/dd/yyyy. Please don't suggest coding because I have users in the same database that need dd/mm/yyyy in one company and mm/dd/yyyy in another company.
I noticed that in NAV 2013 R2 report builder, there is a neat feature for date format to follow the Current Regional Setting. This is great which means the report can be used for user that use dd/mm/yyyy and mm/dd/yyyy. All they need to do is just change the regional setting.
After some testing, I found that the statement is a little misleading. It means that if you want mm/dd/yyyy, you have to select English (United States). If you are using English (United Kingdom), you can never get the report to print mm/dd/yyyy no matter how you change the date format. So what the control actually means is that it follows the default date setting of language chosen. This means that to change the date formatting, one has to change the language.
This is a minor issue for people using English. This is a major issue if the user is using other language like Chinese Simplified and need the date format mm/dd/yyyy. Then the user will need to change the language (English (United States)) used whenever he or she is printing the report and change it back (Chinese Simplified (PRC)) to input the Chinese text in NAV. #-o
One of my biggest difficulty in project is that I need to format the date in the reports to suit the customer. The users will request that as they are following dd/mm/yyyy, all the report need to show the date in dd/mm/yyyy. This is a real pain especially if the reports are formatted to mm/dd/yyyy. Please don't suggest coding because I have users in the same database that need dd/mm/yyyy in one company and mm/dd/yyyy in another company.
I noticed that in NAV 2013 R2 report builder, there is a neat feature for date format to follow the Current Regional Setting. This is great which means the report can be used for user that use dd/mm/yyyy and mm/dd/yyyy. All they need to do is just change the regional setting.
After some testing, I found that the statement is a little misleading. It means that if you want mm/dd/yyyy, you have to select English (United States). If you are using English (United Kingdom), you can never get the report to print mm/dd/yyyy no matter how you change the date format. So what the control actually means is that it follows the default date setting of language chosen. This means that to change the date formatting, one has to change the language.
This is a minor issue for people using English. This is a major issue if the user is using other language like Chinese Simplified and need the date format mm/dd/yyyy. Then the user will need to change the language (English (United States)) used whenever he or she is printing the report and change it back (Chinese Simplified (PRC)) to input the Chinese text in NAV. #-o
0
Comments
In addition, you could use the NAV built-in FORMAT function to format date controls to display e.g. the year with 4 digits, display the month as full name and so on, but this will always refer to the basic format setings defined by the Windows Control Panel and simply extend these.
Hope this helps
Andrew
>>For all data formatting like decimals, date and time format etc. the NAV client simply follows the user profile settings defined on Control Panel / Region and Language --> Format. This is used on NAV pages displayed on the screen
Reply: I agree.
>>as well as on reports.
Reply: I have bad news for you on that theory for Report in NAV 2013 R2. The date logic works for filters and the other fields in the report page but it will then follow the format of the language of the Regional Setting. Please try it and see. Create a date field in the Layout and then ensure that the formatting is default. When you run the regional setting as English (United Kingdom), the date will be dd/mm/yyyy. You can change the date to mm/dd/yyyy. It will not change the date format in the report.
>>In addition, you could use the NAV built-in FORMAT function to format date controls to display e.g. the year with 4 digits, display the month as full name and so on, but this will always refer to the basic format setings defined by the Windows Control Panel and simply extend these.
Reply: That is going to be a little time consuming to do the date formatting for every report and and every date field.
Reply:
Yes agreed - that's exactly what I was saying. The date format always follows the regional settings of the users OS profile.
I assume what you are trying to achieve is to statically set the date format on the report to a fixed pre-defined format by the report - which you can't. The reason is to provide the capability of receiving documents individually formatted matching the local formats for multiple users working in different areas but using the same report objects. Each of them will get its document properly formatted with his/her local data format.
On the other hand, this behaviour will cause issues on global reporting, e.g. when each area will create local reports and forward it to a central headquarter. They may expect to receive the reports the same data format from each local region. In this case it would be necessary to let the users have their format settings temporarily switched to whatever is requested.
>>...This is a major issue if the user is using other language like Chinese Simplified and need the date format mm/dd/yyyy. Then the user will need to change the language (English (United States)) used whenever he or she is printing the report and change it.
Reply:
They wouldn't have to change the complete language (Region). Simply adjusting the date format in the Regional Settings (under the Format tab) would do it. This way they would keep their language but get the dates formatted as required. Only a minor issue too then I guess for those folks not using english language
Cheers
Andrew
When the customer has the Windows server OS in English, you need to setup a domain user with local regional settings. This user must run the service tier.
We have an Issues in NAV 2013 R2 Build 37563 (Hotfix Roll-up 11), where specifically Decimal (Currency) Amounts, display as per Client Regional Settings in Pages, but in NAV Reports, they display completely differently. Is this an introduced bug, and will it be corrected soon?
Example: Posted Sales Invoice Page will show (Say) 1,234,567.89 as per Regional Settings, while Report will display this as 1 234 567,89
Having written this I think I see a pattern:
1. Our Regional Settings are English (South Africa), which is how the Report is Printing
2. ..but we have used "Advanced" to alter that to what we actually use - this is how the Page is Displaying
Any Comments?
I have looked into your issue. If you use English(South Africa), your currency decimal symbol will be "," and the grouping symbol will be a " " by default. This is what the Navision reporting feature will take as your regional setting. If you want to use the currency decimal symbol as "." and the grouping symbol as "," you have to change it to English(United Kingdom).
The alternative is to change the code in the report.
Thanks