SQL Server Reporting Services: How to add Interactive Sorting to a Table with no groups?

Standard

Sorting is one of the common requirements in a SSRS report that business users ask for, even if they don’t ask – it’s great to have interactive sorting enabled at places where it makes sense. If you’ve a Table with no groups, here’s how you can enable interactive sorting on the table:

1. Right Click on the Column Header:

ssrs interactive sorting column reporting

2. Click on “Text Properties” and navigate to “Interactive sorting”.

3. Check the box “Enable interactive sorting on this text box“. Also since there are no groups, we want to sort “detail rows“. Also, you’ll need to specify the sort by column. For demo, I picked Sales_Amount but you could also have an expression here if you wanted:

Interactive Sorting Text Properties Detail rows reporting4. Click on OK. And run the report – you should see the interactive sorting buttons on the Sales Amount column. Users should be able to sort the data in ascending/descending order using this feature:

Interactive SSRS sorting enabledThat’s about it for this post. I hope this helps!

Advertisements

SSRS: Why I prefer shared data source over embedded data source?

Standard

In one word. Manageability.

Let me explain. In SSRS, reports need data sources. And data sources can be of two types: 1) Embedded 2) Shared. When a report uses the embedded data source, the data source properties are stored along with the report. If you’ve 5 reports that use the same data source & you decide to go via the embedded data source route then you’ll have 5 data source properties. If you need to change the data source property like change the server name then you’ll have to edit 5 different data source properties. Not efficient!

But if you use the Shared Data Source, then you can update data source properties from one location. Multiple reports can link to the shared data source & if there is a need to change the data source property, then you’ll have to do it only once.

Other benefit, you can take a data source offline if you ever have that need. This will pause all reports that are using this data source & also, stop all report subscriptions.

So, where do you configure embedded/shared data source? When you “Add a data source” to a report, you have the option to choose the data source type:

1.

SSRS Add Data Source2.

SSRS embedded vs shared connection

conclusion:

In this blog post, we saw how shared data source can help you manage your reports efficiently.