I know what you’re thinking - another four-letter acronym that starts with an S. SCCC. Maybe you weren’t thinking that. Nevertheless, you are thinking about it now. Just kidding, man. Remember when custom was cool? “That’s my 76 Firebird, it’s custom, man!” Do people talk like that anymore?
Through out my 16 years of programming I have found some pearls, some absolutely cool stuff, and most of it free. I remember when I got some free code for the first time, from this MS Access magazine article, around 95. It wasn’t quite free, I got it because I subscribed to the magazine. The particular piece of code I want to talk about was this Calendar Report, that ended up making me really look good. Thanks a lot, to that unknown contributor of the Calendar Report. It allowed me to make a printed out calendar view with the company’s business schedule on it. Every job I had after that I was able to offer that pearl to my new customers because it had value to them. I was able to customize it to their business - easy - too easy. That gem was cool, and it was custom!
Recently, I was given a task to create a dashboard using Reporting Services. I haven’t used SSRS since 2006 and had heard that there were some really cool, new features. Back then, I remember having to use tricks to get things to work, and also, finding out the hard way, that sometimes I had no tricks to use. However, this time around things just worked. I was lucky that most of what I tried to do worked out. The dashboard opens up to yesterday’s date, and the data is only good for the past - imagine that, for some reason they have no future data. Of course - what do all users do? they click on a date in the future and the format of your report, and the things that were supposed to be, didn’t happen. I‘m not a fan of the built-in report parameters control - if that’s what it’s called. You know that portion of the report where you select a parameter, or two, or three... then you click on the View Report button. I just don’t like it, especially when it comes to using the Calendar control. You need to click on the little icon - then a calendar appears - then you click on the date - then you click on the View Report button. That’s three clicks - two too many.
So I Googled, “custom calendar control” & “SSRS”, for what I thought someone would have created by now. I did not find one, but I did find this example from Ryan Duclos . He has a blog post titled Ten Common SQL Server Reporting Services Challenges and Solutions . Let me tell you this is a gem! It does exactly what my scheduling report did.
I need to give thanks to Ryan, for sharing his Calendar Report!
This is what I did. I picked out some cool colors to use for the calendar! The hardest part.
Because this Dashboard is showing information one day at a time, I am using only one calendar. When a day is selected I have the report basically do a refresh by adding the name of the report (CalRpt) in the Go To Report action. I added a Go To Report action to the Day field in the CalMatrix control. Take a look at the report parameters. Ryan explains that the StartDate parameter defines the first Sunday, and the EndDate parameter the last Saturday, to create the display on the calendar. I added the Selected Date parameter to capture the day selected and to use to run my report query.
Here are the (hidden) textboxes that I use to make the calendar control work. They are needed in case the user wants to go to a different month.
The whole idea is... to not show either of these. Not the parameters and not the pretty textboxes you see above. But they are needed to make the calendar control navigable. Whenever a user switches to a different month, either previous or next, some calculation is done to keep track of what the next calendar will look like. The appropriate values for the report parameters are also being updated. The textboxes named Day, NextMonth, and PrevMonth are all using the Go To Report action. When you look at the expressions I use, you will see how this works.
I am attaching a sample report. In the report I hide the textboxes and the report parameters to show how I expect the user to see the report. You will want to show these to help understand how this is being done. I don’t think I need to, nor do I want to get into a big explanation. I have had fun writing this, but I got to get to work, man. If you need help in understanding it, by all means contact me, and I will help as much as I can.
So it’s my turn now to be the contributor, I hope someone finds it useful. I hope that someday, someone is saying “thank you! whoever you were?” - kidding. I would like to say in all sincerity, that I can’t give thanks enough to the contributors, the bloggers, and the people that have shared code. They take the time out of their busy lives to pass it on - and I have benefited.
Happy Thanksgiving - Rick