SQLSaturday#65 SSRS 2008/2008 R2 from the Ground Up

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

Fix SSRS ReportViewer and ReportManager Display on Firefox

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

How to Programmatically Add a User/Group to your SSRS Report Using PowerShell

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);

Retrieve SSRS Report Definition, Role Assignments, Executions Using T-SQL

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 …

SSRS Tidbits – Common Tips, Tricks, Debugging and Other References (Part 1)

.ssrstip
{
background-color:#FFFEEB;
display:block;
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")

SQL Server 2008 R2 (SSRS) Free Ebook, Training Kit Out

Ebook

Free ebook from Microsoft Press: Introducing SQL Server 2008 R2

Download from:
http://blogs.msdn.com/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

Training Kit

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).

Download from:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d

SQL Server PowerShell : How to View your SSRS Reports (rdl) Using PowerShell and ReportViewer

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

• Report Viewer Redistributable 2005 SP1
• Report Viewer Redistributable 2008 SP1

Creating Reports From SharePoint Lists Using SQL Server Reporting Services (SSRS)

Now that you have collected all your information in your SharePoint lists, your business users will want to see reports off them.

You may want to leverage SQL Server Reporting Services for this (I would! I’d love to use SSRS for all the reports I need to create!), but we know it’s not an easy task.
Your options are: