not included above, but possibly useful:<\/em>\u00a0 you may want to provide a multi-select dropdown of feasible resources, so that the user can pick which rows they want to see. \u00a0In my case, the query supporting this multi-select parameter might look something like this (untested!):\nSELECT isnull(Location,@nullReplacement) as Value,\r\nisnull(Location,@nullReplacement) + \r\n ' (' + cast(count(*) as varchar(10)) + ') ' as Label\r\nFROM Users u\r\nJOIN Comments c \u00a0ON u.ID = c.UserId\r\nWHERE c.CreationDate BETWEEN @DateBottom AND @DateTop\r\nGROUP BY Location\r\nHAVING COUNT(*) >= @minItems\r\nORDER BY COUNT(*) DESC ;<\/pre>\n<\/li>\n<\/ul>\nLet’s get started!<\/h2>\n
The first part of the query is a set of 3 CTEs that serves to identify a set of resource rows and assign sorting and colors to them.<\/p>\n
Note that I’ve used CTEs at the beginning of this query, and switched to temp tables for the latter part of it. \u00a0This is purely a matter of taste, in many cases, but here I’m using CTEs for some very inconsequential data and then switching over to temp tables for large datasets.<\/p>\n
with resourceColors as (\r\nselect 0 as resourceColorNumber, 'PaleVioletRed' as resourceColor\r\nunion all\r\nselect 1, 'LightSalmon' as resourceColor\r\nunion all\r\nselect 2, 'Tan' as resourceColor\r\nunion all\r\nselect 3, 'DarkSeaGreen' as resourceColor\r\nunion all\r\nselect 4, 'LightSteelBlue' as resourceColor\r\nunion all\r\nselect 5, 'CornflowerBlue' as resourceColor\r\nunion all\r\nselect 6, 'Plum' as resourceColor ) ,\r\nresources as (\r\nselect row_number() over (order by count(*) desc ) as resourceNumber,\r\nisnull(Location, @nullReplacement) as resourceID\r\nfrom Users u\r\njoin Posts p on u.id = p.OwnerUserId\r\njoin (\r\nselect comments.ID, PostID\r\nfrom Comments\r\njoin Posts on Comments.PostId = Posts.ID\r\nwhere Posts.CreationDate between @DateBottom and @DateTop ) c\r\non p.ID = c.PostId\r\nwhere p.CreationDate between @DateBottom and @DateTop\r\ngroup by isnull(Location, @nullReplacement)\r\nhaving count(*) >= @minItems ) ,\r\nresourceInfo as (\r\nselect resourceID, resourceColor, resourceNumber\r\nfrom resources m\r\njoin resourceColors rc\r\non m.resourceNumber % @maxColors = rc.resourceColorNumber\r\n)<\/pre>\nThe first CTE, resourceColors<\/em>, just picks up the colors available for different rows in the “graph”. \u00a0 (Note: \u00a0in some cases you might want to show these as a key on top against each resource, or against the resource names or IDs). \u00a0As explained earlier, you might have many ways of doing this, but however you do it, you’re going to want to know what the number of available colors (@maxColors, in my example). \u00a0Each color is assigned a consecutive number, starting with 0.<\/p>\nThe second CTE, resources<\/em>, picks up the list of resources we are going to display in rows. \u00a0Each resource is assigned a unique sorting value — here, I’ve chosen to sort by the highest to lowest thread activity, but you might do it alphabetically, or however you like,<\/p>\nThe third CTE, resourceInfo<\/em>, puts this information together, assigning a resourceColor to each row based on its expected sorting according to its unique resourceNumber plus a modulus expression using resourceNumber and @maxColors. (That’s why we numbered the available colors starting with 0 in the first CTE.)<\/p>\nGet a set of appropriate events that fit your selected resources and datetimes<\/h2>\n
We’ll create a temp table that uses this information and gets data for the start and end times, as well as specified colors, for our resources, based on the final CTE:<\/p>\n
select\r\nr.resourceNumber,\r\nr.resourceID,\r\nresourceColor,\r\np.CreationDate as EventStart,\r\nc.AnsweredDate as EventEnd,\r\np.Title\r\ninto #resourceEvents\r\nfrom resourceInfo r\r\njoin Users u\r\non isnull(Location,@nullReplacement) = r.resourceID\r\njoin Posts p\r\non u.Id = p.OwnerUserId\r\njoin\r\n(select\r\nPostID,\r\nmax(comments.CreationDate) as AnsweredDate\r\nfrom Comments\r\njoin Posts on Comments.PostId = Posts.ID\r\nwhere Posts.CreationDate between @DateBottom and @DateTop group by PostID\r\n) c\r\non p.ID = c.PostId\r\nwhere p.CreationDate between @DateBottom and @DateTop;<\/pre>\n… there’s probably a better way to get a relevant sample data set from StackOverflow data, but, hey, it’s my first time, and it will serve for this example.<\/p>\n
Now to derive our calendar grid<\/h2>\n
Here’s where the DateDimension table comes in. \u00a0Whether there was activity in it or not, we need a cell for each row for each possible time period. \u00a0We create a temp table for this purpose, mostly because we happen to want half-hour increments and our DateDimension table stores only to-the-hour granularity:<\/p>\n
-- we'll create a \"half hour dimension\":\r\nselect\r\nDatePart(month,d.Date) as MonthNo,\r\nDatePart(week,d.Date) as Week,\r\nd.Date, DatePart(Day,d.Date) as Day,\r\nd.IsWeekend, Hour,\r\ncast(cast(Hour as varchar(2)) + ':' + \r\n case when BottomHalfHour = 0 then '00' else '30' end as DateTime) \r\n as StartTime,\r\ncast(cast(Hour as varchar(2)) + ':' + \r\n case when BottomHalfHour = 0 then '29' else '59' end as DateTime) \r\n as EndTime,\r\ncase\r\nwhen Hour = 0 and BottomHalfHour = 0 then 'mid nt'\r\nwhen Hour = 12 and BottomHalfHour = 0 then 'noon'\r\nelse cast(case when Hour < 13 then Hour else Hour - 12\r\nend as varchar(2)) +\r\ncase when BottomHalfHour = 0 then ':00' else ':30' end +\r\ncase when Hour < 12 then ' am' else ' pm' end end\r\nas halfHourLabel\r\ninto #halfHours\r\nfrom \u00a0dbo.DateDimension d join\r\n( select top 24 Day-1 as Hour, 0 as BottomHalfHour from \u00a0dbo.DateDimension\r\nunion all\r\nselect top 24 Day-1 as Hour, 1 as BottomHalfHour from dbo.DateDimension ) h\r\non 1=1\r\nwhere d.Date\u00a0between @DateBottom and @DateTop\r\nand h.Hour between @HoursBottom and @HoursTop\r\norder by 1,2<\/pre>\n… Notice that I include both a MonthName for display and a Month numeric value to ensure correct sorting. \u00a0If your data may span more than one calendar year, you’ll want to include Year too, of course. I’ve also included a “Week” value, which you could use for both sorting and grouping as an interim level between Month and Day, should this suit your purposes, although I am not using it here.<\/p>\n
Now put it together for the RDL dataset<\/h2>\n
… and of course, drop the temp tables for neatness’ sake:<\/p>\n
select h.MonthNo,\r\n-- add Week here too if you want\r\nh.Date,\r\nh.Day,\r\nh.Hour,\r\nh.StartTime,\r\nh.halfHourLabel,\r\ne.resourceID,\r\ncase when h.IsWeekend = 1 then 'Gainsboro' else 'WhiteSmoke' end as dayColor,\r\nDateName(month,h.Date) as MonthName, isnull( e.Title, 'No Title') as Title,\r\nisnull(e.resourceColor,'White') as resourceColor,\r\ne.resourceNumber from #halfHours h\r\nleft join #resourceEvents e\r\non cast(eventStart as date) = h.date and\r\neventStart= cast(h.Date as DateTime)+ h.EndTime\r\norder by 1,2,3,4,5,isnull(e.Title,'ZZZZZ');\r\n\r\ndrop table #halfHours ;\r\ndrop table #resourceEvents ;<\/pre>\n… \u00a0the final SELECT is really pretty simple, and has everything we’ll need based on the previous queries. \u00a0The only thing it’s adding in is a dayColor <\/em>value, for use in the headers, so that weekends are clearly distinguished from weekdays, and this is certainly not a critical requirement.<\/p>\nWhat’s really going on here? \u00a0We’re talking our calendar data and creating a left join with our event data. \u00a0So, some matrix cells will be empty (NULLs) because nothing is going on for a given resource for that time period.<\/p>\n
Enough SQL? \u00a0Ready to have some Designer fun?<\/h2>\n
OK, here you go.<\/p>\n
\n\n\n![\"\"](\"\/lisa\/wp-non\/migrated\/2017\/7\/Wizard.PNG\") <\/a><\/td>\n | As promised, having dumped the query into the RDL, I can use the Report Wizard to group things the way we would expect for a calendar grid — it’s straightforward enough that it’s easier to show you what I grabbed in the Wizard than explain in words (fullsize if you click), but the jist is:<\/p>\n\n- row group on resources<\/li>\n
- column groups on time periods, largest on the outside descending to the smallest on the inside<\/li>\n
- detail cell contents on… anything, really. \u00a0You’ll see why.<\/li>\n<\/ul>\n
And now we have very little left to do.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Stupid Designer pet tricks<\/h4>\nHere’s all that’s left to do in the Designer to make this work:<\/p>\n \n- Set the row group to be ordered by resourceNumber, because by default it’s going to add a sort by the value you provided in the grouping. \u00a0We want our specified sorting to reflect our expected cycling through color values.<\/li>\n
- Similarly, the outermost column group needs to sort by MonthNo, not MonthName. \u00a0For clarity, if you prefer, group on MonthNo too. \u00a0I just tend to pick up on the display items when I’m working in the Wizard, which I do very rarely. The innermost column group, which will display our halfHourLabel, should sort by StartTime and, for clarity, it might as well group by StartTime too. \u00a0Again, the halfHourLabel was for display purposes.<\/li>\n
- Set the Fill (backcolor) of the \u00a0Day and halfHour header cells to dayColor, remember we’ll just do this for fun to distinguish weekends from weekdays::
\n![\"\"](\"\/lisa\/wp-non\/migrated\/2017\/7\/BackcolorDayHeader.PNG\") <\/a><\/li>\n- and finally — this is the critical trick — change two properties of the matrix detail cells<\/strong>\n
\n- the Fill (backcolor) of the detail cells should be\u00a0=Fields!resourceColor.Value<\/em><\/strong>, just like header cells’ Fill or backcolor you changed in the last step<\/li>\n
- the Expression for the textbox in the detail cells should be blank<\/strong> (I use =space(0)<\/em><\/strong>).<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
That’s really it! \u00a0This works natively — again, without any iif() to check to see if you have a resource event for a cell, although if you like you can complicate things by adding one — \u00a0because the null cells don’t get any fill at all, they stay blank. \u00a0In every version of SSRS I’ve tried.<\/p>\n |
![\"\"](\"https:\/\/spacefold.com\/lisa\/wp-content\/uploads\/2017\/07\/GanttExample-1-300x169.png\")
<\/p>\n![\"\"](\"\/lisa\/wp-non\/migrated\/2017\/7\/HoursTopBottom.png\")
<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n![\"\"](\"\/lisa\/wp-non\/migrated\/2017\/7\/GanttResult.PNG\")
<\/a><\/p>\n