February 5, 2014 at 10:27 am
Hello,
I am creating an SSRS report in that I want to create three fields
1.Subtotal
2.Grand total
3.Admin total
For this I created a stored procedure usp_SSRS_RSTotalDetailAmount
In this stored procedure I am getting results as
amount
3440.21
3492.93
68.21
These amount results I need to display in SSRS report like
In subtotal - 3440.21
Grand total - 3492.93
Admin total - 68.21
Problem is that for the same stored procedure with same field how can I display amounts in SSRS
February 5, 2014 at 12:42 pm
Did you consider setting up the stored procedure to return a 3 field table, with fields Subtotal, GrandTotal, and AdminTotal?
February 5, 2014 at 1:12 pm
No, I am not using those fields in my sp,if I use those its very easy to drag me in SSRS report but he problem is I am selecting only 'invoiceamnt' field
Below is my stored procedure..When I execute this sp results showing as like
invoiceamt
234.5
5677.654
344.6
but my expected output is like straight records as a single dataset I tried by renaming amounts as 1 and 2 but not sure its not working..
SELECT I.ReimbursementTypeID,
CAST (SUM(ISNULL(I.AMOUNT,0) - ISNULL(I.OtherAmount,0)) AS NUMERIC(18,2)) AS 'invoiceamt'
FROM grantsystem G
INNER JOIN request R on G.Grantsystemid = R.GrantSystemID
INNER JOIN invoice I on R.requestid = I.requestid
INNER JOIN vlookups L on I.Reimbursementtypeid = L.lookupid
INNER JOIN vSchool VS on G.SystemID = VS.SystemID AND I.SchoolID = VS.SchoolID
WHERE G.fiscalyear=@FiscalYear AND G.SystemID = @SystemId AND I.ReimbursementTypeID IN(34,35) AND
I.SchoolID=Case When @SchoolId ='0' Then I.SchoolID Else @SchoolId End AND
MONTH(R.FundMonth)=Case When @FundMonth='0' Then MONTH(R.FundMonth) Else CONVERT(INT,@FundMonth) End
AND (I.IsActive=1) AND (R.IsActive=1) AND (G.IsActive=1)
GROUP BY fiscalyear, G.SystemID,I.ReimbursementTypeID
UNION ALL
SELECT I.ReimbursementTypeID,
CAST (SUM(ISNULL(I.AMOUNT,0) - ISNULL(I.OtherAmount,0)) AS NUMERIC(18,2)) AS 'invoiceamt'
FROM grantsystem G
INNER JOIN request R on G.Grantsystemid = R.GrantSystemID
INNER JOIN invoice I on R.requestid = I.requestid
INNER JOIN vlookups L on I.Reimbursementtypeid = L.lookupid
INNER JOIN vSchool VS on G.SystemID = VS.SystemID AND I.SchoolID = VS.SchoolID
WHERE G.fiscalyear=@FiscalYear AND G.SystemID = @SystemId AND I.ReimbursementTypeID IN(34,35,36) AND
I.SchoolID=Case When @SchoolId ='0' Then I.SchoolID Else @SchoolId End AND
MONTH(R.FundMonth)=Case When @FundMonth='0' Then MONTH(R.FundMonth) Else CONVERT(INT,@FundMonth) End
AND (I.IsActive=1) AND (R.IsActive=1) AND (G.IsActive=1)
GROUP BY fiscalyear, G.SystemID,I.ReimbursementTypeID
UNION ALL
SELECT I.ReimbursementTypeID,
CAST (SUM(ISNULL(I.AMOUNT,0) - ISNULL(I.OtherAmount,0)) AS NUMERIC(18,2)) AS 'invoiceamt'
FROM grantsystem G
INNER JOIN request R on G.Grantsystemid = R.GrantSystemID
INNER JOIN invoice I on R.requestid = I.requestid
INNER JOIN vlookups L on I.Reimbursementtypeid = L.lookupid
INNER JOIN vSchool VS on G.SystemID = VS.SystemID AND I.SchoolID = VS.SchoolID
WHERE G.fiscalyear=@FiscalYear AND G.SystemID = @SystemId AND I.ReimbursementTypeID IN(37,38,53) AND
I.SchoolID=Case When @SchoolId ='0' Then I.SchoolID Else @SchoolId End AND
MONTH(R.FundMonth)=Case When @FundMonth='0' Then MONTH(R.FundMonth) Else CONVERT(INT,@FundMonth) End
AND (I.IsActive=1) AND (R.IsActive=1) AND (G.IsActive=1)
GROUP BY fiscalyear, G.SystemID,I.ReimbursementTypeID
END
February 5, 2014 at 5:20 pm
If I'm understanding the problem correctly, you might be able to get the field names in your procedure by using joins instead of unions:
SELECT
sub.invoiceamt as Subtotal
grand.invoiceamt as GrandTotal
admin.invoiceamt as AdminTotal
FROM
(
SELECT I.ReimbursementTypeID,
CAST (SUM(ISNULL(I.AMOUNT,0) - ISNULL(I.OtherAmount,0)) AS NUMERIC(18,2)) AS 'invoiceamt'
FROM grantsystem G
INNER JOIN request R on G.Grantsystemid = R.GrantSystemID
INNER JOIN invoice I on R.requestid = I.requestid
INNER JOIN vlookups L on I.Reimbursementtypeid = L.lookupid
INNER JOIN vSchool VS on G.SystemID = VS.SystemID AND I.SchoolID = VS.SchoolID
WHERE G.fiscalyear=@FiscalYear AND G.SystemID = @SystemId AND I.ReimbursementTypeID IN(34,35) AND
I.SchoolID=Case When @SchoolId ='0' Then I.SchoolID Else @SchoolId End AND
MONTH(R.FundMonth)=Case When @FundMonth='0' Then MONTH(R.FundMonth) Else CONVERT(INT,@FundMonth) End
AND (I.IsActive=1) AND (R.IsActive=1) AND (G.IsActive=1)
GROUP BY fiscalyear, G.SystemID,I.ReimbursementTypeID
) as sub
JOIN
(
SELECT I.ReimbursementTypeID,
CAST (SUM(ISNULL(I.AMOUNT,0) - ISNULL(I.OtherAmount,0)) AS NUMERIC(18,2)) AS 'invoiceamt'
FROM grantsystem G
INNER JOIN request R on G.Grantsystemid = R.GrantSystemID
INNER JOIN invoice I on R.requestid = I.requestid
INNER JOIN vlookups L on I.Reimbursementtypeid = L.lookupid
INNER JOIN vSchool VS on G.SystemID = VS.SystemID AND I.SchoolID = VS.SchoolID
WHERE G.fiscalyear=@FiscalYear AND G.SystemID = @SystemId AND I.ReimbursementTypeID IN(34,35,36) AND
I.SchoolID=Case When @SchoolId ='0' Then I.SchoolID Else @SchoolId End AND
MONTH(R.FundMonth)=Case When @FundMonth='0' Then MONTH(R.FundMonth) Else CONVERT(INT,@FundMonth) End
AND (I.IsActive=1) AND (R.IsActive=1) AND (G.IsActive=1)
GROUP BY fiscalyear, G.SystemID,I.ReimbursementTypeID
) as grand
JOIN
(
SELECT I.ReimbursementTypeID,
CAST (SUM(ISNULL(I.AMOUNT,0) - ISNULL(I.OtherAmount,0)) AS NUMERIC(18,2)) AS 'invoiceamt'
FROM grantsystem G
INNER JOIN request R on G.Grantsystemid = R.GrantSystemID
INNER JOIN invoice I on R.requestid = I.requestid
INNER JOIN vlookups L on I.Reimbursementtypeid = L.lookupid
INNER JOIN vSchool VS on G.SystemID = VS.SystemID AND I.SchoolID = VS.SchoolID
WHERE G.fiscalyear=@FiscalYear AND G.SystemID = @SystemId AND I.ReimbursementTypeID IN(37,38,53) AND
I.SchoolID=Case When @SchoolId ='0' Then I.SchoolID Else @SchoolId End AND
MONTH(R.FundMonth)=Case When @FundMonth='0' Then MONTH(R.FundMonth) Else CONVERT(INT,@FundMonth) End
AND (I.IsActive=1) AND (R.IsActive=1) AND (G.IsActive=1)
GROUP BY fiscalyear, G.SystemID,I.ReimbursementTypeID
) as admin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply