SSRS Issue (SUM and Count)

Question

SSRS Issue (SUM and Count)

SQLPain

Hall of Fame

Points: 3522
More actions
September 9, 2015 at 9:41 am

#399012

I created a report where collected payments are listed by each collector from month to date. I am calling the below SP for the report:
Everything Looks ok on the reporting side until a requirement came up where I am Also supposed to list the total number of payments collected by each collector (the count) and also the (total sum). If you look at the attached excel you would see two tables, I am getting the first table, 2nd is the one I want expected). How do I perform sum and count operations? Any process/method or expressions anyone could suggest would be highly appreciated.
ALTER PROC USP_PaymentsByCollector
(
@CollectorName AS varchar(20) = NULL,
@CollectorCode AS varchar(20) = NULL,
@LoanID AS varchar (20) = NULL
--@StartDate AS DATETIME = NULL,
----@EndDate AS DATETIME = NULL
)
AS
BEGIN
SELECT
SUM(H.TransactionAmt) AS Damon
,COUNT(H.TransactionAmt) AS DamonCt
,H.LoanID
,CC.CollectorCode AS CollectorCode
,CC.CollectorName AS CollectorName
,H.TransactionAmt
,T.Description AS TranCodeDesc
,H.TransactionDate
,R.Description AS ReversalFlagDesc
,H.MoneyType
,M.Description AS MoneyTypeDesc
FROM dbo.Company
CROSS JOIN dbo.CollectorCodeHistory AS CC
INNER JOIN dbo.History AS H ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime
AND (CC.EndDate >= H.SysDateTime OR (CC.EndDate IS NULL AND GETDATE() >= H.SysDateTime))
AND H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt <> 0
INNER JOIN dbo.TransactionCode T ON H.TransactionCode = T.Code
INNER JOIN dbo.S_ReversalFlag R ON H.ReversalFlag = R.Code
INNER JOIN dbo.S_MoneyType M ON H.MoneyType = M.Code
WHERE H.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND H.TransactionDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
AND (@CollectorName IS NULL OR CC.CollectorName like '%' + @CollectorName + '%')
AND (@CollectorCode IS NULL OR CC.CollectorCode = @CollectorCode)
AND (@LoanID IS NULL OR H.LoanID LIKE '%' + @LoanID + '%')
--AND (@StartDate IS NULL OR (H.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY H.LoanID
,CC.CollectorCode
,CC.CollectorName
,H.TransactionAmt
,T.Description
,H.TransactionDate
,R.Description
,H.MoneyType
,M.Description
ORDER BY TransactionDate
END
EXEC USP_PaymentsByCollector @CollectorName = 'DD'

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63591 More actions · Answer 1

If I understand the question, this is trivial. You add a Row Group Total on [LoanID] or [CollectorName] (depends what you're getting totals on), and then you just use SUM([TransactionAmount]) for the total, and COUNT([CollectorID]) for the Count. They go inside the group header row.

pietlinden SSC Guru Points: 63591 More actions · Answer 2

Getting really good at double-posting, so I might as well fix up this reply with something useful....

Here's how to post some sample data:

CREATE TABLE Payments(

LoanID INT

,CollectorName CHAR(3) NOT NULL

,TransactionAmount MONEY

,PaymentType VARCHAR(15) NOT NULL);

GO

INSERT INTO Payments VALUES (44000,'D D',444.86, 'ACH'),

(44000,'D D',232.22,'Phone Pay'),

(44000,'D D',168.18,'Lockbox'),

(44000,'D D',-476.96,'ACH'),

(44000,'D D',476.96,'ACH'),

(44000,'D D',359.07,'Phone Pay'),

(44000,'D D',850,'Credit Card'),

(44000,'D D',321.09,'ACH'),

(44000,'D D',322.79,'ACH');

Now that everyone can reproduce your problem, we can help solve it. =)

I did it by adding a tablix to the design surface and adding a Row Group on LoanID. Then I added two columns INSIDE the group to the right. In the first, I put COUNT([CollectorName]), and in the second SUM([TransactionAmount]).

finished. =)

SQLPain Hall of Fame Points: 3522 More actions · Answer 3

Thank you all, I was just not doing it under the row groups.

SSRS Issue (SUM and Count)

Cookies on SQLServerCentral