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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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??
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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.
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vamail2Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.