grouping or formatting issue

hey there

I have a date time field

Fields!CreatedDate.Value which I am grouping on and Counting on an ID Field

result of grouping is this

7/13/2006 12:00:00 AM 1

7/21/2006 12:00:00 AM 11

8/7/2006 12:00:00 AM 3

8/8/2006 12:00:00 AM 2

I know if I put (Month(Fields!CreatedDate.Value)

I get this result

7 1

7 11

8 3

8 2

What I really want is

July 12

August 5

how do I do this please

thanks

Hi there!
I dont know if this will resolve your issue but maybe if you try the following:

Format((Month(Fields!CreatedDate.Value)), "MMM")

If this works like I think it might then it should return:

Jul 12

Aug 5......etc

I cant test right now so I cant be sure...!?|||

Try

Monthname(Datepart("m", Fields!CreatedDate.Value))

This report function will return the "January, Feburary, March and etc...

Ham

|||

ok thanks for the replies

unfortunately not quite complete

Ham what I get then is

July 1

July 11

August 1

August 3

etc

so I need

total for July

total for August

etc

cheers

|||

thought I should put the sql

SELECT TicketNumber, CreatedDate
FROM Ticket
ORDER BY CreatedDate

grouped on created date - associated to parent group created date

count on ticketnumber

cheers

Dianne

|||

jewel,

Try

Placing the expression Monthname(Datepart("m", Fields!CreatedDate.Value)) into your group expression (edit group), this will cause the group to only consider the month when summating the values.

Ham

|||

thanks Ham

I had to put this in the group expression and the field expression to get it to work? I presume this is ok?

But yes it does work now thanks heaps for your help

jewel

GROUP BY highest score per user

Hi there!

I've got a SPROC that generates a recordset of user vote tallies (they're calculated in a separated SPROC). The user submissions are grouped by a GUID value so as to remain unique for a user's submission (each user can have multiple submissions.

The problem is that the recordset returned displays ALL the users, and I'd like to only select the highest score for each user. So, if I have 500 submissions from 3 users (User1 and User2 submit once each and User3 submits 497 times), the total recordset will have 3 rows - being the highest score per user, discounting the others.

Here's my base query:

SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID
ORDER BY [TotalPoints] DESC,[Name] ASC

...and I've been able to get the highest vote per user, discounting duplicate entries, by using this:

SELECT a.UserID,MAX(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID

How can I write combine the two in a nested subquery to display only the top score per user?further, here are the table schema:

USERS
- UserID (INT)
- Name (VARCHAR)

BALLOTS
- UserID (INT)
- SubmissionGUID (VARCHAR)
- TotalTally (INT) DEFAULT '0' -- this is incremented by varying values as a user makes correct selections
- WinningTeam (VARCHAR)

also, you can just assume that there are multiple submission, with each submission consisting of 63 records in the BALLOTS table, each with the same SubmissionGUID.

so, a user's total point would be the SUM'med value of all of their records grouped by a SubmissionGUID. Thus, a user "JOHN", could have the following:

USER SUBMISSIONGUID NAME TOTALTALLY
-- -- -- ----
1 kugiuvbiu JOHN 45
2 olhilugiu STEVE 32
3 oih98y897 MARK 31
1 89769gibi JOHN 29
1 0980jpo90 JOHN 13

I'd like just to select each unique USER's highest TOTALTALLY and display that, and forget about the others.

Clear as mud? :)