Hi people,

I love reporting services 2005 BUT have struck a major limitation!

Basically I need a sum distinct function. I have various duplicate details lines and just need to sum the unique values. Anyway this is not possible and a number of people a stuck with this. Yes you can write another sql statement using DISTINCT but then how can you easily integrate that into a table with scope? you can't!

Anyway has anyone been able to achieve this nicely in reporting services? I was thinking of calling a distinct SQL statement from an Expression in a text box on a header field and passing another text box as a parameter to get around this limitation. Is this possible?

Here is a link with similiar problem

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=240606&SiteID=1

Cheers
Damien

Is this an overall sum distinct for the entire table?

If yes, you could write a custom code function to calculate the custom aggregation. See the following blog article for an example: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert

Rob,

Yes this is what I'm after and this is heading down the rigth track. However, a hash table only takes 2 values and I need to have summary of different values. For example, the following is the equivliant SQL statement I am trying to achieve

SELECT Region AS SUMREGION, SUM(DISTINCT HA_ACTUAL) AS SUMHA, SUM(DISTINCT JobBudget) AS SUMJOBBUDGET
FROM vwJobDetailsSummary
WHERE (Region = N'Establishment Costs')

As you can see I need to sum distinct on area and budgets. I need to then be able to put this summary information into the totals fields in my reports based on scope. E.g If Region is AUSTRALIA then put the corresponding SUMHA and SUMJOBBUDGET in the text box based on scope?

This is a severe limitation of Reporting Services and surely there must be a nicer workaround?

I have though about making a UNION of the above SQL statement and the main dataset used and then populating text box with values, but once again how I can choose value based on scope in a Text box?

Once again thanks Rob but I need more values and summary info so intial post is not quite the full solution for me

Cheers

Damien

If you need to sum distinct two different values, you could just add two different custom code functions based on two different hashtables.

Once you add the requirement for scope based sum distinct it becomes more tricky. One way to solve this would be to build hierarchical hashtables (not necessarily efficient, but it could get the job done with limited coding effort).

Let's assume you have one grouping level (i.e. Region) for the sum distincts, then the outer hashtable would have the regions as key and another hashtable (the distinct values of that region) as value. In order to build the distinct sums per region, you would select one region from the outer hashtable and sum up all values contained in the inner hashtable for that particular region key.

But maybe you can write your query differently so that you don't need to the sum distinct in the report, but instead have additional columns in the data set query which represent the distinct values and 0 otherwise.Hence,you couldthen just use the simple Sum aggregate in the report based on these columns returns actually provided the sum distinct.

-- Robert

I have a similar problem and am wondering how you would change the query to report distinct values in an extra column. IfI have the following:

Name Avail_Hrs Hrs_Worked work_type date

There could be multiple different work_types per person, per date, however the Avail_hrs for each day would be the same. How could I get the distinct Avail_hrs based on the unique date?

This is the exact functionality I am trying to achieve. (Avail_Hrs, Hrs_Worked, etc.) Were you able to find a solution?

"Let's assume you have one grouping level (i.e. Region) for the sum distincts, then the outer hashtable would have the regions as key and another hashtable (the distinct values of that region) as value. In order to build the distinct sums per region, you would select one region from the outer hashtable and sum up all values contained in the inner hashtable for that particular region key."

Does anybody have an example of this method?

Here are code I used. Thx for Brian Welcker

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Code Snippet

Dim IDs() As System.Collections.Hashtable=New System.Collections.Hashtable(15) {}

Dim totals() as Double = New Double(15) {}
DIm Grps as System.Collections.Hashtable
Dim i as integer

Function DistinctSum(Byval sGrp as String, ByVal ID As Object, ByVal val As Object) As Double
If (Grps is nothing) Then
Grps = New System.Collections.Hashtable
End if
If (Not Grps.Contains(sGrp)) Then
i=Grps.Count+1
Grps.Add(sGrp, i)
Else
i=Grps.Item(sGrp)
End If
If (IDs(i) Is Nothing) Then
IDs(i) = New System.Collections.Hashtable
End If
If (ID Is Nothing) Then
DistinctSum= totals(i)
Else
If (Not IDs(i).Contains(ID)) Then
totals(i) = totals(i) + val
IDs(i).Add(ID, val)
End If
DistinctSum= totals(i)
End If
End Function

In the Group Header, put

Code Snippet

=Sum(Code.DistinctSum(Fields!GroupField.Value, Fields!ConstitID.Value, Fields!AvailHours.Value))

intoa hiddentextbox.

In the Group Footer, put

Code Snippet

=Code.DistinctSum(Fields!GroupField.Value, Nothing,Nothing)

Sample Result:

	Available Hours	Hidden
GroupA		18312
User A	56
User B	56
User C	56
User D	56
User E	56
User F	56
Total of GroupA	336
GroupB		24360
User G	56
User H	56
User I	56
User J	56
User K	56
Total of GroupB	280

This was the code I am looking for, thanks....

but does this work for a Datetime Field aswell? Or what do i have to change in the code?

Here's my version:

Dim orderIDs As System.Collections.Hashtable

Function MyFunc(ByVal orderID As Object) As integer
dim flag as integer
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (Not orderIDs.Contains(orderID)) Then
flag = 1
orderIDs.Add(orderID, nothing)
else
flag = 0
End If

MyFunc = flag

End Function

In the report:

=sum(val(iif(Code.MyFunc(OrderNbr.value) = 1, CurrentBalance.value, 0)))

Excelente gracias...

hi when i use this in my fotter row this is working fine,but when i am using the same soultion for a grouped row this is returning zero

how to over come this?

hello , i have this problem, i use la continiun expresion
=RunningValue(Fields!monto.Value, Sum, "Product"), but

I want to do a sum, cumulative. at that monent no problem, but when it happens I want to date you start again and not as in the graph below the sum.
thanks

You can reuse the above code for different subtotal<o:p></o:p>

If you have 4 groups and one grand total create the function 5 times and have it as different names and call each one of the function in each subtotal.<o:p></o:p>

A more simpler solution is given here: http://techequation.com/distinct-sum-of-detail-rows-in-ssrs/

Might help someone in the future.

• Edited by ledzeppelin acdc random 6 hours 16 minutes ago
• Proposed as answer by ledzeppelin acdc random 4 hours 15 minutes ago