String Function in Set Analysis

[Anonymous]

My NT ID comes in the form of NTLOCAL\john.smith

I need to change time based on the logged in user. Each user's GMT Offset is stored in the database. When I hard code the value, I get the correct value:

=sum({<[DB User Name] = {'John Smith'}>} [GMT Hour Offset])

The below string also gives the correct value:

=trim(capitalize(replace(mid(OSUser(),9),'.',' '))) gives a value : John Smith.

However, the below set analysis while correct in syntax, does not give the correct value:

=sum({<[DB User Name] = {$(=trim(capitalize(replace(mid(OSUser(),12),'.',' '))) )}>} [GMT Hour Offset])

Where are I going wrong? Any better suggestions?

Thanks,

Dinesh.

[Miguel_Angel_Baeyens · Accepted solution]

Hi Dinesh,

Check that the result of the function is quoted as a literal:

=sum({<[DB User Name] = {'$(=trim(capitalize(replace(mid(OSUser(),12),'.',' '))) )'}>} [GMT Hour Offset])

EDIT: Ok, there may be some quotes missing. What about creating a new table (say inline) where you store in a field the offset value for each user? Then, your expression will look like

=sum([Time Field] + [GMT Hour Offset])

I'd give it a shot, since once the user has logged, the value is not going to change.

Hope that helps

[Anonymous]

That was it. For some reason, the editor shows a syntax error when the quotes are included, but it gives the correct results.

Dinesh.

[Anonymous]

When I use your formula =sum([Time Field] + [GMT Hour Offset]), I get 40499.416666667. How do I format it to the right date time format?

Thanks,

Dinesh.

[Miguel_Angel_Baeyens]

Hi Dinesh,

Use the following:

=Date('40499,416666667', 'DD/MM/YYYY hh:mm:ss')

It will return "17/11/2010 10:00:00"

Hope that helps