Top 10 with ties

I have the following tale that appears like this:

IMAGE

Its show the top 10 company, with the top 5 sub companies underneath in a table where I use the filter TOPN = 10 . My problem comes in when I have two company that exist in the top 10 with the same value, in this case $0.00 it shows more than 10. I understand that using the top = 10 in sql reporting services is suppose to function like this, but want to only show 10. My SP looks like this:

SELECT SicDescription, ISNULL(Company, 'OTHER') AS Company, AccountMV, AccountFeely,sicid,relationshipid as 'rnumber' from snapsraw
WHERE (Branchstate = @state) AND sicdescription is not null AND (monthend = @date)

Any help would be greatly appreciated, I heard of some people saying use the rownumber function, but not sure how to implement it if thats the only solution

Answer this question

Top 10 with ties

project2n5e0o1
Why not run a query before hand to do a grouping ... so it will group the like companies into one row instead of having multiple ones. Can dump it to a temp table or something and then run your top 10 query. If I'm understanding the problem correctly... I can't view that image at work... its blocked. :/
Dead_Zone

The reason I dont want to group into one line is because each company might have a sub-company, and if i group on company, i will not be displaying the subcompanies anymore, its the subcompanies that make of the top 5 under the top 10

examp

company 1 $999

subcomp1 $333

subcomp2 $333

subcomp3 $333

........
sveroa
Problem with that is it only brings back the top 10 records, I need it to bring back all the records, and than currently sql reporting services with show top 10 companies and top 5 sub companies underneath the top 10
Mike Howes

Hammer

Would you mind outling how that query would look, this is what I have now:

SELECT SicDescription, ISNULL(Company, 'OTHER') AS Company, AccountMV, AccountFeely,sicid,relationshipid as 'rnumber' from snapsraw

WHERE (Branchstate = @state) AND sicdescription is not null AND (monthend = @date)
Munita Vohra
Maybe you want to stop breaking your head and write a custom data processing extension. That's what I have been forced to do to meet 'complex' report requirements and since then, I always use data processing extension even for simple reports. With this kind of extension, you can do whatever you want with whatever data from whatever datasource, you can even read data from a txt file if you want, this is very powerful!
Steve from adzac

Duane

Why not just return the Top 10 in your Query

SELECT TOP 10 SicDescription, ISNULL(Company, 'OTHER') AS Company, AccountMV, AccountFeely,sicid,relationshipid as 'rnumber' from snapsraw
WHERE (Branchstate = @state) AND sicdescription is not null AND (monthend = @date)

Ham
VinceG
Maybe so, but overkill in this scenario (and certainly for connecting to .txt files). You might also want to mention the additional deployment considerations for writing extensions.
Furqanms

Duane,

In your table, right click select properties, select Filter tab, in the Filter List

Expression = your account value

Operator = Top N

Value = 10

I hope this is what you were looking for.

Ham
Waltari

Duane,

My approach would be to first select the 10 companies, after I have the 10 top companies, I would then select the top 5 sub-companies for my TOP 10 companies. I would group my report by the main companies and my detail would be the sub-companies.

Ham
johnstonb

You should do this in your query. Assuming you are using a SQL 2005 database, you can use the new DENSE_RANK windowing function to perform a rank with ties.

I'm not sure what your currency filed is.

Try this:

WITH    myCTE AS
(
SELECT SicDescription
      , ISNULL(Company, 'OTHER') AS Company
      , AccountMV
      , AccountFeely
      , sicid
      , relationshipid           AS rnumber
      , DENSE_RANK() OVER (ORDER BY AccountMV) AS rnk

FROM    snapsraw

WHERE   Branchstate               = @state
AND     sicdescription           IS NOT NULL
AND     monthend                  = @date
)
SELECT * FROM myCTE
WHERE    rnk <= 10
djbjmb

Just another quick note, this is what we have:

SELECT SicDescription, ISNULL(Company, 'OTHER') AS Company, AccountMV, AccountFeely,sicid,relationshipid as 'rnumber' from snapsraw

WHERE (Branchstate = @state) AND sicdescription is not null AND (monthend = @date)

sicdescription is the top level grouping (TOP10)

company is the 2nd level grouping (top5)
re infecta
Yeah, Im already doing that, the problem is reporting services doesnt know how to break ties
Jordan Roher
Found your forum post whilst trying to solve the same problem, and I came up with this (quite dodgy) solution.

I changed the expression which I am trying to evaluate for my TopN from

=count(Fields!incident_ref.Value) to

=count(Fields!incident_ref.Value) + (rnd *0.00001)

As my data would always be a range of whole numbers, I have effectively ensured that I won't end up with two values the same.

so 20th may be 57.00038 and 21st may be 57.00047. Now I only get 20 rows on each data set.

Hope this makes sense and helps.

Pete
Top 10 with ties

Answer this question