Interactive Sorting in SQL Server 2008 Reporting Services Reports

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

Often users want the ability to sort the data in various ways. They have gotten used to tools like Microsoft Excel that let you sort on column headers. Fortunately this is a fairly simple ability to implement in SQL Server Reporting Services. Let’s start by creating a base report. (Note this is the same basic report we’ve used in other posts.)

Step 1. Add the report

As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 2. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 3. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]

and click next.

Step 4. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 5. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.

Step 6. Select the formatting Style

Once again we’ll go with Corporate for the style and click Next.

Step 7. Name the report.

Finally we’ll give the report a name of “Regional Sales by Subcategory” and click Finish.

Step 8. Format report columns

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

Step 9. Apply sorting to the column headers.

Here’s where the magic happens. Right click on the first column of the report header, which should be Fiscal Year, and pick Text Box Properties.

clip_image002

In the Text Box Properties dialog, first navigate to the Interactive Sorting area. Next, check on “Enable interactive sorting on this text box”.

Under “Choose what to sort” we’ll take the default of details. If we wanted to sort our groups, we could have picked the Groups option then picked the name of a group.

Next in the “Sort By” pick the FiscalYear field. Your dialog should now look like:

clip_image004

Click OK to save.

Repeat this step for the other columns, selecting the data associated with that column in the “Sort by” area.

Step 10. Preview your work

You should now be able to switch to the Preview tab. At the top right of each column you will now see a set of tiny up/down arrows, which when clicked will cause the report to sort by that column.

image

About these ads

6 Responses to “Interactive Sorting in SQL Server 2008 Reporting Services Reports”

  1. qwerty Says:

    great!! helped me a lot

  2. John Says:

    Works like a dream, thanks

  3. aoe Says:

    woot woot … thanks for this.

  4. Alice Says:

    gracias!

  5. Jack Says:

    Beautifully explained! However, I have a “but”. I want to sort a couple of fields by date, and this is doing interactive sorting on a text box. So 01/05 comes before 03/04. Is there a way of telling it to sort dates into order?

  6. arnaudref Says:

    Thanks, but how to do this with option Visibility to hide?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 100 other followers

%d bloggers like this: