One of the major reasons my company is trying Grindstone is the automatic nature of time assignment to work items, which works well. However, the reporting functionality is missing a pretty large piece of functionality unless I simply am not seeing it. In my experience, there is no way for me to determine how much time has been logged on any given day without exporting to Excel, filtering based on day, and combining time slices from the same work items to create a list of aggregated entries. That is way too much effort, so I’m hoping there is a way to create a filter to do this automatically.
Specifically, there would be a filter for each day of the current week (Sunday through Saturday), which combines like with like and shows a total per work item. If I, for example, log two 30-minute sessions against a work item called ‘Support’ on Monday, I would like a report for Monday with a single line item called support with a total time of one hour. We do not use start or finish dates for our projects because that concept makes no sense. Our items are never finished.
I found this, but it’s not the right solution. I need a list of items for a day in a filtered view; generating the graph takes too many clicks and the visual aspect is unhelpful in this context. It does show the right information if you click on the right filter in advance (e.g., click My Month and then generate the timesheet graph), but a single filter would be much quicker.
The filter view is perfect other than the fact that the available filters don’t seem to allow time slice aggregation based on a specific day. None of the default filters are useful for our organization, but the filter view itself is useful if I can get it to show our time.
On the other hand, I also use the “Use Sql Server” (or “Use Sqlite”) extensions.
The “Use Sql Server” extension synchronizes the Grindstone data to a sql server database on my dev server. With that db I aggregate and summarize data to my hearts content. For example, below I use a SSMS project to create a summary table that is fed into our support tracking system.
/*
calculate support time summary
*/
;WITH workitem_time
AS (
SELECT ts.WorkItemId,
CONVERT(DATE, ts.[End]) AS WorkDate,
Grindstone_02.dbo.RoundQtrHour(SUM(DATEDIFF(minute, ts.[Start], ts.[End]))) Minutes
FROM Grindstone.dbo.TimeSlices ts
WHERE CONVERT(DATE, ts.[Start]) >= '2020-12-19'
OR CONVERT(DATE, ts.[End]) >= '2020-12-30'
GROUP BY ts.WorkItemId,
CONVERT(DATE, ts.[End])
)
, report_data as (
SELECT CONVERT(VARCHAR, ts.[WorkDate], 23) Date
, CASE
WHEN p.Application IS NULL
THEN '-'
ELSE p.Application
END AS Application
, 'Don Nielsen' Resource
, CASE
WHEN p.[Source] IS NULL
THEN 'Other'
ELSE p.[Source]
END [Source]
, format(ts.Minutes / 60.0, 'N2') Hours
/*
, concat(
CASE
WHEN p.[Support Incident] IS NULL
THEN ''
ELSE concat('(', p.[Support Incident], ') ')
END
, wi.Name
) AS Notes
*/
, wi.Name as Notes
FROM workitem_time ts
JOIN Grindstone_02.dbo.vwListPropertyPivot p ON ts.WorkItemId = p.WorkItemId
JOIN Grindstone.dbo.WorkItems wi on wi.Id = p.WorkItemId
WHERE p.Task = 'Support'
)
select Date
, application as [Application/Application Area]
, Resource
, Source as [Support Source]
, sum(cast(hours as float)) as [Hours]
, max(Notes) as [Notes]
from report_data
group by Date
, application
, Resource
, Source
, Notes
order by Date
Thanks for the info. The timesheet graph is what I’ve been using, but it’s buried behind too many clicks unfortunately. A database would be ideal in many ways because then I could query the time, but my users will not be able to manage that type of situation nor will I be able to effectively support them remotely. I will probably design our in-house timesheet software to incorporate some of the features from Grindstone and then move back to that. It’s surprisingly difficult to configure this tool in some ways.
