SQL 2005 Reporting: How to create dynamic sorting and grouping dropdowns on report viewer


in SQL 2005 Business Intelligence Studio I have been able to create and deploy an RDL report.  I see that the report viewer has some nice default features on top like like Export and refresh.  How can I get the reportviewer to also allow my to dyamically (from the top toolbar) select sort columns or group columnns?  
Who is Participating?
QPRConnect With a Mentor Commented:
I have problems with the query builder too sometimes, especially if using temop tables or similar. I find that if I click next then it is usually ok.
My steps here are using VS 2003 with the RS add in so apologies if they don't match your environment completely....

open the report wizard and select the datasource, on the next screen type
exec mystoredprocedurename @myParam (replacing the names to suit)
click through all the steps to finish.
With the report in design view, from the menu bar choose: report -> report parameters.
select your parameter and in the box that says "non-queried" add labels and values.
Labels being what the the user sees in the drop down and values being what are passed.
e.g. First Name - 1stname (where 1stName is the column in the db table)
Include them as variables in your query source.
The user can then use these to decide sorting and/or grouping.
From memory you can not use variables to order/group by in a plain select so you would need to create a sql string then execute that.

create procedure showmydata
@sortby varchar(100)
@groupby varchar(100)
declare @mySQL varchar(1000)
set @mySQL = 'select stuff from mytable order by ' & @sortby & ' group by ' & @groupby'
vamail2Author Commented:

If I use a stored proc with varibales - does that mean ReprotViewer will detect that anc create the UI drop downs for me??
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Yes. Bring the report back into designer view and redo the datasource bit.
It will pick up the input parameters and give you the opportunity to decide how to present and use them.
In your case I am assuming you would want to offer the user a list of hardcoded choices (like a web drown box) that they can sort by?
Sort order
First Name


Well you can type these values in and they will be presented to the user as drop down options.
vamail2Author Commented:
1. The report query builder did not like Execute ShowStatsData at all.

 2. Can you ellaborate on the statement how I can offer these hardcoded choices (I'd prefer of the report viewer handled them itself.)
Well you can type these values in and they will be presented to the user as drop down options.
vamail2Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.