April 3, 2024 at 11:13 am
We upgraded a SQL2014 environment to a sql2016 one recently with a customer. This customer still uses the traditional SSAS Multidimensional cubes.
After the upgrade the dates no longer present themselves in the singular same format within Excel reports.
A value of 14 march 2024 is represented as '2024-03-14' correctly , but 4 march 2024 is represented as '2024-04-03'.
When browsing the date dimension all member values come up correctly as YYYY-MM-DD
Adding a format to the attribute in the date dimension as YYYY-MM-DD does not rectify the problem.
It looks like it reverts to american style notation whenever it can.
April 3, 2024 at 4:42 pm
I am 99.99999% sure that the problem is NOT in the report, but with Excel.
IF Excel sees the value as a date, it will do its best to parse it out in a format based on the client machine's regional settings. So I suspect what is happening is that excel is seeing the dates and is confused about them and doing a "best guess" on them.
One way to confirm this theory - try adding the numeric value of 1 to the date. If you get the next day (such as March 4th becomes March 5th), then Excel sees it as a date. If you get an error (#VALUE!), then Excel is seeing it as a string and has no idea how to handle it so it is parsing it as a string and a string value plus numeric 1 is an error.
What I suspect is happening is that the data for March 14th is coming as "2024-03-14". The regional settings on the machine are set to "yyyy-dd-mm" and the column is configured to display the data as "yyyy-mm-dd" due to configuration in Excel. The data coming to Excel is in string format and Excel has no idea how to convert 2024-03-14 to a date as the 14th month doesn't make sense, so the data is presented as it came in. 2024-03-04 is easy to convert to yyyy-dd-mm format and then present in yyyy-mm-dd format so you get 2024-03-03.
The fix is that your dates need to be presented in the users regional setting format OR the users need to adjust their regional settings to match the report output. OR you need to change those columns to not be presented as "dates".... or an easy (ish) fix - add an apostrophe to the start of the dates so excel sees it as a string every time. What I mean is that 2024-03-14 becomes '2024-03-14 and 2024-03-04 becomes '2024-03-04. That way Excel will see the date value as a string each and every time. Screws up date formatting and date sorting in the excel side, but it'll fix Excel mucking up the date formats.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 3, 2024 at 6:23 pm
Thank you for this extensive reply. I think it does make a lot of sense.
Next friday I'm going to test through VPN to the clients server.
The really weird thing is that absolutely nothing has changed except the upgrade to SQL2016 from SQL2014 (The legacy ERP system can only work with SQL2016 , no higher version)
But your explanation that it leaves the format yyyy-mm-dd 'as-is' because it cannot be converted to the yyyy-dd-mm format with day numbers higher than 12 makes perfect sense and would explain the situation totally
April 3, 2024 at 10:10 pm
My guess about it "worked previously but doesn't now" is that it is a combination of things. First, changes to the client machines (office updates, OS updates, GPO's being applied to force specific regions). Next, users not doing proper data validation. What I mean is that the data always "looked good" with a quick glance, but when you do a full system change and ask users to test, they tend to look at it more closely. Heck, I'm a technical guy and I tend to look more closely at things when I am making changes and have found bugs in systems that have been there for years with nobody noticing until I point it out while making an unrelated change.
Had one recently where a report was incorrectly summing some numbers up making the total be way off. Noticed it while fixing an unrelated bug and fixed both at once thinking I was being efficient. The primary user got upset telling me that the report was "working perfectly before minus that one bug I asked you to fix", and it took a meeting with me, the end user, and their manager where I showed them that it was doing A+A+B in some cases instead of A+B and they still told me I was wrong. Their boss agreed with me though and both changes went live and afterwards they realized the report was more accurate and were thankful.
But if you aren't "testing" a thing, it is sometimes easy to miss stuff that you THINK was working fine all the time when it really wasn't.
BUT I also think that OS and Office updates can cause new behavior in applications, so it could be it worked properly before but due to an Excel update, Excel is more fussy about what is a date. Especially after all the jokes about how bad excel is at dates...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 5, 2024 at 12:30 pm
Customer rebuild the .dsv and query expression within the model. Redeployed and the problem appeared to be solved.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply