Wednesday, December 5, 2012

Sorting For Non Programmers With Report Builder

I have one primary user of the system I work on.  There are several user, but this one drives it and has a lot of need for various different reports.  It takes a long time to gather the requirements for each one, write a program to get it up on our web site, test, and deploy each one.

I have been looking for a way that he can gather and aggregate the data himself.  He can play with the data and turn it this way and that way with out getting a developer involved, other than training and supporting the product.

Another developer mentioned that he had heard about SQL Reporting Services (SSRS).  I looked into it and it still seemed like there would have to have a developer involved, since the tools are so complicated.

As I looked into SSRS I came across Report Builder.  It appeared as though each iteration of this product got a lot better.  I downloaded version 3.

It has a wizard for creating the report.  All of that is pretty straight forward, except two things.  I couldn't find a way to do sorting of the report through the wizard and the adding fields to the report seemed a little odd, but they said it is like a pivot table in Excel.  I don't know anything about pivot tables, but my user uses Excel a lot.

I searched and searched online for how to change the sorting and nothing was steering me into something simple for the user.  The best thing I found in my exploration was just adding an Order By clause to the SQL it generates.  That didn't seem great for a user, but I was almost willing to deal with the training for that.

Then I went through the program right clicking on things.  I found that I could pop up the properties window for the table that was the report and in there was a property called Sort Expression.  I had to right click on the grid and choose to select the grid and then I had to go to the View menu and select the properties check box.  Then that brought up the properties box.

It appears to be done in the report itself and not the SQL, because it doesn't appear to change the underlying SQL.