Microsoft® SQL Server® 2012 Reporting Services–A Review Part 2

Note: all the headings are from the book.  I’m including the chapter names for reference.

Chapter 4 – Basic Report Design

This chapter starts off pretty frank about Wizards.  In my opinion, wizards are a great tool when you need to prototype out something quickly and your are not going to use it in a long term way.  Maybe I’m jaded from my experience with SharePoint where the wizards actually put you behind the eight ball later.  That being said, it’s nice to see that this book starts off with a “minimal” wizard approach and skips going over the primary wizard to create a report.

Your mileage may vary but I think it’s the right approach.

This chapter starts out with a proposition to show you some basic skills around report design.  Barely into it all be fore you get the Warning Will Robinson…Danger.  Well not exactly but it does point out the fact that if you are running against SharePoint the process varies.  Namely, you use SharePoint for creating a new point instead of the Report Manager page.

Since these two approaches quickly flow into a single approach, it may have been nice to have put one of the two approaches in the appendix or devoted four pages to this where the first page starts out with “if SharePoint is not installed, skip steps 1A and 2A and go to Step 3 otherwise if SharePoint is not installed, skip 1A and 2A and go to 1B”.  Having the branches as A and B in the same block of text seems confusing to me.  Just saying…

Track on the steps, pretty straight forward once you’re past the To SharePoint Not To SharePoint Section.

You go from this…


to this


Pretty cool.

The Ribbon… What you say?  With most of UIs for Office and other products, the designers at Microsoft, did a refactor job on the menu.  Yup, they introduced change.  Queue the grumbling!  Chapter 4 takes on a quick 101 trip down the Ribbon highway.  Does this really apply to report design?  You betcha!  Knowing where things are is half the battle.

There are a lot of useful tips in the book but you might need to read closely to catch them.  For example;

One of the things I’ve learned about document management in Windows is that when I open a
document intending to save a copy, it’s a good idea to save the copy right away. This way, I don’t
make changes and then accidently save them on top of the original document. Let’s take care of that
task right now to avoid an “Oh, crud” moment later:

This should have been a “TIP” in my opinion.  The information is good but it is easy to miss.

The chapter goes on to give you a high level report design experience with clear steps.  This makes is easy to follow to build a report.  One of the interesting challenges between the versions of SSRS, is that each time, something pretty major has changed in how you address a report.  A shout out is given to the prior methods but it does not dwell on those “gone” and hopefully “forgotten” features.  Smile

Chapter 5 – Report Layout and Formatting

It’s interesting, the longer my career goes, the more technology seems to blur but people and stereo types seem to hold.  Like the author mentions early in chapter 5.


Ok, the author didn’t yell but I did. Devil  Why?  I also experienced the situation where you create this professional looking report for a bunch of Engineers and have it shot down in FLAMES for being not what they asked for.  Yup, simple Excel style reports were preferred.  My experience was with Crystal Reports then Access Reports back in the bygone days before SSRS.  Yes, this happened more than once.  Go figure.

At least that situation is far better than being on the other side where the client wants more data on a page than can be reasonably placed.

Ok, my mp3 player just went from an interesting Herb Alpert song to Scott Joplin’s “The Entertainer”.

Enough of a segue and on to the material at hand.  Since Chapter 4 ended with Matrix Reports, Chapter 5 logically moves right into more details.  Namely a List is a Table is a Matrix.  Say what?  It’s three three three reports in one you say?  Ok, things like properties come into play etc.  It just outlines the streamlining that SSRS has gone under where once there were three and now there is one.

After discussing the various types of report objects, the author presents a nice “page” that shows the various how the objects can be used to create an informative Dash Board.

In sorting group values, the author brings up a tip that might be more noticeable if it was set apart.  When the natural key does not lend it self to sorting, a sort key needs to be present to facilitate appropriate presentation of the data.  In this case, a Month sort is used as the example.  The month names don’t sort with their numeric equivalents.

There are a variety of data values in a warehouse where sorting is a challenge.  The report developer doesn’t always have the luxury of influence over how the data comes in.  Hopefully the ETL team has taken this into consideration and added the additional information for the Report Dev…  Sorry, got on the box.  Back to the review…

The rest of the chapter goes on to cover the rest of the report objects included in Reporting Services.  It provides a pretty decent overview of what’s available and some general scenarios when those objects can be utilized in a report.  Next, it’s designing data access.

Chapter 6 Designing Data Access

What do you talk about when it comes to data access?  In fact what do you think about?  Well, that’s what chapter 6 is about…

You get the basic drill:  A report uses a dataset that maps to a database through a defined Data Source connection.  You also get a quick bit about where you’re going to design (define?) the report and the pros and cons of the various products.  At this stage is a pretty high level treatment.

Moving on into the chapter, the author presents a good overview of taking the long view in describing what the report designer should consider.  I would add to it that when working on reports, it’s a good idea to have some basic understanding of the business.

We then do a walk down the types of data and different approaches.  You’ll need to get familiar with some terms here if you are not already.  An experienced data professional will probably glance through this chapter and only pick up snippets of interest.  A beginner can use this chapter as a resource to find out about the different types of SQL, data sources and other bits.  It’s quite a rich chapter and includes additional step by’s to build up working reports to boot.

There is quite a bit in this chapter about filtering a query and where that filter is applied.  This is an area where  I have found that a lot of performance improvements can be realized.   It’s nice to see this topic well covered.  I’ve worked with a lot of Devs who like to put everything into the application side and don’t leverage the data server.

Hmmm…. Data…. Server….  Do ya think it might be good at chewing on data… Just saying…


One of the biggest advantages a “dynamic” report has going for it is the use of parameters and the chapter does a decent job addressing this.  If all you produce is a static report, the users or clients of the reports will think your report is very nice but for REAL data, they’ll go back to their Excel files.  Hey, designing a report with sufficient set of parameters to meet the need may not satisfy the analyst but it will make the chief person happy and since they sign the checks…   Ok, just read the chapter… Smile

The chapter ends with a look at some of the possible data sources you might be working against.  It then logically flows into a discussion about Federating Data Sources.  You might wan tot look at The Federated Data Warehouse  Or see the Information service patterns, Part 1: Data federation pattern for some additional light reading.

Chapter 7  Advanced Report Design

Ok, queue the drum roll.  I think…

Hmmm… Making headers and footers a topic under advanced. Well, ok I guess.  To me this should be a basic construct in every report.  Ya ya ya, I know, page numbers right?  That’s your major driver for a footer?  Well, what about the date?  Certainly a Header or Footer item.  The flag that it’s CONFIDENTIAL?  Basic reporting stuff in my book but there is a lot more than just the basics in this chapter so it also belongs here.    If you need more of a 101 on basic report design…

there’s some older stuff on the Microsoft MSDN site for 2008 R2 or for a different look at report design, see books by Edward Tufte or Stephen Few.

The lack of non-product based material on general report design principals is why books like these should take a little time at some point to discuss the overall reasons why some pieces are in a report.

Anyway a nice change is being able to put fields in the header and footer sections.  The next section covers aggregates.  This is a section that a lot of technical folks struggle with.  Why?  Decision makers tend to be strong sensors.  This means that they don’t care so much about the granularity of the data as they care about the information needed to make a decision or two.  Aggregates provide that data.  Information Specialists tend to be more on the Thinker side where it’s all about the grain and they just don’t trust somebodies else’s roll up.

So, the net from all this?  This section is a good one and it shows reports formatted such that the decision maker can use it.  If you are a “Thinker” type and feel like skipping this section, you might want to pay closer attention.  That is unless the report you’re making is for you and you alone.

The next section or two takes you into some of the finer elements of report design.  Not all reports can be in Courier and be printed out on a dot-matrix fan folded feed printer.   It’s all the web now.  At least this section gives you a handy dandy HTML Formatting Reference.

Following formatting, it’s logical to discuss Master and Detail reporting.  Glad to see that’s what was next.  To some degree this was addressed from the data side in Chapter 6.  The treatment here is how to set up that relationship in the report.  Yes the example is from Adventure Works.   Folks seem to get hung up on that simple thing.  Ignore the fact that the samples provided demonstrate typical business scenarios, the argument is usually…

“We don’t sell no stinking bikes…”

Ok, so forget the company.  Point the report at your own data and have at.  The principals here still apply.

Throughout the following sections there are a lot of step through sections and examples that will improve the over all data output.

Chapter 8 Chart Reports

Down the wild woolly path to charts and graphs…

Beware, this is fraught with peril… from a meaningful report design perspective.  Hey, you put it in a graph it must be right…right?

Pay particular attention to the man behind the curtain… no, but do pay attention to Chart Type Summary (Table 8-1).  Especially the BEST USE column.  This chapter is decidedly shorter than the others.  Well worth a careful read to pick up some chart use skills.

Section II Wrap

Ok, it’s a wrap on Report Design.  This section is a good one to refer back to when working on report requirements and basic design.  It is a heavier section from the reading side of things but has plenty of step by step walkthroughs to get you to the end.  It is full of tips and a couple of keen don’t or restrictions about reporting services that should be considered.

Microsoft® SQL Server® 2012 Reporting Services–A Review Part 1

Intro and Chapters 1, 2 & 3

Going to user groups is a great opportunity to meet folks in the industry.  Volunteering to help out for community events never hurts either.  It was by doing this, I was able to meet Paul Turley, one of the authors of this book.  Through that I was able to get an early version on the condition that I give it a review.  <evil laugh…>

It’s part of the WROX Professional Series.  As such, it has the standard make up of;

  1. Whose this book for
  2. What chapters to dive into depending on what you need

All very nicely laid out and clear.

You might be asking… Why buy a book?  I mean, you can just bingle(bing/google) it.  Why read a book?

Chapter 1

To begin with the authors hit you up with questions you should keep in mind when starting a project.  It’s really tempting just to get right in and cut some code, see what works and adjust.  You know, Agile like…  not.  Agile has a few more processes than just heading out without a plan.  Pulling in to port and asking where you are might work for Captain Ron but not so much for programming…  the questions?

What are the requirements? What questions does your report need to answer? What’s not working? What needs to be fixed, and what will it take to build a solution to help you reach your goals? So, I ask you, what do you need? Why are you reading a book about Reporting Services? Do you have a specific problem to resolve, or do you just need to develop some basic report design skills? Do you need to build an entire reporting solution? Who are the users of these reports? Are they department workers, business managers, or financial analysts?

Ok, some good questions…  but is that really enough reason to get a book?

Chapter 1 completes the picture of who, what and where along with a brief look into the back story of reporting services.   It also addresses, in my opinion,  much of why getting a book is the secret sauce that puts it all together.

I’ve worked with Microsoft Technologies a long time and rather than take the on shoe fits all approach, Microsoft has always seemed to want to provide ten ways to reach the same conclusion.

This can be confusing when using bing or google since you might get the answer for part of the solution, write some code, then re-bingle and get a new answer.  You were going for clarity and ended up in the clouds.   There is a nice table in chapter one that presents the various tools Microsoft has defined for the product.

While the authors state that trading in BIDS for SSDT is a bit of a challenge, it’s still visual studio to me…<queue Billy Joel and “It’s still rock and roll to me”

With Chapter 1 out of the way, it’s time to delve into the more targeted chapters.  Because the book has a multiple audience focus, the first chapter addressed much of the content in broad strokes.  While tempting to just dig into the later chapters, skipping this would make targeting the information you want to get a bit more of a challenge.  So spend an hour, read it through.  I was glad I did.

Chapter 2

I’m glad the chapter covers basic installation.  I’m a bit more adamant about developers learning more of the gory details that go into server installation, etc. (a virtual network, is a great way to go) this chapter at least gets you through the basics.  Besides a fully detailed treatment of the install would be a book in itself if you consider all the fun that is a “Full” environment.

Here I’d like to see a cautionary statement about SharePoint.  Its been my experience that if you plan on using SharePoint in the future, you go ahead and install the plumbing from the beginning.  If you don’t, you’ll end up in SharePoint hell and PowerShell surgery.  Again just my opinion…maybe the warning will appear later in the chapter…

The instance ID warning is good advice for developers and network admins alike.  I’ve been in a situation or two when the instance id was changed and it played havoc with other systems trying to connect.

I liked the treatment of modes.  Having walked the plank, er, SharePoint, running webparts can be a much easier path than taking the plunge and tightly coupling it all.

In chapter 2, table 2-3 lists Command Line Utilities.  What’s missing is the PowerShell Provider.  From my experience with SharePoint, PowerShell is a key tool with Microsoft server technologies.  See CodePlex SSRS PowerShell Provider.

Overall chapter 2 give the keys to look to when setting up SSRS in a basic mode.  As you know from my previous blogs, I fully recommend taking the additional step of creating a virtual environment as far as you can to find the other gotchas along the way.  The section on security warns that “basic” is the least secure but to go beyond that…you enter the realm of domain accounts, kerberos and a few other gotchas.   Going through this chapter illustrates the SSRS is now at least a near first class citizen and as a result has the complexities associated with that status.

The chapter summary does a good job of outlining what’s there and how in-depth you should take the information.  Don’t expect to build an Enterprise solution using this chapter.  It provides excellent guidance for the basic install and, I think, does a good job of describing many of the complexities with an Enterprise install.  Trust me, for that puppy a team is needed.

Chapter 3

SharePoint!!!! Integration…

Ok, I’m sure this is going to be a heck of a ride…

Now, I’m going to take exception to the first part of the chapter titled Understanding SharePoint Technologies.  Ok, I like you guys but no partial chapter can even touch that monster.  But, I’m also sure that’s not exactly the meaning…  Well time to dive in and see how my understanding changes…

I really love the understated “take note of this” block immediately following the long list of SharePoint Features…  I’m calling them understated because I’ve gone through SharePoint Enterprise deployment.  So, take my concerns with a huge grain, heck, mountain of salt.  The basic install works very well and for a development or proof of concept, really, these tidbits meet the grade.

This just goes to show that any of the new technologies from Microsoft are of sufficient complexity that taking the time to get familiar and have good reference material is a key investment in sanity.

1 2 3(?) punch…

1. Introducing Reporting Services

I like the overview this chapter presents.  The product has changed quite a bit with 2012.  Understanding the offerings helps making an intelligent decision when considering the product.

2. Reporting Services Installation and Architecture

Good coverage for a single chapter and a great place to get started and set up a proof of concept.  The chapter does a decent job of getting you going, a good walk through or two and all the rest plus hinting at the deep complexities available.

3. Configuring SharePoint Integration

Again, good treatment of the basic steps.  Having walked down a SharePoint path or two, just dealing with SharePoint is the subject of many books, webinars, etc.  The steps worked just fine in a virtual machine on my development system.


As I make my way through the book, I’ll continue to put out more posts.  So far, so good.  Depending on your understanding of the technologies, the first three chapters may seem too narrow or overly complex.  As the complexities of Microsoft’s products grow this gets reflected in what is written about them.  You have to make a choice on how deep to dive will keeping it within a readable level.  Having experienced the earlier versions of these products, to me, these chapters fall on the narrow side.  That’s only because I want enterprise class answers from sections focused on a basic approach.  Hmmm… perhaps it’s MY point of view that needs a slight re-focus?  Yup.  First three chapters get a Thumbs Up… can’t wait to continue this with the next chapters…