Hi Catherine_Tee , you would need to move your logic to a Dashboard DataSet, but you could do something like this example. Here we retrieve the columns you want with a modified SQL query e.g. LogonTime does not exist in SecUser table so you need a Join and also Null handling on the LogonTime column. Then you can get the Local LogonTime using the TimeZoneInfo object and then convert the UTC time (LogonTime) to Local Time (LocalLogonTime)
Hope this helps
' Declare stringbuilder
Dim sb As New Text.StringBuilder
' Declare SQL (you need to create a Join with UserLogonActivity to retrieve LogonTime) and add placeholder column LocalLogonTime
Dim sql As String = "SELECT s.[Name],s.[Description],s.[Email],ISNULL(u.[LogonTime], '1900/01/01 00:00:00') AS [UtcLogonTime],'Unknown' AS [LocalLogonTime]
FROM [SecUser] s
LEFT JOIN [UserLogonActivity] u
ON s.Name = u.[UserName]
ORDER BY s.[Name]"
' Declare placeholder
Dim dt As New DataTable()
'Execute Query on FrameWork DB
Using dbConnFW As DBConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
' Populate DataTable
dt = BRAPi.Database.ExecuteSql(dbConnFW, sql.ToString, True)
End Using
' prepare placeholder column
dt.Columns("LocalLogonTime").ReadOnly = False
dt.Columns("LocalLogonTime").MaxLength = -1
' Get the local time zone of the user
Dim localTimeZone As TimeZoneInfo = TimeZoneInfo.Local
' Loop DataTable Rows
For Each dr As DataRow In dt.Rows()
'Process rows
dr.BeginEdit
' Convert UTC DateTime to Local DateTime
dr("LocalLogonTime") = TimeZoneInfo.ConvertTimeFromUtc(dr("UtcLogonTime"), localTimeZone)
dr.EndEdit
Next
' Loop DataTable Rows âž¡ Logging
For Each row As DataRow In dt.Rows()
For Each col As DataColumn In dt.Columns()
sb.AppendLine(String.Format("Col âž¡ {0}, Row âž¡ {1}", col.ColumnName, row(col)))
Next col
Next row
' Log Result âž¡ Throw Error
Throw New XFException(Convert.ToString(sb))