Just a quick trick before the weekend starts and I can spend some quality time with something/someone who is not a pivot table…
I mentioned in my post Two Great Tastes That Taste Great Together that you can pull TimeByDay into an Excel report and then “group” 7 days at a time to get weeks. This function totally rules, but it does have one constraint – the format of those dates is always “startdate – enddate”, ( so a week would look like 1/1/2012 – 1/8/2012 ) and there is NO way to change it. Trust me on this, I spent a fair amount of time trying to use conditional formatting and number formatting to override this. Eventually I just gave up and decided, “When in Rome…”
So to keep things straight and comparable, for many dark purposes, I found a way to force other data which is not “grouped” like that to mimic the start – end format. It’s not terribly exciting, or hard to figure out, but I thought since I’m the one trying to lead people down this path, I might as well leave a few trail blazes behind. This portion of my query is written for timesheets out of Project Server, but I’m sure you could use it for any manner of start/end date columns in a table. Just stuff this shiny statement into your SELECT:
CONVERT(VARCHAR, MSP_TimesheetPeriod.StartDate+1, 101) + ‘ – ‘ + CONVERT(VARCHAR, MSP_TimesheetPeriod.EndDate+1, 101) as [Week],
That will make your weeks look like the same kind of date range as the grouped pivot table data out of Project. I promise to stop being so boring someday. Not everything can be as cool as Finding Documents with No Checked In Version Across a Site Collection, right?