sql server reporting services: Maybe your query should look something like ( This example uses the pubs
database)... The issue is going to be that you much sum up all sales for all
accounts you are interested in to the get top N ( I did top 2) ,,, That
could be a long query

select * from sales
inner join
(select top 2 stor_id, sum(qty)as TotalSales from sales s2 group by stor_id
order by 2) as bigsales
on bigsales.stor_id = sales.stor_id


--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)

I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
[quoted text, click to view]

I have some rows with finance periods, there is different periods per
account per year. I then need to have top 10 accounts based on the sum of
all periods for an account.

When I select top 10 i get 2 accounts because there is 10 postings, is it
possible to get top 10 based on distinct account number and sorted by the
highest sum amount of all the periods ?

The report should show the top10 customers, so when i click on one customer
it expands and show the different postings for this account. It works but
only shows the 10 first postings and not accounts.

I'm rather new at this so please help me out, thank you for your fast
response !

Jack

[quoted text, click to view]


Ok, but will i be able to get top 20 if I typed that instead of 2 ? Will
we
still be able to list sales postings for the sum in the report if I did it
this way, you know hit the plus sign and then see all postings ?

Jack


--
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 44700 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

I have a problem with this because its sums up the totaldb for every line
(posting), how can I make the top 5 with sum only run once and not sum up
every time ?

Her is my select statement:

SELECT DEBSTAT.KONTO AS Konto, DEBSTAT.VAREFORBRUG AS Vareforbrug,
DEBSTAT.SLUTRABAT AS Slutrabat, DEBSTAT.KORREKTION AS Korrektion,
DEBSTAT.VAREBELXB AS Varebeløb, DEBSTAT.DATASET AS
Regnskab, DEBSTAT.PERIODESTART AS Periode, DEBKART.KONTO AS Expr1,
DEBKART.NAVN, DEBSTAT.VAREBELXB + DEBSTAT.VAREFORBRUG
AS DB2, bigdb.Totaldb
FROM DEBSTAT INNER JOIN
DEBKART ON DEBSTAT.KONTO = DEBKART.KONTO INNER JOIN
(SELECT TOP 5 konto, SUM(VAREBELXB +
VAREFORBRUG) AS Totaldb
FROM debstat debstat2
WHERE (DAY(DEBSTAT2.PERIODESTART) <> '31')
GROUP BY konto
ORDER BY totaldb DESC) bigdb ON bigdb.konto =
DEBSTAT.KONTO
WHERE (DAY(DEBSTAT.PERIODESTART) <> '31')
GROUP BY DEBSTAT.KONTO, DEBSTAT.KORREKTION, DEBSTAT.VAREFORBRUG,
DEBSTAT.SLUTRABAT, DEBSTAT.VAREBELXB, DEBSTAT.DATASET,
DEBSTAT.PERIODESTART, DEBKART.KONTO, DEBKART.NAVN,
bigdb.Totaldb
HAVING (DEBSTAT.DATASET = @Regnskab) AND (DEBSTAT.PERIODESTART >
@periodestartparm) AND (DEBSTAT.PERIODESTART < @periodeslutparm)

[quoted text, click to view]