locked
Can't get Sum of values from one column basd on distinct values of another column in SSRS RRS feed

  • Question

  • Hi,

    I am working on SQL Server 2005, SSRS 2005.

    I have a dataset from an SSAS cube which returns data like below

    Cust     Company   Book    Cost    Cost2     Cost3    Qty
    1          1               1          10      0            0           5
    1          1               1          0        10          0           5  
    1          1               1          0        0            10         5
    1          1               2          10      0            0           7
    1          1               2          0        10          0           7
    1          1               2          0        0            10         7
    2          1               1          10      0            0           12
    2          1               1          0        10           0          12

    So, first group is on Cust, second on company, third on Book.
    For all customer, company, book, all the costs should be added up but the quantity should not be.
    The quantity remains same for a combination of Cust, Company and Book.

    I can get the right value with First(Qty) under the group Book but For a Cust and Company, there are two books. Therefore, when I sum the Qty in the parent group Company, it shows 5*3 + 7*3, where as my requirement is to get 5 + 7, based on unique Book under a Customer and Company.

    How can it be achieved?

    Thanks
    Rahul
    Wednesday, January 6, 2010 11:23 AM

Answers

  • Hi Rahul,

    By default, both SQL Server Reporting Services 2005 and SQL Server Reporting Services 2008 does not have a aggregate function to sum distinct.

    We can implement custom aggragate function using custom code to do sum distinct.

    Please follow the steps described in Robert's article to implement custom aggregate function.
    http://blogs.msdn.com/robertbruckner/archive/2008/07/20/Using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx
    In, this case, we just need to replace the code function with:

    Dim totalValues As New System.Collections.Hashtable
    Dim keyValues As New System.Collections.Hashtable
    Function AddValue(ByVal customID As Integer, ByVal companyID As Integer, ByVal bookID As Integer, ByVal qty As Integer) As integer
            If (Not keyValues.Contains(customID.ToString()& companyID.ToString() & bookID.ToString())) Then
     keyValues.Add(customID.ToString()& companyID.ToString() & bookID.ToString(), nothing)
     If (Not totalValues.Contains(customID.ToString()& companyID.ToString()) ) Then
      totalValues.Add(customID.ToString()& companyID.ToString(), qty)   
     Else
      totalValues(customID.ToString()& companyID.ToString()) = totalValues(customID.ToString()& companyID.ToString())   + qty   
     End If
            End If
    End Function
    Function GetTotal(ByVal customID As Integer, ByVal companyID As Integer) As integer
     If (totalValues.Contains(customID.ToString()& companyID.ToString()) ) Then
      return totalValues(customID.ToString()& companyID.ToString()) 
     Else
      return 0
     End If
    End Function



    Please replace "=code.AddValue(Fields!UnitPrice.Value)" with:

    =Code.AddValue(Fields!Cust.Value, Fields!Company.Value, Fields!Book.Value, Fields!Qty.Value)



    Please replace "=Code.GetMedian" with:

    =Code.GetTotal(First(Fields!Cust.Value), Fields!Company.Value)

    Please feel free to ask, if there is anything unclear.

    Thanks,
    Jin Chen
    Jin Chen - MSFT
    Friday, January 8, 2010 7:25 AM