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!