Creating filters for past N days

#1

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.

#2

Something like this?

image
image962×247 33.6 KB

#3

On the other hand, I also use the “Use Sql Server” (or “Use Sqlite”) extensions.

image

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
#4

Ok. One last image reply. This is what the Grindstone db looks like from SSMS…

image

#5

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.

#6

One other thing - do you have a link to the Extensions feature? I can’t find it anywhere in the documentation, so I have no idea how to get started.

Edit: Nevermind. I found it stickied in the forum listing.