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 Aggregate and Grouping

I apologize for this simple question. I'm trying to create a query such tha
t
the results are grouped by 'City' and the cities are returned in decending
order by the Sum(cash).
I'd like the data returned like this:
city nonAgg1 nonAgg2 'sum'
----
name2 'some value' 'some value' 10
name2 'some value' 'some value' 9
name1 'some value' 'some value' 11
name3 'some value' 'some value' 9
name3 'some value' 'some value' 1
I've been using a query like below:
SELECT City, nonAgg1, nonAgg2, SUM(cash) FROM table
GROUP BY City, nonAgg1, nonAgg2
ORDER BY SUM(cash) DESC, City
The (poorly written) query is, of course, ordering the values as such:
city nonAgg1 nonAgg2 'sum'
----
name1 'some value' 'some value' 11
name2 'some value' 'some value' 10
name2 'some value' 'some value' 9
name3 'some value' 'some value' 9
name3 'some value' 'some value' 1
How can I rewrite the query so that it appears in the order I'm trying to
get it in?Hi, Newbie412
You should post DDL and sample data as "CREATE TABLE ..." statements
and "INSERT INTO ... VALUES ..." statements, like this:
CREATE TABLE TheTable (
TheDate datetime,
city varchar(30),
nonAgg1 varchar(20),
nonAgg2 varchar(20),
cash money,
PRIMARY KEY (TheDate, city, nonAgg1, nonAgg2)
)
INSERT INTO TheTable VALUES ('20051220','name1', 'a', 'x', 5)
INSERT INTO TheTable VALUES ('20051221','name1', 'a', 'x', 6)
INSERT INTO TheTable VALUES ('20051220','name2', 'a', 'x', 10)
INSERT INTO TheTable VALUES ('20051220','name2', 'a', 'y', 9)
INSERT INTO TheTable VALUES ('20051220','name3', 'a', 'x', 5)
INSERT INTO TheTable VALUES ('20051221','name3', 'a', 'x', 4)
INSERT INTO TheTable VALUES ('20051220','name3', 'b', 'y', 1)
The following query returns the expected results:
SELECT t.City, nonAgg1, nonAgg2, SUM(cash) SumOfCash
FROM TheTable t INNER JOIN (
SELECT City, SUM(cash) as CityCash
FROM TheTable
GROUP BY City
) x ON t.City=x.City
GROUP BY t.City, x.CityCash, nonAgg1, nonAgg2
ORDER BY CityCash DESC, t.City
Razvan|||Hi
CREATE TABLE #Test (col1 CHAR(1) NOT NULL, col2 INT NOT NULL)
INSERT INTO #Test VALUES ('A',10)
INSERT INTO #Test VALUES ('A',100)
INSERT INTO #Test VALUES ('A',20)
INSERT INTO #Test VALUES ('B',500)
INSERT INTO #Test VALUES ('C',1)
INSERT INTO #Test VALUES ('C',8)
SELECT col1,col2 FROM #test ORDER BY col1 ASC,col2 DESC
DROP TABLE #Test
"Newbie412" <Newbie412@.discussions.microsoft.com> wrote in message
news:6F96CBC0-E42D-460B-8B4B-417E575350DA@.microsoft.com...
>I apologize for this simple question. I'm trying to create a query such
>that
> the results are grouped by 'City' and the cities are returned in decending
> order by the Sum(cash).
> I'd like the data returned like this:
> city nonAgg1 nonAgg2 'sum'
> ----
> name2 'some value' 'some value' 10
> name2 'some value' 'some value' 9
> name1 'some value' 'some value' 11
> name3 'some value' 'some value' 9
> name3 'some value' 'some value' 1
> I've been using a query like below:
> SELECT City, nonAgg1, nonAgg2, SUM(cash) FROM table
> GROUP BY City, nonAgg1, nonAgg2
> ORDER BY SUM(cash) DESC, City
> The (poorly written) query is, of course, ordering the values as such:
> city nonAgg1 nonAgg2 'sum'
> ----
> name1 'some value' 'some value' 11
> name2 'some value' 'some value' 10
> name2 'some value' 'some value' 9
> name3 'some value' 'some value' 9
> name3 'some value' 'some value' 1
> How can I rewrite the query so that it appears in the order I'm trying to
> get it in?|||Newbie412,
Here are solutions for SQL Server 2000 and 2005, if I
understood your requirements correctly.
CREATE TABLE #Test (
City VARCHAR(5) NOT NULL,
nonagg INT,
cash INT NOT NULL
)
INSERT INTO #Test VALUES ('name1',1,6)
INSERT INTO #Test VALUES ('name1',1,1)
INSERT INTO #Test VALUES ('name1',1,4)
INSERT INTO #Test VALUES ('name2',1,10)
INSERT INTO #Test VALUES ('name2',2,4)
INSERT INTO #Test VALUES ('name2',2,5)
INSERT INTO #Test VALUES ('name3',1,9)
INSERT INTO #Test VALUES ('name3',2,1)
-- SQL Server 2000
SELECT
City,
nonagg,
sum(cash) as sumCash
FROM (
SELECT
City,
nonagg,
cash,
(select sum(cash) from #Test as T2
where T2.City = T1.City) as orderKey
from #Test as T1
) AS T
GROUP BY City, orderKey, nonagg
ORDER BY orderKey DESC, City, nonagg
GO
-- SQL Server 2005
;
WITH T(City,nonagg,cash,orderKey) AS (
SELECT
City,
nonagg,
cash,
sum(cash) over (partition by City) as orderKey
from #Test
)
SELECT
City,
nonagg,
sum(cash) as sumCash
FROM T
GROUP BY City, orderKey, nonagg
ORDER BY orderKey DESC, City, nonagg
GO
DROP TABLE #Test
-- Steve Kass
-- Drew University
Newbie412 wrote:

>I apologize for this simple question. I'm trying to create a query such th
at
>the results are grouped by 'City' and the cities are returned in decending
>order by the Sum(cash).
>I'd like the data returned like this:
>city nonAgg1 nonAgg2 'sum'
>----
>name2 'some value' 'some value' 10
>name2 'some value' 'some value' 9
>name1 'some value' 'some value' 11
>name3 'some value' 'some value' 9
>name3 'some value' 'some value' 1
>I've been using a query like below:
>SELECT City, nonAgg1, nonAgg2, SUM(cash) FROM table
>GROUP BY City, nonAgg1, nonAgg2
>ORDER BY SUM(cash) DESC, City
>The (poorly written) query is, of course, ordering the values as such:
>city nonAgg1 nonAgg2 'sum'
>----
>name1 'some value' 'some value' 11
>name2 'some value' 'some value' 10
>name2 'some value' 'some value' 9
>name3 'some value' 'some value' 9
>name3 'some value' 'some value' 1
>How can I rewrite the query so that it appears in the order I'm trying to
>get it in?
>