Tuesday, August 16, 2011

SQL Server Reporting Services: Monkey See, Monkey Do

Wow. Had my portion of SCOM Reporting last week! A key customer of mine needed badly some Reports out of SCOM.

So first I tried the Generic Reports which are available in SCOM R2. But these reports didn’t fit the bill. So it was time to author some Reports myself. In this case two tools can be used:

  • Ad Hoc Report Builder (the web based Report Builder which starts when one click the option Design a new report in the SCOM R2 Console);
    image
  • Report Designer which resides in SQL Server Business Intelligence Development Studio.

Pro’s and Con’s
These two tools have their advantages and disadvantages. To make a long story short: the only advantage of Ad Hoc Report Builder (the simplified presentation of the Data Warehouse) is its disadvantage as well. Too much data AND options are missing so one can never create the top notch reports the organization requires.

And the disadvantage of Report Designer (looks too complicated for newbie's) is it’s advantage as well. Because when one masters it in a proper way the sky is the limit!

Back to last week
So last week I found myself in Report Designer, located in in SQL Server Business Intelligence Development Studio. Thanks to some serious good help from outside (Thanks a lot S!) I got the job done. The customer was very happy.

Where is my banana?
Only I felt myself a bit like a monkey pressing a button and getting a banana in return. The monkey never ever understands the whole mechanism which takes place when the button is pressed. Only know the outcome: a banana.

In my case the button presented me with a Report which needed some small modifications which I could apply. But the whole true concept behind, interpreting the queries in the Report, the parameter area definitions and so on, much of it eluded me. And that BOTHERED me! BIG TIME!

All hands on deck!
So it was time for some good thorough action. After some searching on the internet I found this book, all about SSRS 2008!

It starts from the very beginning like what’s a database, a table, column, WHERE clauses, constraints, INNER JOIN and so on. From there it moves on with a simple basic query and soon it creates a more complex query.

The book is written in such a way that even I understand it! :). Great to gain new knowledge and experiences!

So within a month or two I hope to have covered enough ground to translate all this newly gained knowledge to the SCOM Data Warehouse in order to create good SCOM Reports!

A new series is born
In the meanwhile I will post some articles about SCOM Report authoring. The first one in this new series will be about the Ad Hoc Report Builder. Since even that tool can give you some nice reports. And later on I hope to post articles about Report Designer! To be continued…

4 comments:

flipper said...

Hi Marnix,

Great to here that you are commencing this new series on custom report authoring.

May I suggest a possible scenario is the authoring of reports that query raw performance data?

While I know that raw performance data is retained in the database only for around 7 or 10 days making the authoring of performance reports based on raw data limited to a week that is still a reasonably useful timeframe for application experts who may need to assess the performance of an application during a known heavy utilization period etc.

In short, its seems there is always someone in the enterprise who asks about having a performance report sent to them using the raw rather than the aggregated data.

Keep up the great posts and I look forward to this series in the coming weeks.

Kind Regards,

Michael.

Tom Martin said...

Thanks Marnix. Look forward to your follow up posts with all the knowledge you'll be gaining.

One question, do you think one would need to know much about SQL first before jumping into a book like this? Or, can learning SQL reporting pretty much stand on its own?

Thanks,
Tom

Marnix Wolf said...

Hi Tom.

The book starts at the basics so even when one doesn't know zip about databases in general, or SQL for that matter, the first three chapters tells one a lot.

Having said that SSRS doesn't exsist without SQL, so some basic knowlegde is welcome. But just basic knowlegde of SQL will do since the book is really good and starts out simple.

Cheers,
Marnix

Bob said...

Marnix,

I really appreciate your blog and find myself in a very similar situation regarding reporting at the moment. This link may prove helpful in your learning. The default Report Builder that you get with SCOM is version 1.0 - even if you have installed on a SQL2008 R2 platform, which provides version 3.0. There is an enormous difference between the two. If you want to use the 3.0 version of the tool you simply replace the .exe on the Reporting Server that is called with the newer version. The change does work and in SCOM it launches Report Builder 3.0. Now it's just a matter of figuring out Report Builder... ;) Details here:
http://myitforum.com/cs2/blogs/bbird/archive/2011/03/18/replacing-the-version-of-report-builder-called-in-the-scom-2007-r2-console.aspx