Limiting to 1 Row per Name using Stored Procedures

Question

Post reply

Limiting to 1 Row per Name using Stored Procedures

tigerlilly_783

Grasshopper

Points: 21
More actions
December 8, 2011 at 6:00 pm

#249391

Hi there,
I'm pretty new to this and to SSRS and I'm trying to write a report. I don't have access to the DB so the only way I can access the data is through Stored Procedures.
From the stored procedure, I am able to get a large set of data but there are multiple rows per Name (see example below)
Name | Country | Date Submitted
Bill | USA | Mar 10 2010
Bill | Canada | Feb 2 2009
Mary | USA | Feb 20 2010
I need to somehow filter down the results so that I see one row for Bill and 1 row for Mary and I'm not sure how to do that.
Any help is GREATLY appreciated!!!!

Viewing 5 posts - 1 through 5 (of 5 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Will this assist you?

CREATE TABLE #T(Name VARCHAR(10), Country VARCHAR(10),Date_Submitted DATE)

INSERT INTO #T

SELECT 'Mary','USA','Dec 20 2011'

SELECT 'Bill','USA','Mar 10 2010' UNION ALL

SELECT 'Bill','Canada','Feb 2 2009' UNION ALL

SELECT 'Mary','USA','Feb 20 2010'

;with numbered as(SELECT rowno=row_number() over

(partition by Name order by Date_Submitted DESC ),Name,Country,Date_Submitted from #T)

select * from numbered --WHERE Rowno = 1

Results:

rownoNameCountryDate_Submitted

1BillUSA2010-03-10

2BillCanada2009-02-02

1MaryUSA2011-12-20

2MaryUSA2010-02-20

Use the WHERE Rowno = 1

Results:

rownoNameCountryDate_Submitted

1BillUSA2010-03-10

1MaryUSA2011-12-20

You can also alter the ORDER BY field for yet different resutls

For example ORDER BY Date_Submitted ASC

Results:

rownoNameCountryDate_Submitted

1BillCanada2009-02-02

1MaryUSA2010-02-20

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

tigerlilly_783 Grasshopper Points: 21 More actions · Answer 2

I actually have to do all of the filtering in the report itself. I could be mistaken, but I thikn you need to add the code you included in the query to the db itself. I can't actually query the db, I have to use a stored procedure which returns the data set in its entirety.

I'm looking at the available functions in the "Expression" and is there a way to do with something like lookupSet, Max, or First functions?

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 3

Create a group level in the report and group on the data that you want in your single row. Leave out the data that is causing the duplicate rows.

If you need one of those values - use min or max to get the value at the group level.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

tigerlilly_783 Grasshopper Points: 21 More actions · Answer 4

Actually, I was able to achieve what I needed by putting this code in the Row Groups > Details > Visibility section

=Fields!Name.Value = Previous(Fields!Name.Value)

Thanks for all the replies though!