• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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!
1 Solution
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...

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.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now