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!

adding All option to a sorted list

Hi.
My DB holds a table with a varchar column containg numbers.
In RS I need to create a dataset that will sort these numbers and add 'All'
value to this dataset.
My problems is:
As the dataset holds sting values and it performs lexicographic sort, if I
perform cast to int on these numbers (to sort them correctly) I can't add the
'All' value.
Furthermore, Does anyone knows how to refer to a custom made dataset defined
in the RS?
Thanks,
GuyTry something like
select right('0000' + myvarcharfield,4) from table order by 1
union
select 'All'
for a set of numbers not longer than 4 characters... This will sort them
correctly I think without converting them to ints
--
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
"GuyR" <GuyR@.discussions.microsoft.com> wrote in message
news:56FE3261-1DEF-48DD-B4B3-07F2F992AC4A@.microsoft.com...
> Hi.
> My DB holds a table with a varchar column containg numbers.
> In RS I need to create a dataset that will sort these numbers and add
> 'All'
> value to this dataset.
> My problems is:
> As the dataset holds sting values and it performs lexicographic sort, if I
> perform cast to int on these numbers (to sort them correctly) I can't add
> the
> 'All' value.
> Furthermore, Does anyone knows how to refer to a custom made dataset
> defined
> in the RS?
> Thanks,
> Guy
>