Using Actions Within SSRS Maps

In my monthly SSRS 101 class (http://sharepoint.rackspace.com/ssrs-training) one of the topics we cover is using the map in report builder to show sales concentrations in the United States. In the example we use, our final report looks similar to the image below:

clip_image002

In the class we use “Actions” to allow the user to click a state name in the table and navigate to a different report that shows state specific sales information. I’ve had multiple students ask if it’s possible to enable the same type of navigation within the map itself. So many people have asked actually, that I thought I’d do a quick write up on how you can implement it.

Implementing the actions within the map component is basically the same as implementing action within the Tablix. We still use the “Action” option, and choose which report we would like to link to, and then pass in the appropriate parameters. The only difference with the map is that the correct action settings are somewhat hidden and a little difficult to find if you haven’t worked with the map much. To implement this functionality in the map you’ll need to do the following:

  1. Open your report in Report Builder or BIDS/SSDT
  2. Click twice on the map object until you get the “Map Layers” window
  3. In the “Map Layers” window click the dropdown triangle to the right of the “creepy eye”
  4. From the available options select “Polygon Properties…”CreepyEye2
  5. From here you can select the “Action” option and start to link your report as you normally would. In our case we’re linking to a report that is currently deployed to SharePoint and accepts parameters for the StateProvinceName, EnglishProductCategory, and EnglishProductSubCategory.

clip_image005

That really all there is to it. If we run the report now, we should be able to click on individual states within the map and be redirected to our “detail” report (which we would have to build separately). In the example report you saw earlier in the post, if we click on California in the map we’ll be redirected to a report that looks similar to the following:

clip_image007

That’s it for this post, hopefully you found it useful, and as always….happy reporting!

Introduction To Indicators With Reporting Services

In this walkthrough, we’ll take look at using the Indicator visualization in SSRS. Indicators are a great tool to have at your disposal, particularly if you’re creating “dashboard” reports. The general idea of a dashboard is to give the user a high level overview of how areas of interest area performing, and indicators are a really great visual aid to help users quickly identify the current performance of a metric. Let’s take a look at setting up a simple indicator in SSRS report builder. (This walkthrough does assume some general knowledge of SSRS and how to create a data source and data set)

  1. Open Report Builder, and create a new blank report
  2. Create a data source connected to a SQL Server data source (any database will do, we’ll actually be hardcoding our data in the data set)
  3. Create a data set in report builder and paste in the following SQL into the query window:

    SELECT 'Category A' 'Category', 33 'Value' UNION SELECT 'Category B', 66 UNION SELECT 'Category C', 99 UNION SELECT 'Category D', 20

  4. Add a table to the body of your report
  5. Drag “Category” and “Value” from the dataset you created in step 3 to the first two columns of your new table. If you run/preview your report at this point, your table should look like the image below

    clip_image001

  6. Back in the Report Builder design view, navigate to the insert tab, and select the Indicator Visualization

    clip_image003

  7. Place the Indicator into the 3rd column of your table. This should bring up a new window that allows you to choose from a variety of different visualizations. In this example we’re going to use the classic red, yellow, green circles from the “Shapes” category. Make this selection (shown below) and then click “OK”

    clip_image005

  8. Left click twice into the table cell that contains the Indicator to select it. The “Gauge Data” window should appear once it’s been selected
  9. Click the “Gauge Properties” button on the “Gauge Data” window.

    clip_image006

  10. Click on the “Values and States” menu option
  11. In the dropdown labeled “Value” select [Sum(Value)] from the available options
  12. In the dropdown labeled “States Measurement Unit” select “Numeric” from the available options

    clip_image008

  13. Click “OK”
  14. Preview the report.

At this point your table should look like this:

clip_image010

Note that if we look back to our “Indicator Properties” screen and compare the “Start” and “End” values (often called thresholds) for each differently colored indicator, we can see that the red indicator has an end value of 33. Since “Category A” falls into the range of 0-33 it’s marked with a red indicator in our table. One important thing to note about the list of start/end values for each indicator listed on the “Indicator Properties” screen is that they’re evaluated in order from top to bottom, so the order that they appear in is important. If there happen to be ranges that overlap, SSRS will only look as far as the first matching range and then look no further (sort of similar to a switch statement). To further illustrate this point, we could set the “Start” value to zero for all three indicator colors in the properties window and our table would look exactly the same when the report is run. The order of the indicators can be easily modified by simply using the up and down arrows found on the “Indicator Properties” screen.

The “Indicator Properties” screen also allows you to modify just about everything else you might want to modify about your indicator states, such as the color and shape that is presented. If you’re feeling really feisty, you can even add additional indicator states by simply clicking the add button. Who knows, maybe a blue sad face emoticon will be a really useful range/state to add. While you can go kind of crazy with the colors and shapes if you want, make sure to keep the overall goal of your report in mind. Indicators are really good at enabling users to quickly identify good/mediocre/poor performing areas “at a glance”, but you need to keep the number of states low. Also red/yellow/green are pretty much universally accepted as bad/ok/good. Creating an indicator that has 20 states and every color under the rainbow will probably be pretty useless to the report user.

I think that about wraps up our intro to indicators, hopefully you found it useful. Until next time, happy reporting!

Formatting a Map Legend to Currency (Or Whatever) in SSRS

In this quick example we’re going to take a look at how you can format the numbers in a map legend to be currency, or whatever you want. By default the numbers appearing in a map legend are formatted to numeric with zero decimal places. Often times you might want this to be currency, or perhaps even percent instead. For whatever reason, several of the options with the map are fairly well hidden and a little difficult to find, particularly for people new to SSRS. Follow the steps below to achieve legend formatting nirvana:

  1. Open your report in Report Builder or BIDS/SSDT
  2. Click twice on the map object until you get the “Map Layers” window
  3. In the “Map Layers” window click the dropdown triangle to the right of the “creepy eye”
    CreepyEye
  4. From the available options select “Polygon Color Rule…”
  5. Select “Legend” from the menu options on the left
  6. In the textbox labeled “Legend Text” you should see something similar to the following “#FROMVALUE{N0} - #TOVALUE{N0}”. This is what SSRS uses to format the legend. The “N0” portion of this is telling Reporting Services to format the legend as Numerical with zero decimal places. To have it format as currency, simply change the N0 to C0 like so “#FROMVALUE{C0} - #TOVALUE{C0}”. Note that you need to change the N to a C in both the FROMVALUE and the TOVALUE portions.

    clip_image002

  7. Click OK

That should do the trick! Now if you run the report you should see the dollar sign in your legend.

clip_image004

If you want to format the legend as something other than dollars, there’s a pretty easy way to figure out what you should use to replace the N0 value. The easiest way is probably to place a textbox on the report, manually type some numbers in it and format it however you desire using the textbox properties window (available via right clicking on the textbox). Once the textbox is formatted to your liking, you can open the properties window (the full report properties window, available under the view tab in Report Builder) and then looking at the Number Format script that was generated. You’ll want to make sure you have the textbox you just created selected when you’re looking through the properties, as the properties window is context sensitive. In the example below you can see I’ve formatted a textbox to show pounds instead of dollars.

clip_image005

In this example I would replace N0 with '£'0 to achieve my desired formatting. Now go forth and format map legends to thine hearts content! Happy reporting!

Adding an Expandable Report Details Window In SSRS

One of the most challenging things report writers face is getting all the elements that users want to actually fit on a report. I know I’m constantly battling for more space by trying to make other things smaller. Since report real-estate is somewhat precious, having a “Report Details” section that tells the user about the report they’re looking at often falls by the wayside. A report Details section could contain things like who actually owns the report, details about the calculations used in the report, and who they can contact if they have questions. One way to add this information to the report without having to take up a bunch of space is to put it in a collapsible window within the report, and have it hidden by default. Below we’ll walk through the steps of how we might accomplish this.

  1. Open your report and add a textbox to your report
  2. Type in the text “Report Details” into the new textbox
  3. Note the name of the textbox that you just added….you’re going to need it later. You can find the name of the textbox by clicking on it and then finding the name property in the properties window, or right clicking on the textbox and then going to “Text Box Properties…” in my case the textbox is named “Textbox3”

    clip_image002

  4. Add a “Rectangle” next to your textbox
  5. Add as many textboxes inside of your rectangle as you want and type in whatever information you deem important for the users to know. In my quick example I’ve mocked up the following information:

    clip_image004

  6. Now that you’ve got your info added, right click on the rectangle object you added and select “Rectangle Properties” from the dropdown
  7. Select “Visibility” from the options on the left
  8. In the section labeled “ When the report is initially run” select “Hide”.
  9. Place a check in the box labeled “Display can be toggled by this report item”
  10. In the dropdown below the checkbox select the name of the textbox that you typed “Report Details” in. In my case it was “Textbox3”

    clip_image006

  11. Click “OK”
  12. Run the report

The report should run and automatically hide the rectangle and everything that was inside of it. You should also see a “+” next to the “Report Details” textbox that will allow you to unhide the rectangle if you click on it. The finished example should look similar to the following when initially run

clip_image008

And then once expanded:

clip_image010

You would probably want to add some additional formatting like perhaps a border around the rectangle or maybe even change the background, but I’ll leave the formatting choices up to you. This information can be invaluable to users viewing the report, particularly if the report involves complex calculations. After all, a report that is being misinterpreted can be even worse than having no report at all, and adding a details section to your report can go a long way in helping to clarify what it being shown. Happy reporting!

Enabling Interactive Sort in SSRS Reports

When using any reporting tool, I’m always on the lookout for features that add value/functionality, and are quick/easy to implement. Interactive sorting within SSRS is one of those things that’s so quick and easy to implement at a basic level, that I usually add it to reports without it even being request because it will literally only takes seconds, and I’ve never had anyone come back and say “Hey, we don’t want the ability to sort the data in our report!” With that in mind, let’s take a quick look at how we can implement interactive sorting on a table within SSRS. (This walkthrough does assume some general knowledge of SSRS and how to create a data source and data set)

  1. Open Report Builder, and create a new blank report
  2. Create a data source connected to a SQL Server data source (any database will do, we’ll actually be hardcoding our data in the data set)
  3. Create a data set in report builder and paste in the following SQL into the query window:

    SELECT 'Product A' 'Product', 3000 'SalesAmount' UNION SELECT 'Product B', 10000 UNION SELECT 'Product C', 5000 UNION SELECT 'Product D', 1000 UNION SELECT 'Product E', 7000 UNION SELECT 'Product F', 2000

  4. Add a table to the body of your report
  5. Drag “Product” and “SalesAmount” from the dataset you created in step 3 to the first two columns of your new table. If you run/preview your report at this point, your table should look like the image below

    clip_image001

  6. Back in the Report Builder design view, on the header row of the table, right click the cell containing the “Sales Amount” title, and then select “Text Box Properties”

    clip_image003

  7. On the left hand side of the “Text Box Properties” window select “Interactive Sorting”
  8. Place a checkmark in the checkbox labeled “Enable interactive sorting on this text box”
  9. In the dropdown box labeled “Sort by” select “[SalesAmount]”

    clip_image005

  10. Click “OK”
  11. Preview your report

If all has gone well, you should now see two up/down triangles in the Sales Amount header as shown below highlighted in yellow.

clip_image006

While the graphic actually looks like two different arrows that you can click, it’s actually just one button that will order the column you configured from low to high (or A-Z) on the first click, and then subsequent clicks will reverse the current order. While having this functionality might not be all that useful when there’s only 6 rows of data, it can be fairly useful to the report user when dealing with a small/medium result set of a few hundred or thousand rows. Note that you do want to be mindful of picking the correct column to sort when you’re on the “Textbox Properties” screen, it would be fairly easy to pick a different column and end up with a sort button on “Sales Amount” that actually sorts the “Product” instead. While this functionality is very easy to add, it does require some additional space lengthwise for the header cell you place these in, so if you’ve got a table that is a tight fit width wise that might be a concern. I will also say that I typically only put these on columns where it makes sense that a user might want to sort. For instance sales amount is a pretty good candidate for sorting, but if I had a table with customer contact info in it, I probably wouldn’t put a sort button on the phone number column, as I doubt anyone would ever want to sort on the phone number. It’s also a good Idea to educate the report viewer on use of sorting as well. It’s my experience that some users will sort a table to try and locate something alphabetically because they’re unaware that SSRS has a “Find” function they can use (The find textbox is literally at the top of every report).

If you look back to “Textbox Properties” screen you’ll also notice that the interactive sorting can be applied to groups within the table as well, along with some additional options I’ll leave you to play with on your own. Until next time, Happy reporting!

Reporting Services (SSRS) Tutorial Video….AND ONLINE CLASS!

If you’re looking to ramp up on Reporting Services, I recently made a tutorial video that you might find interesting.  While the video Focuses on building reports with SQL Server Data Tools (Visual Studio), I’m also teaching a monthly 4 hour online “Reporting Services 101” class! The class focuses more on Report Builder and SharePoint:  In that class we take it step by step….starting at step one to give you a good foundation, and by the end we’re cranking out some pretty robust reports.  

You can check out the details for the class here: http://sharepoint.rackspace.com/ssrs-training

You can check out the short tutorial video of building a report using SQL Server Data Tools (Visual Studio) below. 

Reporting Services using SSDT