Adding Parameters to the ORDER BY Clause

What I'm wanting to do is create a report which allows you to sort by a
few different columns, and also choose if you want it sorted ASC or
DESC.
Basically, I'll have two parameters...
1) OrderBy (a number of different columns)
2) OrderDirection (ASC or DESC)
and I'll want to do a
SELECT -
FROM -
WHERE -
ORDER BY @.OrderBy @.OrderDirection
Obviously it doesn't work like this, or I wouldn't be asking for help!
I know you can use things like "Parameters!OrderBy.Value" but I was
unable to get this to work. Would somebody mind helping out?
Thanks!
-ScottI have always done this from within the RDL file during creation. It is in
one of the property dialog boxes, you may have to choose and advanced button
somewhere...It will be within the group header properties I think...
--
--Eric Cathell, MCSA
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||SELECT dbo.DatabaseName.*
FROM dbo.DatabaseName
WHERE (FieldName LIKE @.FieldName)
ORDER BY FieldName
Note: @.FieldName is "assigned" from the Report...Report Parameters menu bar
--
Message posted via http://www.sqlmonster.com|||I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||When using filters on a table/matrix group there's a ASC or DESC. But, can
you make that an expression? When I select it, there's no option to make it
an expression.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eeNwO3pEFHA.1188@.tk2msftngp13.phx.gbl...
> I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
> hope this helps
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <ScottWMcCarter@.gmail.com> wrote in message
> news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> > What I'm wanting to do is create a report which allows you to sort by a
> > few different columns, and also choose if you want it sorted ASC or
> > DESC.
> >
> > Basically, I'll have two parameters...
> >
> > 1) OrderBy (a number of different columns)
> > 2) OrderDirection (ASC or DESC)
> >
> > and I'll want to do a
> > SELECT -
> > FROM -
> > WHERE -
> > ORDER BY @.OrderBy @.OrderDirection
> >
> > Obviously it doesn't work like this, or I wouldn't be asking for help!
> > I know you can use things like "Parameters!OrderBy.Value" but I was
> > unable to get this to work. Would somebody mind helping out?
> >
> > Thanks!
> >
> > -Scott
> >
>|||I'm not sure if my original question has been answered - In the SQL
query, how am I going to add my parameters so that you can dynamically
change if it is being sorted by ASC or DESC? Thanks for all the help
so far, you guys are great!|||You can certainly do that in the query by using a dynamic query string
="SELECT ... " & IIF(Fields!Order.Value="Asc", "ASC", "DESC"). However, is
there any reason why you feel you need to do it in the query and not in the
report engine (post query)?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<ScottWMcCarter@.gmail.com> wrote in message
news:1108412814.884740.156270@.g14g2000cwa.googlegroups.com...
> I'm not sure if my original question has been answered - In the SQL
> query, how am I going to add my parameters so that you can dynamically
> change if it is being sorted by ASC or DESC? Thanks for all the help
> so far, you guys are great!
>|||No, I just don't know how to do it in the report engine!! That is what
I'm looking for. Thanks!