Some Thoughts About SQL Server Reporting Services #SSRS #MSBI

SQL Server Report Services is a great product for creating reports.  What I like about it, the IDE, the tight integration with Team Foundation Server, the ability to customize every feature and having multiple data sources within a single report.

It has a web server to distribute reports via the web and schedules and you can add them to on-Premise SharePoint.

I've used 2005, 2008 and 2012.  It's got a lot of great features.

What are some of my frustrations?

The formula dialogue box is sometimes difficult to navigate.  The field are available to choose from at the bottom, the font could be adjustable for easier viewing.

I'm not aware of a formula editor to validate the syntax.  When incorrect, it displays a red underline under the entire formula.

It would be great if the exact error was displayed and perhaps a recommendation on how to fix it.  Sometimes the IIF statements get quite nested and trying to determine where the error is takes longer than it should.

And while the dialogue is open, it would be nice to switch to another formula expression without having to close and reopen.

And perhaps a button "Validate Formula" to test it instead of closing out, running the report to see if it works.

Everything is customizable.  Some things are found when pressing F4 in the properties Window.  Others appear in a dialogue box.  There are so many customizable features, it's not always known where to change a setting.

The Group Headers and Footers and Details along the left hand side are not easily identifiable.  Especially when adding many rows to a Group Header.  Perhaps a label or hover over feature.

I've seen in other reporting tools the ability to export the entire report contents to a text document.  This documents the report, as well as helps to search through the doc to identify where a particular formula exists.  I like the ability of SSRS to view the source code, but beware, one slight change and the report breaks and sometimes unrecoverable.

I did not see any easy way to add back a Group Header of Footer easily, other than to remove the Tablix and recreate it.  I posted my solutions here:  http://www.bloomconsultingbi.com/2015/07/ssrs-add-group-header-back-to-tablix.html

I like SSRS ability to call another report from within a report and adding hyperlinks to spawn new reports and actually writing custom JavaScript code to open a new window.  I like the ability to add multiple data source within a single report as well as Share Data Sources.

When adding a field to a Group, it typically requires a "First" or "Sum".  And then you have to specify the Group name within quotes to let it know which group to grab the data from in regards to totaling.  This feature requires additional keystrokes which slows down the development and if you cut and paste a formula you have to debug it.

One of the best features about SSRS is the ability to export to Excel.  I've been using Crystal Reports since version 5 in 1996 and there was always an issue export to Excel.  SSRS has a tight integration with Excel.  Great feature.

I like the way the parameters are laid out.  Pulling data from Data Sources, specifying the default value, can all be done in code.

When adding fields to a report, it typically displays the fields in the order they appear in the query.  Having an ability to sort the list would be nice, as sometimes there's a hundred fields or more to hunt and peck through, taking time.

Overall the product is awesome.  Since I've been writing reports non stop this week, I just wanted to blog about some of the things I've noticed.

And there you have it~!