Billable Time/Time worked Portal Reports ?

David_Acevedo

Is there any way to get a portal dashboard like query? The end goal is to report per support group their time worked for example: Weekly report that shows week's time totals for each analyst on a specific support group. 

Any help will be more than appreciated.  

Adam_Dzyacky

Yes, you'll need to define a few variables to get this to run directly inside of SSMS and those are the @StartDate, @EndDate, and @analyst where @analyst is the AD Username/SamAccountName of the analyst you want to query against.

As an example, here's what needs to be at the beginning that you should be able to see how you can modify -

DECLARE @StartDate datetime, @EndDate datetime, @analyst nvarchar(4000)
SET @StartDate = '01-01-2017'; 
SET @EndDate = '05-19-2017';
SET @analyst = 'usernameHere'

If you drop the original query into Report Builder without the above block, it should dynamically just create the parameters so they can be defined when you run the report.

Adam_Dzyacky

Hey there @David_Acevedo - I haven't tested it for the portal dashboards so it may require a little rework, but I wrote a query for the SCSM DW that goes after Work Items and their Billable Time. Perhaps this can get you started in the right direction?

https://community.cireson.com/discussion/466/scsm-dw-query-billable-time#latest

David_Acevedo

Adam, thank you very much for your response, I'm trying to run the query but I'm getting errors. 
Is there any specific way to run this one? I ran it against the DWDataMart. Is there any variables I need to update? This is specifically for Incidents. 

Adam_Dzyacky

Yes, you'll need to define a few variables to get this to run directly inside of SSMS and those are the @StartDate, @EndDate, and @analyst where @analyst is the AD Username/SamAccountName of the analyst you want to query against.

As an example, here's what needs to be at the beginning that you should be able to see how you can modify -

DECLARE @StartDate datetime, @EndDate datetime, @analyst nvarchar(4000)
SET @StartDate = '01-01-2017'; 
SET @EndDate = '05-19-2017';
SET @analyst = 'usernameHere'

If you drop the original query into Report Builder without the above block, it should dynamically just create the parameters so they can be defined when you run the report.

David_Acevedo

That worked, thank you very much !

Adam_Dzyacky

Glad to be of help David!

Matt_Overton

Hi @Adam_Dzyacky, sorry to trouble you again but I've been trying this out too but can't seem to get it to run?

Report Builder prompts me to enter some parameters which is fine and I enter them like this:


But then when it runs, I get this. What am I doing wrong here?:

Matt_Overton

@Adam_Dzyacky Never mind, I figured things out in the end!