This is the second post in a series about SQL Server Reporting Services (SSRS). I will be picking up where we left off in the project that was created in Part 1.

In this post we will be taking our list of Sales People and seeing how we can group and sort them in order to make the report more useful. We will also jump into expressions in order to add conditional formatting. The goal is to group the sales people by Country, show the YTD and last year’s sales totals and add color formatting to quickly show how this year’s sales compare to last year.

Sorting

Let’s refresh, this is what our report looks like now.

Report View

It was a good start, but the data is not displayed in a very useful way. To improve the readability of this report we could group the data by country.

The first step is to not display the Sales Managers and pull in the missing fields. To hide the Managers we can simply add a WHERE clause to our query in the SalesPersonData Dataset to only pull data WHERE TerritoryName IS NOT NULL. The SalesLastYear and CountryRegionNames are already being pulled in so we just need to add a new column for Sales Last Year (don’t forget to add the Currency formatting) and replace the Territory Name with Country (We will bring the Territory back in future posts for more detailed grouping). If you preview and the Managers still show up, hit the Refresh button in the menu bar (highlighted below).

Report Preview

The simplest way we can view the data by Country is to sort it. Right click on the grey box around the tablix and select Tablix Properties. In the navigation go to Sorting. Right now the data is relying on SQL Server’s default sorting. Click Add and in the Sort by dropdown select [CountryRegionName]. You also have the option to change the order to descending. You can add more to sort the data within a sort – the top line is always the top level sort. Use the up and down arrows to rearrange them.

Tablix Sorting Properties

Now our report is being sorted by [CountryRegionName] and [LastName].

Sorted Report

Grouping

While the sorting does help with seeing how the data is structured, it isn’t quite what we wanted. One way we can layout the data is with Country name as a group header and the related Sales Reps listed underneath. Similar to the quick outline below:

Country Name
First Name | Last Name | Email | Sales YTD | Sales Last Year
First Name | Last Name | Email | Sales YTD | Sales Last Year

Country Name
First Name | Last Name | Email | Sales YTD | Sales Last Year

To get this layout head down to the Row Groups section under the Report Design stage.

Report Design Stage

Right now you see one row in there called (Details) and the same three lines that is next to the details row in the report. Clicking on the drop down on this row you see the options to add a group, delete this group, add totals and view this group’s properties (even a detail row is treated as group).

For this report we want to group our details by country so select Add Group > Parent Group. In the Group by dropdown select [CountryRegionName]. Also select the option for Add group header. You have the option to include a group footer which can be used for totals or additional group specific information. The group header and footer will each repeat once per group.

Tablix Grouping

After hitting OK you will see that the report has changed a bit. There is a new initial column for the Country and an additional row (the group header). In the tablix box you will also see that there is now a bracket encasing the new row and the detail row. With the detail row inside this bracket, the row will repeat for every item within the group. The row above it does not have an extra icon in the tablix box so it will only repeat once in each group.

Report Grouping

Here is the report preview so you can compare and see how the rows are displayed based on their location and tablix icon.

Report Preview

For the first bit of formatting, I want to merge all of the table cells in the row that contains the country group name (far left column). Select the cell with the group name, shift-click the columns you want to merge with and open the right mouse click menu. There should be an option that says Merge Cells, but it isn’t (test it out on the empty cells below the First Name and Last Name header to verify that it exists). The reason is because of the thicker right border on the country grouping column – you can’t merge cells across these groups. Don’t worry though, we can get around it.

First, go ahead and delete the column with the column grouping display (you should be fine to do this, but if you get a pop up asking whether to delete the columns and the group or just the columns, select delete columns only).

Next, add a column to the left of the First Name. In the middle row add the CountryRegionName and then try the steps mentioned earlier to merge the columns in this row. (Note some subtle formatting changes in the below screenshot: bold country group name, removed Country Region Name header, removed Country column)

Report Grouping

Group Totals

We are making good progress here. Next we need to add some totals – by country and overall.
First, an overall total. In the Row Groups right-click on the CountryRegionName group and expand the Add Totals menu. Since we are at the CountryRegionName group level it will add a total (or sum) of all of the groups within the level. You can add totals before or after the group – let’s go with after.

SSRS adds another row (notice it is outside of the grouping). It even recognizes when there are columns that have fields that can be summed and includes the field values for you. This is nice when working with a simple report but you will need to write the sum expression for the textbox yourself when doing more complicated calculations.

Grouping Totals

Now that you have added this one, try to add the totals that will show up for each country group (hint: after each group’s details). In the Design view take a note of where within the group this new row is placed.

Design View

(Right-click the Details in the Row Groups and Add Total > After)

Let’s add a couple more formatting changes before moving on.

  • Add Group Total to the first column of the group sums
  • Add Grand Total to the first column of the overall sum
  • Add an empty row under the Group Total inside the group that will add space between countries
  • Bold text in the Total rows

Report Formatting

Conditional Formatting Through Expressions

The last thing we are going to look at is changing the font color.
Right-click on the cell for SalesYTD and select Text Box Properties from the menu. This dialog form has many options to format the selected text box. We are interested in the Font section where we can change the font styling (font, size, color, etc). Everything has the option for you to add a custom expression (fx). Select the expression button next to Color.

There could be multiple posts on writing expressions but right now I’ll give you some tips to get us started.

  • Expressions should start with an equal sign (=) unless it is a SSRS keyword (eg. where we left off you should see only the word Black with no equal sign)
  • To access the values for a field use Field!FieldName.Value (Remember the .Value or you will get errors when running your report)
  • The Category section in the bottom left has some useful functions that are available and short descriptions on parameters and overloads. You can also read through the documentation (https://msdn.microsoft.com/en-us/library/ms157328.aspx).

The function we will be working with is the condition function, IIF (In the Category menu you can find it under Common Functions > Program Flow). The function takes three parameters: an expression that calculates to true or false, the object to return if it is true and the object to return if it is false

(IIF(<expression>, <do this if true>, <do this if false>).

Keeping these things in mind, our goal is to change the text green if the YTD Sales are greater than Last Year and red if it is lower. So our function will look like this:

= IIF(Fields!SalesYTD.Value > Fields!SalesLastYear.Value, "Green", "Red")

We are returning the color as part of a function so they have to be marked as strings within quotes. To apply this formatting to the Sum fields you will also need to update the function to compare the summed fields:

= IIF(Sum(Fields!SalesYTD.Value) > Sum(Fields!SalesLastYear.Value), "Green", "Red")

Conditional Formatting

These concepts should get you on the right track to start building basic reports and will be the starting points for any more complicated reports you will need to create. I’ve tried to include all of the little gotchas I have run into while building reports.

Let me know in the comments if there are any other areas you would like to see a post on or if there is anything I have gone over at top level that you would like a more advanced look at!

Take a look at Part III: Parameters

Remember when you were learning how to type on a computer keyboard? I bet you played a game, or several, to hone your skills and learn how to type faster, am I right? Well improving your typing skills isn’t the only thing learning games are used for. Learning games are powerful tools that can be used to hone coding skills as well as develop new ones, even in younger audiences. In fact, it’s a growing trend! For the past 2 years there has been a boom in programs that promote coding education for youth, whether it be for a summer or an extracurricular activity. So it stands to reason that learning games for the subject would follow a similar trend. After all, what better way to teach youth than through a game? Now I won’t cover the entire history of gaming in education, or give an in-depth analysis on this growing trend, but I will take a brief trip down memory lane, shed some candle light over the coding trend, and go over some key teaching programs and games that have hit the interwebs. Because we all know that you’re only interested in this article for the games anyways 😉

Brief History of Learning Games

Games have been used to teach a wide range of knowledge that would otherwise be uninteresting to children throughout history. Since the history of learning games covers such a vast amount of history, I’ll only touch on some key facts that lead to the well-known medium of computer learning games.

  • In the Middle Ages, chess was used to teach noblemen the strategies of war.
  • In 1779, kindergarten was created based on the premise of integrating learning through games and play.
  • Psychologists throughout history, such as Jean Piaget, have further encouraged learning through games, connecting the development of moral judgement in children to learning to understand rules in a game.
  • With the creation of technology, blended learning came into play. A formal education program in which students learn (at least in part) through digital or online content and instruction, blended learning combines the traditional brick-and-mortar teaching method with computer-mediated activities.

Blasts From the Past

Though blended learning took off in the 1960’s, computer-based learning games didn’t really take off until the 1980’s. These games taught the fundamentals of education ranging from teaching students how to read, to basic mathematics, to geography and biology. In fact, many of the greats in educational games came from the 1980’s. The following is a brief list of blasts from the past that I’m sure many have had the privilege to play growing up and what they were meant to accomplish:

  • The Oregon Trail

    The goal of the trail was to teach about the realities of 19th century life on the Oregon Trail, from battling swollen rivers and broken axles to the ravages of dysentery.

  • Where in the World is Carmen Sandiego?

    The search of Carmen was meant to make geography fun for learners. The game was such a success that several different versions were created to cover different subjects like history and math.

  • Reader Rabbit

    This game series was a successful tool for teaching youngsters how to read and spell.

  • Number Munchers

    This game was centered on teaching math and problem solving. Another series was designed to teach basic grammar skills called Word Munchers.

  • Math Blaster

    Like Number Munchers, Math Blaster was designed to make math exercises interesting. The game series later covered a range of subjects from algebra to reading.

  • SimCity

    Problem solving skills as well as knowledge about managing day-to-day affairs such as planning, spending, and allocating resources were the goals of this widely popular game.

Taking Learning Games to the Next Level

The Trend

Over the past 2 years, various reasons have taken learning games to the next level: teaching the fundamentals of computer science and coding languages. This trend appears to have stemmed from a variety of factors, the basis of which is the need for employees in the ever-growing computer sciences fields. It is thought that creating more learning games that teach coding will entice the next generation of developers and ensure the industry’s future. Stemming off of that, there has been a lot of promotion introducing more girls to the industry. A few examples here would be Covergirl’s Girls Can campaign and Microsoft’s Girls Do Science campaign. No matter the reasoning behind the trend, there’s no arguing that learning to code is being promoted in a big way, and not only for youth but for adults as well in the form of code academies, boot camps, and coding communities.

Computer Science Jobs

Resource: Part of infographic for Code.org

The Medium

Now that we’ve covered the trend, let’s go over how learning code is being accomplished. Based on my findings, there are 3 types of gaming architectures used for teaching code:

  • Basic Children’s GamesBasic children’s games help teach not only syntax but basic computer science concepts. Some of these concepts include basic programming logic, representing information (binary numbers), algorithms, procedures, and cryptography.
  • Games with Goals in MindThere are more advanced games for adults and teens that have goals in mind. When you finish the game you not only learn the basics but you’ve also built something of your own, whether that’s an app or a website. For example, Code Avengers is a learning website that is composed of games that help teach how to code games, apps, and websites.
  • Games Centered on CommunitiesThese are typically for players with previous coding knowledge where they get to test their skills by competing against other coders. You’re given a problem in code and have to use your code knowledge of different topics and languages to solve it. I’ve seen this mostly done on an individual basis utilizing a point and rank system, but I bet there are some communities out there that have teams competing against each other.

Platforms for these 3 architectures can range from good old fashioned board games or games that use physical objects/activities, to mobile and computer apps, to gaming community websites. I haven’t found games developed specifically for a gaming console, but I wouldn’t be surprised if there weren’t a few out there.

Lists of Examples

And now the moment you’ve all been waiting for, it’s game time! Here are a few lists with some great finds. I found a little bit of everything, including some popular coding games, programs/bootcamps, and code “academy” websites.

Examples of Learning Games

Examples of “Code Academies”

Examples of Programs or BootCamps

Note: There are usually Code Camps and BarCamps specific to location. I’ve just listed ones for Orlando and Tampa.

Additional Resources

For those that are more interested in the topic than the games themselves, here are a few sites and articles that I came across during my exploration of learning games and their history.

Enjoy!