This is going to be the first in a series of blog posts about Microsoft’s SQL Server Reporting Services (SSRS). Each post will build off of a previous one so I will be sure to let you know where to start in future posts.

SSRS gives you the ability to create and manage reports from a variety of data sources. For us, the most used data source is SQL Server but we have also used SQL Azure and MySQL.

Requirements / Setup

For these posts I will be working with SQL Server 2012, Visual Studio 2013 and the Adventure Works database (http://msftdbprodsamples.codeplex.com/releases/view/55330).

SSRS does have a Report Builder but I have not used it much so will not be going over it and instead have built them using Visual Studio. In order to create report projects you will need to download SQL Server Data Tools (listed as SQL Server Tooling on this page) as well as SSDT-BI for Visual 2013 here: https://msdn.microsoft.com/en-us/data/hh297027.

Now that our environment is set up we can jump into Visual Studio.

Visual Studio Project Setup

The first thing you should notice when doing a File > New Project is the new Template section named Business Intelligence that was created with the SSDT-BI updates. We are going to select the Reporting Services and the Report Server Project. Choose a name and select OK.

Visual Studio Project Setup

Report projects start out very empty with only three folders – Shared Data Sources, Shared Datasets and Reports.

Shared Data Sources

A data source is the connection to where your data is stored which could be SQL Server, MySQL, SQL Azure or any of the other listed connections. You can have multiple Data Sources in a project if your reports need to get data from multiple places. Data Sources have the extension .rds.

Shared Datasets

Datasets are the queries used to get your data from a Data Source. You can retrieve data from a specific table, write a query right in the Dataset or from a Stored Procedure stored at the Data Source. A Dataset must have a Data Source. Datasets have the extension .rsd.

Reports

If you can’t tell from the name of the folder, this is where the report files are kept. Reports have the extension .rdl

Also note the “Shared” in the folder names. As you will see later you can create Report specific Data Sources and Datasets but the ones in this folder can be accessed by all of the reports in the project.

Connecting to a Data Source

First step in the process to create a report is to set up a Data Source. For the most part in this series I will be using a connection to SQL Server 2012.

Right-click on the Shared Data Sources folder and Add New Data Source. You are presented with a screen to name your Data Source, select the type of connection and add a connection string. You can paste/type in the connection string in the text area or click the Edit button to have it build the connection string for you.

Shared Data Source Properties

Creating a Dataset

For this initial report we are going to just do a simple output to list all of the sales people in the database along with some of their basic information. We can do this a couple ways – build the query ourselves or, if you look in the Views available in the database there is one that gets all of the Sales Person data.

Right-click on the Shared Dataset folder and Add New Dataset. In the first dialog box that comes up you name the Dataset, select your Data Source and you have a few options on how to get the data. I would say the most used options are probably Text and Stored Procedure. Note: It is always a good practice to give your Data Sources and Datasets meaningful names.

With Text selected you can write a SQL query in the textbox given that will run against the Data Source. A Query Designer is also available so you can build it and see what data will come back. In the Query Designer there are different ways to construct your query but I have only the SQL and Result Panes visible here.

Query Designer

Here I am selecting all of the columns in the Sales.vSalesPerson view. Selecting OK in the Query Designer will insert your query into the text box.

Before selecting OK on the Properties window click the Refresh Fields button and then on the left side navigation go to Fields. This is a list of all of the Field items being brought in from the query. The right side is the name of the field as specified in the query and the left side is a place for you to optionally change the name of it that will be used in the reports. I recommend not changing them here but giving them an alias in the query if they need friendlier names. Refreshing the fields is not required on the creation of a query but if you ever change it to add/remove fields it will be required so I just get into the habit of hitting it with any change.

Report Setup

We have a place to get the data from and the data we want to report on so now we need a place to display it. Right-click on the Reports folder and (pay attention here) go to Add > New Item. Selecting Add New Report will send you through the Report Wizard and while it may be ok with a simple report we are doing now, it is more of a hassle when you start getting into more complicated things. Choose Report (not Report Wizard), type a name (mine is SalesPeople) and select Add.

You should see a screen similar to the one below.

Report Setup

If you do not see the Report Data window, click anywhere inside the Report Design window select VIEW in the navigation and the very last item in the list should be Report Data. If the Report Design window is not the main focus then this menu item does not always show up. Some of the folders in the Report Data window should look familiar – Data Sources and Datasets. These are where you define the report specific items in each.

Like last time, we need to start out setting up the Data Source. Like I mentioned earlier you can create a Data Source that is specific to the report and I am sure there are cases where this may be needed but I would lean towards that being an exception rather than the rule. So, let’s select the option Use shared data source reference, choose our SQLServerDataSource from the dropdown list and of course give it an appropriate name.

And finally, adding our report Dataset. When creating a Dataset for the report you can choose a shared dataset or embed one in the report. For datasets I have gone more of a case by case basis on whether to use shared or embedded datasets and you can change it later on and switch to a shared dataset if you end up needing the same query in multiple reports. Here, select the shared dataset SalesPersonData, hit Refresh Fields and OK. You should now see the dataset and all of the fields available from it.

Creating a Report (Finally!)

Now we are at a point to put the pieces on the page. The next window we want to pull up is the Toolbox (VIEW > Toolbox). Let’s take a Table and drag it to the report stage. A note here – the report only ever refers to this item as a Table in the Toolbox. Once it hits the report stage any reference to it calls it a Tablix.

Visual Studio defaults the Tablix with two rows and three columns – the top row named Header and the second row named Data. Click once anywhere inside a field. A new row of grey/tan fields become visible which give you the ability to control row or column level properties.

Creating a Report

You will notice that in the row of the Data this new box has three horizontal lines in it. This will denote a data row. This row will be repeated for each record in the Dataset it is tied to. Right-clicking any of these will bring up a menu of options to change. Left-clicking on the top left will display a move button so you can drag it around the report stage.

If you right click on the top left grey box a menu comes up; select the last item, Tablix Properties. Here you can name the Tablix select a Dataset and a whole slew of other things that we won’t go into in this post. For now, let’s rename the Tablix and select the dataset we created.

There are a few different ways to display a field in the report. One is to go back to the Report Data window, select one of the fields from the dataset and drag it to the Tablix. You will see columns light up as you hover over them and dropping the item will add the field name in the header row (which Visual Studio tries its best to predict) and the field name surrounded by brackets, which designate non-text data, in the data row. If there are no empty columns a highlighted line will appear between columns where it will add the data you have selected. Another is, if you hover your mouse over a field in the data row a small image appear. Clicking on it gives you a menu with all of the dataset’s fields and selecting one will give you the same result as the drag and drop. If you change a data field in this manner the header row will not automatically update.

After going through and adding a handful of fields I ended up with this setup.

Report Fields

Now let’s take a look at what we have made so far. Above the report stage there are two buttons named Design and Preview. Currently we are in Design mode because we are, well, designing the report. Click the Preview button and you will see the result of all of your hard work. No, it doesn’t look like something you would want to present to your boss or a client but it is just a step on the way to a presentation worthy report.

Report Data

Basic Styles and Formatting

Let’s just take a couple minutes to do a bit of clean up. Switch back to Design to make these changes.

Right-click outside of the report stage (in the dark theme it is the dark grey around the report stage. You should get a menu that has a View option that will expand and allow you to check Ruler. Drag the edge of the report stage to the 8 inch mark. Using the extra grey row on the Tablix, change the width of each column to better fit the data and have the report fill the width of the report stage. You may need to switch back and forth to Preview it.

Next, click in the textbox with First Name so it has a dark border around it (when hovering it should still have a mouse pointer when you click and not a text cursor). Hold the Shift key and click in the Sales YTD textbox so the entire header row has a dark border then in the toolbar area select the B to bold all of the text in this row.

Report Formatting

Looking better already. The last update for this post is to format the Sales YTD as currency. Back in Design mode right-click the SalesYTD data textbox and select Text Box Properties. On the left side navigation go to Number and select Currency in the Category options. Check the Use 1000 separator (,) box and leave the other options as default. One last preview to see the changes.

Report Formatting

There are a lot of ways to display and report data in SSRS – I’m still learning new things with each report I make. A strong knowledge in building SQL queries is also very good to have because if you aren’t pulling in the correct data then no matter how interesting the report looks and how well it is presented it won’t be useful.

See Part II: Groupings or Part III: Parameters in this three part series. 

I try, at least once every couple months, to read a book that is not directly about software development, Scrum, etc. My thinking is that all tech and no business makes Don a dull boy; I really need to develop myself as a leader and developer of employees as much or more than a curator of custom applications.

I recently finished reading Creativity, Inc.: Overcoming the Unseen Forces That Stand in the Way of True Inspiration by Ed Catmull,Cover of the book Creativity Inc.one of the founders of Pixar. You’ve seen his partner’s name (John Lasseter) extensively in Pixar movie credits but you’ll see his name as well if you pay close attention. Ed has an extensive computer science pedigree, including a doctorate in computer science from the University of Utah but bit hard on his pursuit of the first computer-generated movie in the late 80s.

Back to Creativity, Inc. It is a wonderful book and I recommend it to anyone charged with managing or participating in a highly creative professional team – in any field. In one of the chapters Ed goes into an explanation of some things that Pixar does/does differently from other film studios during film development and which he attributes their thus far unbeaten streak of ~14 blockbuster films.

I found so much in a couple of value-packed pages that I want to share them and show it through my personal Scrum/software development lens:

Dailies, or Solving Problems Together

In the fall of 2011, eight months before the release of Brave, a dozen or so animators ambled into the dailies meeting in the screening room at the far end of Pixar’s atrium. It was just after 9am, and more than a few attendees were sipping cups of coffee in an attempt to look alive.

Scrum adherents will recognize the Pixar daily to be a combination of daily standup/daily scrum and product demo. While Catmull is a serious geek, he left the world of hardware and software development long before the 17 signatories cobbled together the Agile Manifesto. Nonetheless, the ideas are so universal, self-evident and effective that we see the concept of cross-functional teams, inspection and incremental creation in feature animation development.

The director Mark Andrews, had stepped in to direct Brave midway through production at the request of John and myself, and he was widely seen as an inspiring leader. A proud descendant of Scotland, where Brave is set, Mark urged his crew to join him in wearing a kilt to work every Friday. Many viewed him as nothing short of a force of nature. “Mark talks to you as if he’s trying to drown out an F5 class tornado behind him – and winning,” is how one animator described him. “I suspect he consumes plutonium pills.” The dailies meeting would do nothing to disprove that suspicion.

“Good morning, everybody! Wake up!” Mark yelled, kicking off an hour-long session during which the assembled animators shared glimpses of the scenes they were bringing to life. Mark watched carefully and gave detailed notes on how to improve each scene and encouraged everyone else in the room – a rigging supervisor, the movie’s producer, its head of story and the other animators – to do so as well. The goal of this meeting, as with all dialing meetings, was to see the shots, together, as they really were.

Time to Share Clock

Dailies are a key part of Pixar culture, not just because of what they accomplish – constructive midstream feedback – but because of how they accomplished it. Participants have learned to check their egos at the door – they are about to show incomplete work to their director and colleagues. This requires engagement at all levels, and it’s our directors’ job to foster and create a safe place for that. Mark Andrews did this at the Brave meeting by being irrepressible: singing ‘80s songs, reveling in people’s nicknames and mocking his own drawing ability as he hurriedly sketched out suggested tweaks.

The Director as Product Owner. Lively and impassioned Product Owners motivate a self-directed team to raise their game to a new level. Their engagement serves as an example for the entire team to evaluate, consider and provide ideas. Candor and trust embolden each team member to know that the objective is to improve the quality of the work product.

“Is that all the energy you got for me today?” he teased one sleepy colleague. To another, whose work he deemed flawless, he shouted the words all animators yearn to hear: “Final that! Bang!” Whether or not all the animators would get that same go-ahead, everyone could count on this: When each finished his or her presentation, the room would burst into applause.

Esprit de corps and relentless pursuit of a team-defined Definition of Done drive the team – but only the Product Owner can impart the mantle of Done.

This wasn’t a pep rally, though. The critiques that were offered were specific and meticulous. Every scene was prosecuted relentlessly, and each animator seemed to welcome the feedback. “Is that stick big enough for everybody?” Mark asked at one point, referring to a flimsy-looking branch that was supposed to keep a heavy door propped open in one scene. Several people didn’t think so, and as Mark scribbled with a stylus on a tablet in front of him, a sturdier log appeared on the screen on the front of the room. “Better?” he asked. One by one, each scene that the group reviewed raised new issues. That old man who just ran up a flight of stairs? He should look more winded. The facial expression of a young spy? It could be more devilish. “Chime in!” Mark urged. “Sound off!”

Team members responsible for each unit of work present their own work product, advocate and take input on fine points all in service of improving the quality of the product – with the smallest amount of personal pride getting in the way as possible.

For all the barking and levity, you could feel the focused concentration in the room. What these people were engaged in was the kind of detailed analysis – and openness to constructive criticism – that would determine whether merely good animation would become great. Mark bore down on ten frames in which Queen Elinor, the mom character who has turned into a bear, walks on stones while traversing a creek. “She looks like she’s stepping more catlike than heavy-bear-like,” he said. “I like the overall speed, but I’m no feeling the weight. She’s walking like a ninja.” Everybody nodded and – note taken – they moved on.

Relentless prosecution of Acceptance Criteria, incremental improvement and utterly complete shippable units of work are the obsession of the Pixar crew.

Dailies are a master class in how to see and think more expansively, and their impact can be felt throughout the building. “Some people show their scenes to get critique from others, others come to watch and see what kind of notes are being given – to learn from their peers and from me – my style, what I like and dislike,” Mark told me. “The dailies keep everyone in top form. It’s an intimidating room to be in because the goal is to create the best animation possible. We go through every single frame with a fine-toothed comb, over and over and over again. Sometimes there are full-on debates because, truly, I don’t have all the answers. We work it out together.”

Team Collaborating

I give this glimpse into a dailies session because sharing and analyzing a team’s ongoing work every morning is, by definition, a group effort – but it does not come naturally. People join us with a set of expectations about what they think is important. They want to please, impress and show their worth. They really don’t want to embarrass themselves by showing incomplete work or ill-conceived ideas, and they don’t want to say something dumb in front of the director. The first step is to teach them that everyone at Pixar shows incomplete work, and everyone is free to make suggestions. When they realize this, the embarrassment goes away – and when the embarrassment goes away, people become creative.

Deep and transparent inspection is a bedrock of Pixar’s creative process. Openness, lack of personal agenda and continual benefit to the organization take time to develop but once the team has organized, its self-direction is powerful.

By making the struggles to solve the problems safe to discuss, then everyone learns from – and inspires – one another. The whole activity becomes socially rewarding and productive. To participate fully each morning requires empathy, clarity, generosity and the ability to listen. Dailies are designed to promote everyone’s ability to be open to others, in the recognition that individual creativity is magnified by the people around you. The result: We see more clearly.

I don’t think we can argue with the outcome.