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! :)

sqlsat65-schedule

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)
sqlsat65 - sample report - employee sales with visualization

Report with drilldown, barcode, gauge
sqlsat65 - sample report - employee sales with drilldown barcode and gauge

Report using map (from ESRI shapefile for Canada) and Bing Maps layer
sqlsat65 - sample report - canada sales on map
Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 10.0/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

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 Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles\ReportingServices.css
Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.4/10 (11 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)

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

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.7/10 (14 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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 …
Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.9/10 (17 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.6/10 (21 votes cast)
VN:F [1.9.22_1171]
Rating: +4 (from 4 votes)

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

VN:F [1.9.22_1171]
Rating: 8.4/10 (9 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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

One of these days I will hammer through the Web Services option, and also play more with what can be done with Report Viewer. And when I have good demos, I definitely will post them here :)

#==============================================================
#VANPASS August 2009
#PowerShell and SSRS (SQL Server Reporting Services)
#Donabel Santos
#http://www.sqlmusings.com / http://www.twitter.com/sqlbelle
#==============================================================
 
#--------------------------------------------------------------
#Approach 1: simplistic approach - accessing report via URL
#--------------------------------------------------------------
Set-Alias ie "$env:programfiles\Internet Explorer\iexplore.exe"
 
#note you can control how the report is rendered via the parameters in the URL string
ie "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReports%2fSample+Report&rs:Command=Render"
 
 
#--------------------------------------------------------------
#Approach 2: using the ReportViewer
#You need to download the ReportViewer redistributable package
#--------------------------------------------------------------
#I am qualifying this because I have more than one version in my system
#If you need webforms, use Microsoft.ReportViewer.WebForms
[void] [System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, `
	Version=9.0.0.0, Culture=neutral, `
	PublicKeyToken=b03f5f7f11d50a3a")
 
#Windows.Forms for viewing dialog box
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
 
#System.IO because I want to export to Excel
[void][System.Reflection.Assembly]::LoadWithPartialName("System.IO")
 
#System.IO because I want to open Acrobat
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Diagnostics")
 
#for credentials, if needed
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Net")
 
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer;
$rv.ProcessingMode = "Remote";
$rv.ServerReport.ReportServerUrl = "http://localhost/reportserver";
$rv.ServerReport.ReportPath = "/Reports/Sample Report";
 
#if you need to provide basic credentials, use the following
#$rv.ServerReport.ReportServerCredentials.NetworkCredentials= 
#	New-Object System.Net.NetworkCredential("myuser", "mypassword");
 
$rv.Height = 600;
$rv.Width = 800;
$rv.RefreshReport();
 
#--------------------------------------------------------------
#Show as Dialog Using Windows Form
#--------------------------------------------------------------
#create a new form
$form = New-Object Windows.Forms.Form;
 
#we're going to make it just slightly bigger than 
$form.Height = 610;
$form.Width= 810;
$form.Controls.Add($rv);
$rv.Show();
$form.ShowDialog();
 
#--------------------------------------------------------------
#Export to Excel
#--------------------------------------------------------------
#now let's try exporting to Excel
$mimeType = $null;
$encoding = $null;
$extension = $null;
$streamids = $null;
$warnings = $null;
 
$bytes = $rv.ServerReport.Render("Excel", $null, 
                                [ref] $mimeType, 
                                [ref] $encoding, 
				[ref] $extension, 
				[ref] $streamids, 
				[ref] $warnings);
$file = "C:\VANPASS\samplereport.xls";
$fileStream = New-Object System.IO.FileStream($file, 
              [System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();
 
#let's open up our excel application
$excel = New-Object -comObject Excel.Application
$excel.visible = $true;
$excel.Workbooks.Open($file) | Out-Null;
 
#--------------------------------------------------------------
#Export to PDF
#--------------------------------------------------------------
$bytes = $rv.ServerReport.Render("PDF", $null, 
                                [ref] $mimeType, 
                                [ref] $encoding, 
				[ref] $extension, 
				[ref] $streamids, 
				[ref] $warnings);
$file2 = "C:\VANPASS\samplereport.pdf";
$fileStream = New-Object System.IO.FileStream($file2, 
             [System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();
[System.Diagnostics.Process]::Start($file2)
 
 
#--------------------------------------------------------------
#Other ways to render or access your reports:
#SSRS Web Services
#--------------------------------------------------------------
VN:F [1.9.22_1171]
Rating: 10.0/10 (12 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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:
Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 7.5/10 (38 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 4 votes)
`