Thank you to all the troopers who attended my really-early-morning-cold-and-snowy session at SQLSaturday#65 in Vancouver, BC. It was such a great event; thanks to all volunteers, sponsors, speakers and organizers (kudos to Scott Stauffer (blog | twitter) .. Thanks to Todd McDermid (blog | twitter) too for helping me give out swags during my session, and thanks for the great powerpoint slide decks which I’ve used in 2 SQLSaturdays now! 🙂
As promised, here are the SQLSaturday#65 presentation materials for SQL Server 2008/2008 R2 Reporting Services from the Ground Up
Brent Ozar (blog | twitter) has a few pictures of the event 🙂
Sample Reports (pdfs)
Report with different visualization components (data bar, sparkline, indicator, gauge, chart)
Report with drilldown, barcode, gauge
Report using map (from ESRI shapefile for Canada) and Bing Maps layer
I love Reporting Services. I really do. But sometimes, just sometimes, it does generate extra work for me especially when clients are not using IE.
First Fix – ReportManager
This one is still a “temporary fix”. This still does not allow to scale width to 100%
To do this, we need to override a few CSS classes.
1. Go to the Reporting Services CSS file. This typically resides in a folder similar to:
D:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportManagerStylesReportingServices.css
Some of my projects are still in SSRS 2005 and PowerShell v1.0, so this script works and is tested on this environment only, for now.
I plan to port this to SSRS2008, PowerShell v2.0 when I get the chance.
I don’t think the code will change much; although one major change would be how I’m creating the proxy right now. PowerShell v2.0 has a cmdlet called New-WebServiceProxy.
To get this to work for PS v1.0, for now, I use New-WebServiceProxy.ps1 from http://poshcode.org/538 to create the proxy.
Steps are fairly simple
1. Create the proxy
2. Create an array of policies (ie existing users/groups for a particular report or folder)
3. Create a new policy
4. Create a new role, and add it to your policy
5. “Re”-set your policies — ex $reportserverproxy.SetPolicies($itempath,$newpolicies);
Just a few queries against the ReportServer database that might come in handy…
Of course, these are just some sample queries. Tune and modify according to your needs …
border:1px solid #E6DB55;
Just wanted to share some common “tips” or “tricks” I find myself using over and over again when creating reports.
I think I’ve had this draft for a while, and just never was able to publish it because things got so busy. Anyway, I will just publish in parts. Better late (and something) than never (and nothing!)
Common Report Tasks
Common page headers and footers
Report (reportname) generated by DOMAIN/User on 02/03/2010 4:51:03 PM
="Report (" & Globals!ReportName & ") generated by " & User!UserID & " on " & Globals.ExecutionTime"
Page X of Y
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
If you have start and end date parameters, and want to display them in your header or footer, with date formatted as MMM dd, YYYY
="Report Details from " & Format(Parameters!StartDate.Value, "MMM dd, yyyy") & " to " & Format(Parameters!EndDate.Value, "MMM dd, yyyy")
Free ebook from Microsoft Press: Introducing SQL Server 2008 R2
New updated version of SQL Server 2008 R2 Training Kit is out, and is full of awesome SSRS goodies (hands on labs, tutorials, presentations, and videos).
To view your SQL Server (or SSRS) reports using PowerShell, you can either use
- plain browser + URL string combo
- Report Viewer
- Web Services
The script below shows the first two options.
If you are going to go with Report Viewer (and personally, that’s my preference), you will need to download the ReportViewer redistributable package