Group by Top # entered in as Parameter

Background: I have a report that groups by Item number and gives adds
up total amount for that item number. What I want to do is have the
user enter in a numeric value as a parameter such as 10, 15, 20, etc
that will then only display the TOP 10, 15, 20, etc (what they entered
in the parameter) total amounts on the report. Can anyone help me out,
Im sure this can be done but it gets tricky with the parameters thrown
in the mix. Any suggestions is much appreciated. Thanks!hi brent
you can do this w/o issue by using a stored procedure as the source dataset
(and having your 'TOP' value included as one of the parameters).
next, you are going to need to supply a dataset for the dropdown:
select '10' as topval
union
select '20' as topval
union
select '3....
if you plan on 'rolling your own' ASP.NET interface, you can preload the
values for the dropdown in HTML.
Rob
"Brent" wrote:
> Background: I have a report that groups by Item number and gives adds
> up total amount for that item number. What I want to do is have the
> user enter in a numeric value as a parameter such as 10, 15, 20, etc
> that will then only display the TOP 10, 15, 20, etc (what they entered
> in the parameter) total amounts on the report. Can anyone help me out,
> Im sure this can be done but it gets tricky with the parameters thrown
> in the mix. Any suggestions is much appreciated. Thanks!
>

Group By clause with an inserted column

Good afternoon all,
I'm trying to write SQL that adds a descriptive column and groups on that
column but I get an error saying my inserted column is invalid. Can anyone
help? An example follows.
Sales Table:
Sales Type Amount
A 5.00
A 6.00
B 2.00
SQL:
SELECT
(CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES'
END), SUM(Amount)
GROUP BY '?
Desired Result:
TAXABLE SALES 11.00
NONTAXABLE SALES 2.00
Thanks in advance,
Don J> GROUP BY '?
CASE
WHEN Sales Type = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END
AMB
"Don Jellie" wrote:

> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can anyon
e
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES
'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J|||Don Jellie wrote:
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on
> that column but I get an error saying my inserted column is invalid.
> Can anyone help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
> SALES' END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J
create table #a (SalesType char(1) NOT NULL, Amount DECIMAL(10, 2) NOT
NULL)
go
Insert Into #a Values ('A', 5.00)
Insert Into #a Values ('A', 6.00)
Insert Into #a Values ('B', 2.00)
go
SELECT
CASE
WHEN SalesType = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END,
SUM(Amount)
From
#a
GROUP BY SalesType
go
drop table #a
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Since your column named Sales Type has a space in it, you must "Quote" it by
wrapping it in " " or [ ] like so:
SELECT
CASE WHEN [Sales Type] = 'A'
THEN 'Taxable Sales'
ELSE 'NonTaxable Sales'
END AS "Sales Type",
SUM(Amount) AS "Amount"
GROUP BY [Sales Type]
"Don Jellie" <Jelliebean1@.msn.nospam.com> wrote in message
news:ADDCEAC9-659D-4EA2-8252-25E5DB0D6840@.microsoft.com...
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can
anyone
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
SALES'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J