Michael J. Swart

RSS Feed
RSS Feed
Email Me
Email Me
About Me
About Me

October 15, 2008

Reporting Services is Fun Again…

Filed under: Technical Articles — Tags: , , , — Michael J. Swart @ 12:51 pm

… or at least fun for the first time. Earlier, I wrote about the limitations that I came across with SQL Server Reporting Services (SSRS) 2005 when trying to implement a Pareto chart in the exact way that I envisioned.

Well I’ve been trying out SSRS 2008 and I was surprised (in a good way). The things that should be easy are easy and straightforward. The things that are hard are at least possible.

In the past, Crystal Reports and earlier versions of SSRS have caused me to waste a few hours wrestling with different settings. But with SSRS 2008, for the first time, my experience with reporting tools was not an exercise in coping with frustration.

What’s changed?

One big change is the set of charts that are available out of the box. These charts seem to have a 1-1 mapping between the charts found in Excel 2007. There must have been collaboration between SQL Server folks and the Office folks. There’s also an overhaul in the UI and dialogs that are used to specify properties of the charts.

Example / Tutorial

I’ve included a video (2.5 minutes, 1.5 MB) of my experience with creating a Pareto chart to the way I like it:

Watch this video

I’ve also included a video (2.5 minutes, 1.5 MB) of what it takes to add a table underneath the chart in order to more closely match the reports that come with SSMS.

Watch this video

Find the query I used in the comments section.

Caveat:

You can’t include these reports (i.e. reports created with the 2008 report designer) into Management Studio’s custom reports. I guess SQL 2008 is still using 2005’s report control. The issue is described in the Microsoft Connect bug #356519.

6 Comments »

  1. Don't know about how the formatting will turn out. Here's the script I promised:

    DECLARE @SystemIO FLOAT
SELECT @SystemIO = SUM(total_logical_reads + total_logical_writes)
FROM sys.dm_exec_query_stats;
 
SELECT TOP 20 
	[Row Number] = ROW_NUMBER() OVER (ORDER BY total_logical_reads + total_logical_writes DESC),
	[Query Text] = CASE 
		WHEN [sql_handle] IS NULL THEN ' '
		ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
			(CASE 
			WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
			ELSE QS.statement_end_offset
			END – QS.statement_start_offset) / 2))
		END,
	[Execution Count] = execution_count,
	[Total IO] = total_logical_reads + total_logical_writes,
	[Average IO] = (total_logical_reads + total_logical_writes) / (execution_count + 0.0),
	[System Percentage] = 100 * (total_logical_reads + total_logical_writes) / @SystemIO,
	[Object Name] = OBJECT_NAME(ST.objectid),
	[Total System IO] = @SystemIO,
	[SQL Handle] = [sql_handle]
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text ([sql_handle]) ST
WHERE total_logical_reads + total_logical_writes > 0
ORDER BY [Total IO] DESC

    Comment by Michael J. Swart — October 17, 2008 @ 5:05 am

  2. This was a great article and the videos are worth thousands of words of text. It was exactly what I was looking to and you saved me hours. 🙂 Thank you!

    Comment by Trevor Carnahan — May 24, 2011 @ 7:52 pm

  3. Trevor! That’s great to hear.
    I’ve actually found myself going through these videos myself afterwards too (Now how did I do that again?!)

    Comment by Michael J. Swart — May 24, 2011 @ 10:48 pm

  4. […] https://michaeljswart.com/2008/10/reporting-services-is-fun-again%E2%80%A6/ […]

    Pingback by How To Create a Pareto Chart in SSRS 2008 « Tower of Business Intelligence — November 2, 2011 @ 6:45 am

  5. Hi Michael,
    I may say that the video is very clear and didactic, I was also looking for a way to build pareto charts and this blog has been the final piece of the puzzle.

    In SSRS 2008 R2 there is a way to automatically perform a pareto char, but the final result of the formatting is not as good as if you set every component at your will as in your video.

    Best regards
    Xavier GS

    Comment by xavier_gs — December 6, 2011 @ 8:27 am

  6. Thanks for that comment Xavier.
    I find the same thing … Setting every component to the place you’d like is a really nice thing to be able to do.
    I find that the more control you want over the look of a report, the harder it is to do that using out-of-the-box features. In fact, it almost becomes easier to resort to Web developer and Software developer skills.

    Comment by Michael J. Swart — December 8, 2011 @ 10:18 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress