Hello!

Our techs are looking for a quick way to pull up their time logged on incidents for the week.  It looks like I can do this through Cube Processing but I cant seem to get the Excel sheet correct.  Is there a way I can just add a report to their console that performs the above?

Thanks,

Rob

Hi Rob,

This is certainly something that can be accomplished using SSRS and have published to the console.  You can use Report Builder to create a simple table and add your query to a new Data Set.  I have a query below that should get you what you need.  Depending on what you want to show, you could add parameters to the report file so the techs could do searches by name, date range, etc.  Also, you can have it total the minutes in the report for the week, or show them a break down by Incident.  If you need some assistance with that, just let me know.

Select I.Id, I.Title, WU.DisplayName, B.TimeInMinutes, B.LastUpdated
From IncidentDimvw I
--Billable Time
left outer join WorkItemDimvw W on I.BaseManagedEntityId = W.BaseManagedEntityId
left outer join WorkItemHasBillableTimeFactvw BFact on W.WorkItemDimKey = BFact.WorkItemDimKey
left outer join BillableTimeDimvw B on BFact.WorkItemHasBillableTime_BillableTimeDimKey = B.BillableTimeDimKey
--Billable User
left outer join BillableTimeHasWorkingUserFactvw WUF on B.BillableTimeDimKey = WUF.BillableTimeDimKey
left outer join UserDim WU on WUF.BillableTimeHasWorkingUser_UserDimKey = WU.UserDimKey
Where GETDATE() <= DATEADD(DAY, 7, B.LastUpdated)
Order By WU.DisplayName Asc

Hi Christopher!

That's a great start!  Does this report pull data from the "Time Worked" field on the incident forms?  Or does this calculate the time between when the IR was opened and then closed?  I also added a parameter for selecting a date.  Do I just need to simply add the parameter to the list on the left, or do I need to to drag it into the form somewhere?

Thanks for your help!

Rob

Hey Rob,

This is calculated based off of the Time Worked field on the Incident form.  To add a parameter, there are two things you need to do.  First, create the parameter in Report Builder under the Parameters section.  Second, add the parameter either to the SQL query or to the Filter section of the Dataset properties.  The first one is considered a SQL parameter while the other is a report parameter.  Either one will work for your situation.  Once you add the parameter and run, SSRS will add the header automatically that prompts the user to enter in the parameter's value.  It's pretty cool!

- Christopher

Hey Christopher,

Thanks for the query, if you have time can you also guide me through some more customization I can do with this.

Thanks

Boris