Sorting by Column functionality in a deployed report?

Question

Sorting by Column functionality in a deployed report?

Sarah M Young

SSC Journeyman

Points: 85
More actions
May 18, 2006 at 11:06 am

#112914

I have a sales/performance report deployed to our management team. They access the report via a web interface that contains/passes my parameters. It's really a lovely thing.
HOWEVER, they would like to be able to do sorts (ascending/descending) based on values within the columns.
Example: We have 100 sales representatives. In the column for "Sales Conversion" there are percentage values from 0-100%. The grouping on the row level is currently by Sales Agent Name(we pass parameters for the group by as well)...and I want it to stay that way. But perhaps a manager wants to click on the Sales Conversion column and see the agents by their conversion rate descending....ie: take a peek at our best sales folks.
Does anyone have ideas on this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

satz SSC Veteran Points: 231 More actions · Answer 1

I am just starting to work of 05 so i will just suggest a dirty way to do this.

field headers are hyperlinks with full report link used + a new queryparameter .so when the user clicks the fieldname for sorting ,it loads the same report with an additional query parameter.

In data side create this query parameter and order by it , if this value is not null.

As i said its dirty but it will work.

satz

Syed Asim Rizvi Ten Centuries Points: 1162 More actions · Answer 2

If you are using SSRS 2000, there is no functionality, but there is a way around that works great!

Here is what you need to do:

Create a internal report parameter with values like 11 and 12 for your Sales Conversion column (11 for Ascending and 12 for Descending)

Than set Navigation property of Sales Conversion column and in "Jump to Report" select your report (the same report you are working on) and set this sort parameter clicking "Parameters" button like this:

"=IIF(Parameters!param_Sort.Value=11,12,11)" (This will take care of Ascending and Descending) (Also set your other parameters here so that everything remains same except sorting)

Than in your Table properties, enter these 2 entries in Sorting properties:

Expression: "=IIF(Parameters!param_Sort.Value=11, Fields!First_Name.Value, Nothing)" - Direction: "Ascending"

Expression: "=IIF(Parameters!param_Sort.Value=12, Fields!First_Name.Value, Nothing)" - Direction: "Descending"

Thats it!

If its SSRS 2005, it is just a matter of set column property.

Good luck!

Sarah M Young SSC Journeyman Points: 85 More actions · Answer 3

Thanks you guys! We are in the process of converting to 2005, so perhaps I should way until that is rolled out to make my modification.

I like that last work around, but I'm concerned about how that will work for printing purposes since you're bsically jumping to another report. (the same, but still, a new frame I think?)

Thanks again!

Sarah M Young SSC Journeyman Points: 85 More actions · Answer 4

PS: Since you two seem to know what you're doing, feel free to trot on over to my post on Analysis services...I have a cube question over there....

Syed Asim Rizvi Ten Centuries Points: 1162 More actions · Answer 5

The report will remain the same and in same window, only thing that will change is sorting.

It wouldn't effect printing at all, whatever you see on screen is what gonna print or export.