User-specific date format

[Anonymous]

Forum,

Here is a problem. Client has users in multiple countries who are using the same QVS-based application. Date format is different from country to country, for example DD/MM/YY in Europe, and MM/DD/YY in US. They want to be able to see the data in their respective format.

Is there a reliable way to implement this? Appreciate any ideas, especially the crazy once - they help to think.

(I've implemented a clumsy solution based on time functions that worked OK in QVS 8.01, but behaves rather erratic in QVS 8.50. I reported a bug to QlikTech.)

[disqr_rm · Accepted solution]

How about having a variable at runtime (Settings -> Variable Overview), say vLang, and setting it to:

=getregistrystring('HKEY_CURRENT_USER\Control Panel\International', 'sShortDate')

This would give you the date format of the user in the vLang variable. Then use this variable in charts and everywhere to format the date.

[Anonymous]

Rakesh,
Thank you for the idea. On the local machine it is more elegant than my current solution. The problem is that it doesn't work for server-based apps. User gets registry info from the server, no matter what is on the user machine. The is, if the serever date format is MM/DD/YY, and user setting is DD/MM/YY, yser gets MM/DD/YY.
(I tested with QVS 8.50 and 8.01)
But your idea may help to find a working solution. I'm going to try around it.

[Anonymous]

There is a solution. The important part is the Date Format variable must be assigned on open, that means the expression cannot be used in the variable definition. I'm going to use this macro:

sub OnOpen
 ...
 DateFormat
 ActiveDocument.GetApplication.WaitForIdle
 ...
end sub
private sub DateFormat
 df = ActiveDocument.Evaluate("=upper(getregistrystring('HKEY_CURRENT_USER\Control Panel\International', 'sShortDate'))")
 ActiveDocument.Variables("vDF").SetContent df, true
end sub

Thanks a lot Rakesh!

[disqr_rm]

Michael, That's pretty smart.

Somehow, I have feeling that the variable solution also should work on Server & IE combination of application. that's is why I was checking the user specific keys and not the system default one. But to be honest, I have never tried it myself. Anyway, we know hoe to handle it now.

Thanks for posting the final solution.

[Anonymous]

Rakesh,
Sorry to dissapoint, but it didn't work anyway. I apparently missed something in my testing, likely was opening a local copy of the file instead of server-based.
It consistenly reads info from the server registry. Even if I run the above "DateFormat" macro using a button on the user machine, it gets data from the server.
To be continued, I think...

[disqr_rm]

Try this in Macro (OnOpen?):

set v = ActiveDocument.Variables("vDateFormat")
v.SetContent ActiveDocument.GetProperties.LocaleInfo.DateFmt, true

This would give you user date format in the vDateFormat variable. Don't forget to create that variable first with empty value.

This is interesting. Keep me updated.

[Anonymous]

Thanks Rakesh,
I tried this now (and also "GetLocaleInfo"), and the result is nor from local user neither from server, but rather from the application format settings
SET DataFormat =...
This is defined on reload. If there is no DataFormat variable, it will be taken from the system where the reload runs

[disqr_rm]

Damn! This should be easy.

The last thing I can think is to create a function (say, GetMeUserLocale) which uses VBScript function GetLocale() and returns this as string value.

Then, have a table (load Excel, or something) in QV with LocaleID and DateFormat which will be filtered (or searched) based on the return value of GetMeUserLocale.

I have dealt with this situation in a real environment, but did not solve it by getting system locale, as my user could be a US user sitting in Japan still wants to see dates in US format. What I ended up doing is to have a centralized user preference management ASP application, where you can set your prferences (decimals, colors, totals, dates, etc.) for all QV Applications and this ASP app stores this information in an SQL Table. Ofcourse the key is QV Application ID assigned uniquely to each productive QV application. Then, this SQL table is loaded in every QV document (WHERE AppID = QV_App_Name) and then section access reduces this UserPref table to the accessing user only (AD User ID). This worked just fine for me in the past.

[Anonymous]

Rakesh,
Believe it or not, we got it!
It turned out, that VBscript functions get info from the user box, unlike the QV functions that read the server. So it's basically the same as above, but I'm using VBscript to get the format from the regisrty. Well, a few lines longer:

sub OnOpen
 ...
 DateFormat
 ActiveDocument.GetApplication.WaitForIdle
 ...
end sub
sub DateFormat
 const HKEY_CURRENT_USER = &H80000001
 strComputer = "."
 Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
 strComputer & "\root\default:StdRegProv")
 strKeyPath = "Control Panel\International"
 strValueName = "sShortDate"
 oReg.GetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue
 df = UCase(strValue)
 ActiveDocument.Variables("vDF").SetContent df, true
end sub

I hope I didn't miss anyhing this time. Your hints really helped, thanks.