Microsoft Reporting Issue

I am having a problem using the dataset into a Microsoft Report (rdlc).  I basically need to create a "Tests Summary" report.  Based on the following data

Test                   Score           Date Taken
Reading                44                  1-1-2008
Algebra                90                  1-1-2008
Algebra                48                  12-2-2007
Reading                56                  12-2-2007

The end result, I'm looking for is the highest score (grouped by Test), so:
Algebra             90           1-1-2008
Reading             56           12-2-2007

The problem is in the sql you can not include the date unless you also use it in the group by.

So I'm wondering how I would setup the DataSet in VS 2008 so that I can return the data I need.  I've stripped the date from the query to return just the Highest Score by Test Type.  I can display that in a Report Table just fine, but need to figure out how to put in the third column, in the report, for the Date the highest score was taken from?

Can you help?  Thanks!
LVL 1
rock815Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gigglickCommented:
select distinct Test , max(score) ,[Date taken] from mytable group by Test, [Date taken]

The above sql is valid and should give you what you are looking for...although I may have misunderstood your question.  If this is not what you are looking for please clarify and hopefully I can help.  I used rs a lot...

Gigg
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rock815Author Commented:
Thanks, but that query gives me....

Algebra      29      2008-01-22 05:00:00.000
Prealgebra      84      2008-01-22 05:00:00.000
Reading      98      2008-01-22 05:00:00.000
Writing      98      2008-01-22 05:00:00.000
Algebra      99      2008-02-11 05:00:00.000

As you see, there are 2 Algebra instances.  I need to ONLY keep the highest score.

Thanks!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.