Data Report who shows activity last 7 days pr user

Hi,

I and my company are in the business of selling our time as a service and need to bill our work and since we use RDMS the hole day for our connection we have a pretty good way to track the time spend per customer through the logging options.

So I created a Data Report entry who prompts the user for account used and returns that in a query and the outcome will be a report showing all activity with CustomerGroup (the folder) Connection (session) CloseComment (we have it as default to prompt for comment on each session close), StartTime, EndTime and duration shown in hh:mm:ss (calculated field).

This is the Query I use:
SELECT [GroupName] as CustomerGroup ,[ConnectionName] as Connection ,[ClosePrompt] as CloseComment ,[StartDateTime] as StartTime ,[EndDateTime] as EndTime ,convert(varchar(5),DateDiff(s, StartDateTimeUTC, EndDateTimeUTC)/3600)+':'+convert(varchar(5),DateDiff(s, StartDateTimeUTC, EndDateTimeUTC)%3600/60)+':'+convert(varchar(5),(DateDiff(s, StartDateTimeUTC, EndDateTimeUTC)%60)) as Duration FROM [RDMS].[dbo].[ConnectionLog] Where [LoggedUsername] = @Param1 and [StartDateTimeUTC] > GETDATE()-7 and [Message] = 'Open session' ORDER BY [GroupName] ASC On the parameters tap I define Parameter #1 as Text whit domain\ as a default value so the user will only need to add there user name (I tried to use Variable but it did not work for me).

Ofcourse you need to setup your Data Report connection to your SQL server and edit in my script the FROM sentence to fetch from right table and database.

Hope this comes to use for someone out there.