Group Total..

I'm working on a Financial Report which contains a column "XYZ" , its value
is calculated from a formula by passing the row's record id and commission
rate. (the formula is inside a custom dll). The values are correctly
computed. Now, the footer should display the total of all the rows in the
group.
for instance:
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
Pepsi $361,000 gfyeefyefffee
Coca Cola $475,250 djfdfjdfddddd
RCola $28,757 re8reruejreerr
fdfsfnfsfssf
_________________________________________
Total: $ 865,007
Each of the "XYZ Total" in the above example, uses an expression as = FindTotal(recID!value, comm_rate!value)
In this case, how do I get the total in the footer? How to recursively add
the FindTotal expression when it contains the row's unique record id?
Thanks
P.S. The above data is a sample data. The actual report contains 3 different
levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
Transaction TitleI recently came across a new software, that I think you might want to look into.
www.simx.com/simx/home_report%20manager.htm
Works with SQL Server, and I was able to do reporting much like what you are describing.
"newmem" <"" wrote:
> I'm working on a Financial Report which contains a column "XYZ" , its value
> is calculated from a formula by passing the row's record id and commission
> rate. (the formula is inside a custom dll). The values are correctly
> computed. Now, the footer should display the total of all the rows in the
> group.
> for instance:
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> Pepsi $361,000 gfyeefyefffee
> Coca Cola $475,250 djfdfjdfddddd
> RCola $28,757 re8reruejreerr
> fdfsfnfsfssf
> _________________________________________
> Total: $ 865,007
> Each of the "XYZ Total" in the above example, uses an expression as => FindTotal(recID!value, comm_rate!value)
> In this case, how do I get the total in the footer? How to recursively add
> the FindTotal expression when it contains the row's unique record id?
> Thanks
> P.S. The above data is a sample data. The actual report contains 3 different
> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> Transaction Title
>
>|||While I appreciate your eagerness to help, I think that most people would
prefer that you refrain from advertising other products in a forum dedicated
to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
not to post there. :)
That being said, you should be able to define a custom field that does the
calculation and then referce the custom field in a sum in the group footer.
Presumably, you only need to add values from the inner group as the outer
group is just summary. If you want it to do parent / child hierarcy
aggregates, you need to use the recursive keyword.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>I recently came across a new software, that I think you might want to look
>into.
> www.simx.com/simx/home_report%20manager.htm
> Works with SQL Server, and I was able to do reporting much like what you
> are describing.
> "newmem" <"" wrote:
>> I'm working on a Financial Report which contains a column "XYZ" , its
>> value
>> is calculated from a formula by passing the row's record id and
>> commission
>> rate. (the formula is inside a custom dll). The values are correctly
>> computed. Now, the footer should display the total of all the rows in the
>> group.
>> for instance:
>> "Unit" "BrandName" "XYZ Total" "Comments"
>> Sodas
>> Pepsi $361,000 gfyeefyefffee
>> Coca Cola $475,250 djfdfjdfddddd
>> RCola $28,757 re8reruejreerr
>> fdfsfnfsfssf
>> _________________________________________
>> Total: $ 865,007
>> Each of the "XYZ Total" in the above example, uses an expression as =>> FindTotal(recID!value, comm_rate!value)
>> In this case, how do I get the total in the footer? How to recursively
>> add
>> the FindTotal expression when it contains the row's unique record id?
>> Thanks
>> P.S. The above data is a sample data. The actual report contains 3
>> different
>> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> Transaction Title
>>|||Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as => >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>

Group Filter doesn't work as expected

Employee (Group H1) AVG( of all Ratings)
Job # (Group H2) Rating
Rating is a "Calculated" field in the Dataset.
I put a filter on Job# (Group H2) to only include Ratings > 25%
Results:
Job#'s are filtered properly and every job < 25% does NOT show. HOWEVER,
the AVG (of all Ratings) ignores the Group 2 filter and includes every job
even those with a rating < 25%.
How can I get the "Avg Rating" to also only average those ratings according
to the group 2 filter I setup?
mythreadsHi,
Try setting up th filter this way. Write the expression return a true/false
value:
For expression use this (or similar): Job# > 25%
For Operator use: =For Value use: True
HTH!
Kind regards - Fred|||Hi,
Try setting up th filter this way. Write the expression return a true/false
value:
For expression use this (or similar): Job# > 25%
For Operator use: =For Value use: =True (*** CORRECTED FROM LAST POST***)
HTH!
Kind regards - Fred|||Thanks Fred for responding.
Your suggestion produced the same results. After further digging we solved
this original problem by putting the Filter on the "Dataset" and not the
report. I didn't know you could do this.
When I put the filter on "Group 2" header, "Group 2" detail was correct, but
"Group 1" header ignored the filtering in "Group 2" and used the raw Dataset
information.
When we put the filter on the dataset, it applied to both Group 1 and Group
2.
Thanks again.
"Fred Block" wrote:
> Hi,
> Try setting up th filter this way. Write the expression return a true/false
> value:
> For expression use this (or similar): Job# > 25%
> For Operator use: => For Value use: =True (*** CORRECTED FROM LAST POST***)
> HTH!
> Kind regards - Fred
>
>

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? :)