Order By Param

Is there a way to have a report parameter for the selection of what field
the user wants a table to be grouped on? For instance, the parameter would
have 3 options, clientid, clientname, and clientcity. I want to be able to
select one of these and have the table group on this selection.
Or any generic ways or ideas on how to accomplish this?
Thanks!!!I am looking for the same solution. I have a client that I have promised 10
reports to and they gave me a few but want sort capability on 5 different
fields. I could say that those are 5 different reports, but there should be
an easy way to list the fields in a drop down parameter and adjust the ORDER
BY based on this. Shouldn't there?
Thanks in advance.
ANDY
"Amon Borland" wrote:
> Is there a way to have a report parameter for the selection of what field
> the user wants a table to be grouped on? For instance, the parameter would
> have 3 options, clientid, clientname, and clientcity. I want to be able to
> select one of these and have the table group on this selection.
> Or any generic ways or ideas on how to accomplish this?
> Thanks!!!
>
>|||can you pass the field to sort on into the report as a parameter and use a
CASE in your query to conditionally sort based on whatever you passed in as
a parameter?
Bill
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:C80B4FC3-FA92-4ADE-BFEA-D6B0867FDE97@.microsoft.com...
> I am looking for the same solution. I have a client that I have promised
10
> reports to and they gave me a few but want sort capability on 5 different
> fields. I could say that those are 5 different reports, but there should
be
> an easy way to list the fields in a drop down parameter and adjust the
ORDER
> BY based on this. Shouldn't there?
> Thanks in advance.
> ANDY
> "Amon Borland" wrote:
> > Is there a way to have a report parameter for the selection of what
field
> > the user wants a table to be grouped on? For instance, the parameter
would
> > have 3 options, clientid, clientname, and clientcity. I want to be able
to
> > select one of these and have the table group on this selection.
> >
> > Or any generic ways or ideas on how to accomplish this?
> >
> > Thanks!!!
> >
> >
> >|||It seems all my replied are this..
Sorting is easy
DECLARE @.sql as varchar(100)
SET @.sql = 'SELECT * FROM blah WHERE blah = blah ORDER BY '
@.paramete
EXEC sp_executesql @.sq
Grouping on the other hand I don't think you can do dynamically

Order By in Strore Prod

I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody hel
p?
CREATE PROCEDURE SP_GetCorpFinalReport
(
@.SortOrder nvarchar
)
AS
SELECT * FROM CorpFinalReport Order By
CASE @.SortOrder
WHEN 'custno' THEN custno
WHEN 'gross' THEN gross
WHEN 'net' THEN net
WHEN 'npv' THEN npv
WHEN 'plimpact' THEN plimpact
--else RecNo
end DESC
GO
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...de59ba32b430c3
*****************************************
some response please!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...l-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...680a157c0dfad7
*****************************************
|||Gabe,
First of all, be sure to change the declaration of @.SortOrder, because
it only holds 1 character now nvarchar is the same as nvarchar(1).
Sysname or nvarchar(bigger-number) is ok.
Then try something like this:
create procedure yourProcedure (
@.SortOrder sysname
) as
select * from CorpFinalReport
order by
case when @.SortOrder = 'custno' then custno end desc,
case when @.SortOrder = 'gross' then gross end desc,
case when @.SortOrder = 'net' then net end desc,
case when @.SortOrder = 'npv' then npv end desc,
case when @.SortOrder = 'plimpact' then plimpact end desc,
RecNo desc
Also, it is not a good idea to name your stored procedure SP_...,
because names that begin with sp_ have owner/db resolution rules that
are designed for system stored procedures.
Steve Kass
Drew University
Gabe Wong via SQLMonster.com wrote:

>I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody he
lp?
>CREATE PROCEDURE SP_GetCorpFinalReport
>(
>@.SortOrder nvarchar
>)
>AS
>SELECT * FROM CorpFinalReport Order By
>CASE @.SortOrder
>WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
>WHEN 'npv' THEN npv
>WHEN 'plimpact' THEN plimpact
>--else RecNo
>end DESC
>GO
>*****************************************
>* This message was posted via http://www.sqlmonster.com
>*
>* Report spam or abuse by clicking the following URL:
>* http://www.sqlmonster.com/Uwe/Abuse...de59ba32b430c3
>*****************************************
>
|||You are going to have to handle that by executing a SQL string like:
DECLARE @.SQL VARCHAR(1000)
SELECT @.SQL='SELECT * FROM CorpFinalRport ORDER BY '+@.SortOrder
EXEC(@.SQL)
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c7db6820f2c6415584de59ba32b430c3@.SQLMonster.c om...
> I want to order by a table dynamicly base on user criteria. If user ckick
on gross, the table sort by gross. Below is my strore prod. The table is
displayed but when user click on gross, the data remain same, in other
words, it not sorted! Can anybody help?
> CREATE PROCEDURE SP_GetCorpFinalReport
> (
> @.SortOrder nvarchar
> )
> AS
> SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
> GO
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.sqlmonster.com/Uwe/Abuse...de59ba32b430c3
> *****************************************
|||It worl find now, but one more thing, it order by Gross value, the value order by the first char only, why? for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
is it i should write : order by max(groos)?
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...l-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...b0c07e0de1186a
*****************************************
|||What datatype is Gross of in your table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:2b1b743f2fd34951a7b0c07e0de1186a@.SQLMonster.c om...
> It worl find now, but one more thing, it order by Gross value, the value order by the first char
only, why? for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> is it i should write : order by max(groos)?
> *****************************************
> * A copy of the whole thread can be found at:
> * http://www.sqlmonster.com/Uwe/Forum...l-server/17779
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse...b0c07e0de1186a
> *****************************************
|||i CAST it to int, now it work perfectly, Thanks ALL!!! if posible come to Malaysia, i buy u dinner!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...l-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...bba0c949a38069
*****************************************
|||> some response please!
Geez, have patience. You did post well outside of business hours in the
western hemisphere...
sql

Order By in Strore Prod

I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody help?
CREATE PROCEDURE SP_GetCorpFinalReport
(
@.SortOrder nvarchar
)
AS
SELECT * FROM CorpFinalReport Order By
CASE @.SortOrder
WHEN 'custno' THEN custno
WHEN 'gross' THEN gross
WHEN 'net' THEN net
WHEN 'npv' THEN npv
WHEN 'plimpact' THEN plimpact
--else RecNo
end DESC
GO
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3
*****************************************some response please!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=d0adf5a5f71c4ebc98680a157c0dfad7
*****************************************|||Gabe,
First of all, be sure to change the declaration of @.SortOrder, because
it only holds 1 character now nvarchar is the same as nvarchar(1).
Sysname or nvarchar(bigger-number) is ok.
Then try something like this:
create procedure yourProcedure (
@.SortOrder sysname
) as
select * from CorpFinalReport
order by
case when @.SortOrder = 'custno' then custno end desc,
case when @.SortOrder = 'gross' then gross end desc,
case when @.SortOrder = 'net' then net end desc,
case when @.SortOrder = 'npv' then npv end desc,
case when @.SortOrder = 'plimpact' then plimpact end desc,
RecNo desc
Also, it is not a good idea to name your stored procedure SP_...,
because names that begin with sp_ have owner/db resolution rules that
are designed for system stored procedures.
Steve Kass
Drew University
Gabe Wong via SQLMonster.com wrote:
>I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody help?
>CREATE PROCEDURE SP_GetCorpFinalReport
>(
>@.SortOrder nvarchar
>)
>AS
>SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
>GO
>*****************************************
>* This message was posted via http://www.sqlmonster.com
>*
>* Report spam or abuse by clicking the following URL:
>* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3
>*****************************************
>|||You are going to have to handle that by executing a SQL string like:
DECLARE @.SQL VARCHAR(1000)
SELECT @.SQL='SELECT * FROM CorpFinalRport ORDER BY '+@.SortOrder
EXEC(@.SQL)
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c7db6820f2c6415584de59ba32b430c3@.SQLMonster.com...
> I want to order by a table dynamicly base on user criteria. If user ckick
on gross, the table sort by gross. Below is my strore prod. The table is
displayed but when user click on gross, the data remain same, in other
words, it not sorted! Can anybody help?
> CREATE PROCEDURE SP_GetCorpFinalReport
> (
> @.SortOrder nvarchar
> )
> AS
> SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
> GO
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3
> *****************************************|||It worl find now, but one more thing, it order by Gross value, the value order by the first char only, why? for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
is it i should write : order by max(groos)?
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=2b1b743f2fd34951a7b0c07e0de1186a
*****************************************|||What datatype is Gross of in your table?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:2b1b743f2fd34951a7b0c07e0de1186a@.SQLMonster.com...
> It worl find now, but one more thing, it order by Gross value, the value order by the first char
only, why? for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> is it i should write : order by max(groos)?
> *****************************************
> * A copy of the whole thread can be found at:
> * http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/17779
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=2b1b743f2fd34951a7b0c07e0de1186a
> *****************************************|||i CAST it to int, now it work perfectly, Thanks ALL!!! if posible come to Malaysia, i buy u dinner!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=b40370fa97644873a0bba0c949a38069
*****************************************|||> some response please!
Geez, have patience. You did post well outside of business hours in the
western hemisphere...

Order By in Strore Prod

I want to order by a table dynamicly base on user criteria. If user ckick on
gross, the table sort by gross. Below is my strore prod. The table is displ
ayed but when user click on gross, the data remain same, in other words, it
not sorted! Can anybody hel
p?
CREATE PROCEDURE SP_GetCorpFinalReport
(
@.SortOrder nvarchar
)
AS
SELECT * FROM CorpFinalReport Order By
CASE @.SortOrder
WHEN 'custno' THEN custno
WHEN 'gross' THEN gross
WHEN 'net' THEN net
WHEN 'npv' THEN npv
WHEN 'plimpact' THEN plimpact
--else RecNo
end DESC
GO
****************************************
*
* This message was posted via http://www.droptable.com
*
* Report spam or abuse by clicking the following URL:
* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3[/u
rl]
****************************************
*some response please!
****************************************
*
* A copy of the whole thread can be found at:
* http://www.droptable.com/Uwe/Forum...ql-server/17779
*
* Report spam or abuse by clicking the following URL:
* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=d0adf5a5f71c4ebc98680a157c0dfad7[/u
rl]
****************************************
*|||Gabe,
First of all, be sure to change the declaration of @.SortOrder, because
it only holds 1 character now nvarchar is the same as nvarchar(1).
Sysname or nvarchar(bigger-number) is ok.
Then try something like this:
create procedure yourProcedure (
@.SortOrder sysname
) as
select * from CorpFinalReport
order by
case when @.SortOrder = 'custno' then custno end desc,
case when @.SortOrder = 'gross' then gross end desc,
case when @.SortOrder = 'net' then net end desc,
case when @.SortOrder = 'npv' then npv end desc,
case when @.SortOrder = 'plimpact' then plimpact end desc,
RecNo desc
Also, it is not a good idea to name your stored procedure SP_...,
because names that begin with sp_ have owner/db resolution rules that
are designed for system stored procedures.
Steve Kass
Drew University
Gabe Wong via droptable.com wrote:

>I want to order by a table dynamicly base on user criteria. If user ckick on gross,
the table sort by gross. Below is my strore prod. The table is displayed but when u
ser click on gross, the data remain same, in other words, it not sorted! Can anybody
he
lp?
>CREATE PROCEDURE SP_GetCorpFinalReport
>(
>@.SortOrder nvarchar
> )
>AS
>SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
>GO
> ****************************************
*
>* This message was posted via http://www.droptable.com
>*
>* Report spam or abuse by clicking the following URL:
>* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3[/
url]
> ****************************************
*
>|||You are going to have to handle that by executing a SQL string like:
DECLARE @.SQL VARCHAR(1000)
SELECT @.SQL='SELECT * FROM CorpFinalRport ORDER BY '+@.SortOrder
EXEC(@.SQL)
"Gabe Wong via droptable.com" <forum@.droptable.com> wrote in message
news:c7db6820f2c6415584de59ba32b430c3@.SQ
droptable.com...
> I want to order by a table dynamicly base on user criteria. If user ckick
on gross, the table sort by gross. Below is my strore prod. The table is
displayed but when user click on gross, the data remain same, in other
words, it not sorted! Can anybody help?
> CREATE PROCEDURE SP_GetCorpFinalReport
> (
> @.SortOrder nvarchar
> )
> AS
> SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
> GO
> ****************************************
*
> * This message was posted via http://www.droptable.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.droptable.com/Uwe/Abuse...4de59ba32b430c3">
> ****************************************
*|||It worl find now, but one more thing, it order by Gross value, the value ord
er by the first char only, why? for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
is it i should write : order by max(groos)?
****************************************
*
* A copy of the whole thread can be found at:
* http://www.droptable.com/Uwe/Forum...ql-server/17779
*
* Report spam or abuse by clicking the following URL:
* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=2b1b743f2fd34951a7b0c07e0de1186a[/u
rl]
****************************************
*|||What datatype is Gross of in your table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gabe Wong via droptable.com" <forum@.droptable.com> wrote in message
news:2b1b743f2fd34951a7b0c07e0de1186a@.SQ
droptable.com...
> It worl find now, but one more thing, it order by Gross value, the value order by
the first char
only, why? for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> is it i should write : order by max(groos)?
> ****************************************
*
> * A copy of the whole thread can be found at:
> * http://www.droptable.com/Uwe/Forum...ql-server/17779
> *
> * Report spam or abuse by clicking the following URL:
> * [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=2b1b743f2fd34951a7b0c07e0de1186a[
/url]
> ****************************************
*|||i CAST it to int, now it work perfectly, Thanks ALL!!! if posible come to Ma
laysia, i buy u dinner!
****************************************
*
* A copy of the whole thread can be found at:
* http://www.droptable.com/Uwe/Forum...ql-server/17779
*
* Report spam or abuse by clicking the following URL:
* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=b40370fa97644873a0bba0c949a38069[/u
rl]
****************************************
*|||> some response please!
Geez, have patience. You did post well outside of business hours in the
western hemisphere...

oracle SQL - promping user input

Hi, I have searched the very useful database you have here and although
given some direction i still have a problem with a query i am working on.
any help given would be greatly appreciated!
the premise of query i am working on requires that a user be prompted for a
variable found on table 3. 2 columns of data from 2 different tables (table
1 and table 2) be displayed - all null values on each of those must be
labeled instead of just having a blank space.
i have not found a way to use 2 outer joins on the same tables so i have
used a UNION operator to combine my results - this all works fine and
displays what it should - however i need to put in a line where the query
will prompt the user with a variable to filter the data. here is my
solution so far.. (i have replaced the specific data where possible with
generic ones for reading ease)
--start query
select COALESCE(a.column_1, 'NONE ASSIGNED') as "Column 1", b.column_2 as
"Column 2"
from table_1 a, table_2 b
where b.table_id = a.table_id(+)
UNION
select a.column_1 as "Column 1", COALESCE(b.column_2, 'NO PERSON ASSIGNED')
as "Column 2"
from table_1 a, table_2 b
where a.table_id = b.table_id(+);
--end query
the results look something like this:
Column 1 Column 2
100 John
200 NO PERSON ASSIGNED
300 Smithy
NONE ASSIGNED Wayne
etc etc..
i want the query to prompt the user for a variable (lets say "name of
country") - which is from a third table joined to the other 2, so it only
filters those results. however because of the UNION clause, it will only
filter the results from one of the 2 queries and will list the results from
the other query.
the line i have been experimenting is:
where c.country_name = '&Country Name'
and adding table_3 c to the FROM clause and also joining them to the other
tables with WHERE c.xxx_id = b.xxx_id
cliff notes - is there any way to prompt the user to input a variable and
then listing the relevant results from both select statements in the UNION
query...?
I am not sure if i am articulating this problem in the best possible way,
my apologiesThis is a Microsoft SQL Server group but your code is for Oracle. Are you
looking for a conversion to Transact-SQL?
As regards prompting the user, Transact-SQL doesn't have any UI
functionality. You have to prompt the user using your client application or
host programming language. Since you haven't specified what
application/language that is we can't really advise you. Please repost to
the correct group if you need more help.
David Portas
SQL Server MVP
--|||Are you going to tell me MS didn't receive any *wishes* for an
Sql*Minus (I mean Plus:) utility?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:2uKdncChTPcRrw_fRVn-hQ@.giganews.com...
> This is a Microsoft SQL Server group but your code is for Oracle. Are you
> looking for a conversion to Transact-SQL?
> As regards prompting the user, Transact-SQL doesn't have any UI
> functionality. You have to prompt the user using your client application
> or host programming language. Since you haven't specified what
> application/language that is we can't really advise you. Please repost to
> the correct group if you need more help.
> --
> David Portas
> SQL Server MVP
> --
>