Finding Nth Smallest Values inside Cross Tab

Question

Finding Nth Smallest Values inside Cross Tab

meghna.bhargav

SSCrazy

Points: 2634
More actions
April 7, 2008 at 11:54 pm

#186127

Hello,
I am working on migration report(from rpt to rdl )in which cross tab is used and inside that Nth Smallest Values(from 1-8) has to be displayed.
When I put formula field that calculates the value, it prints first value found in that set.
If I put min or max for formula field , it prints Max and Min value from that set.
Does anyone know how to display Nth Smallest Values inside Cross Tab?
I guess it can be done by passing dataset from rdl to custom code function , but I do not know how to pass a dataset to custom function.
Thanks
Meghna

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

You're going to have to break it up. You need to the initial query to only return the 8 smallest values per grouping, and then send that to the cross-tab report.

The Transact-SQL would look something like this:

as a CTE

;with Smallest8CTE as (

select groupcolumn,

subgroupcolumn,

value,

ROW_NUMBER() over (PARTITION BY groupcolumn,subgroupcolumn order by value) RN

from MyTable)

select groupcolumn,

subgroupcolumn,

value

from Smallest8CTE

where rn<9

which is the same as this non-CTE notation

select groupcolumn,

subgroupcolumn,

value

from ( select groupcolumn,

subgroupcolumn,

value,

ROW_NUMBER() over (PARTITION BY groupcolumn,subgroupcolumn order by value) RN

from MyTable

) Smallest8CTE

where rn<9

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

meghna.bhargav SSCrazy Points: 2634 More actions · Answer 2

thanks for reply , but i m using SQl2000 , where ROW_Number doesnt work.

Can you provide me something which i can use in 2000?

Waiting for your reply

thanks

Meghna

Naughtycell Old Hand Points: 354 More actions · Answer 3

select top 1 * from (

SELECT TOP nth * FROM tablename

ORDER BY column DESC) a

Just make sure you don't have >two records with the same value.

Otherwise use (if so use)

select * from tablename

where column in (select top 1 column from (

SELECT TOP nth * FROM tablename

ORDER BY column DESC) a)

meghna.bhargav SSCrazy Points: 2634 More actions · Answer 4

'SELECT TOP 1st * FROM tablename ' is not working i mean like 'SELECT TOP nth * FROM test'

is givieng error in sql2000.

Can u suggest something?

thanks

meghna.bhargav SSCrazy Points: 2634 More actions · Answer 5

For this table , if i wnt 4th smallest value then answer should be 5 .

create table test(No int)

insert into test values(1)

insert into test values(3)

insert into test values(6)

insert into test values(2)

insert into test values(11)

insert into test values(7)

insert into test values(5)

insert into test values(29)

insert into test values(9)

Hope u getting me?

thanks

Naughtycell Old Hand Points: 354 More actions · Answer 6

select top 1 * from (

select top 4 * from test

order by [No] asc) a

order by [No] desc

Hope this come with relief!

Sello

meghna.bhargav SSCrazy Points: 2634 More actions · Answer 7

meghna.bhargav

SSCrazy

Points: 2634

April 9, 2008 at 4:21 am

#800261

thanks it worked ,Thanks a lot

Meghna

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Meghna,

Just curious... why do you need to do such a thing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meghna.bhargav SSCrazy Points: 2634 More actions · Answer 9

Hi,it works but as i am migrating my crystal report to RDL(Microsoft reporting services), i cant make changes to the Query , can anyone tell me how to do this using function from front end?

Any help will be highly appreciated!

thanks

Meghna

Axaptamundo SSC Enthusiast Points: 198 More actions · Answer 10

To do this from the front end right-click the group column in your matrix report > Edit Group > Filter tab.

Expression: the value in your total column e.g. =Sum(Fields!QTY.Value)

Operator: Bottom %

Value: 5 (for bottom 5%)

I agree it was much simpler in Crystal...

meghna.bhargav SSCrazy Points: 2634 More actions · Answer 11

hi david , thanks for your reply.

But i m not getting exactly what are you trying to convey to me , what does Botton N mean?

If you can u make it more clear to me, i will be highly grateful to u.

Thanks

Meghna

Axaptamundo SSC Enthusiast Points: 198 More actions · Answer 12

Bit difficult to explain but there is a screenshot in http://books.google.co.uk/books?id=b0dXlx5aww8C&pg=PA113&lpg=PA113&dq=reporting+services+matrix+report+show+top+n&source=web&ots=U0MYGHDUWS&sig=AvFsWubHTQU-6PmHxqjrV6rF44A&hl=en

Basically you are filtering the row group so it only shows the bottom or top n percent of values.