SQL Server 2008 R2 was an exciting release for Reporting Services. It gave us features such as:
– Shared Data Sets
– Report Parts
– New Visualization, including spatial visualizations

There’s are also significant changes and new features to SSRS 2012 – but a some of these can only be materialized if you are using SSRS 2012 integrated in SharePoint 2010. Nonetheless these are still pretty exciting changes, and can change the landscape of reporting for our end users (read: more Self Service BI):
Read the rest of this entry »

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

Read the rest of this entry »

Your report is only as good as your requirements.

When I first worked with reports and reporting services, I was excited and giddy. Beside my plain old text and T-SQL, I now get to work with some shapes and colors! And look ma, no hands, err, it’s drag and drop!

But the fascination with colors, drilldowns, drillthroughs, what-have-yous fade away as quickly as that drag and drop. You realize fast that – although managers typically like the pie charts, the drill downs, the colored legends – if any number, or any minor thing for that matter, is not “right”, the whole report is not right, and all your work really goes down the drain.

Sometimes, it’s not because the report is “completely wrong”.
Read the rest of this entry »

Today has been quite an eventful day for me, feels like a mishmash of stuff happened today. Definitely worth a blog post.

Here are some of the things I learned and did today.

And man it amazes me how many smart smart helpful people are out there

Service Broker

I possibly might need to work on SQL Server Service Broker. I think this might be the right tool for a project, but I need to be certain so I need to give it a test drive.

Thanks to Aaron Bertrand (blog | twitter) for pointing me to these excellent excellent resources on SQL Server Broker.

http://mrdenny.com

http://http://itknowledgeexchange.techtarget.com/sql-server/tag/service-broker/

http://rusanu.com

Read the rest of this entry »

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

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 »

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 »

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 »