Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to call different results from stored procedure to SSRS report Expand / Collapse
Author
Message
Posted Wednesday, February 5, 2014 10:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 3:46 PM
Points: 76, Visits: 156
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

Post #1538272
Posted Wednesday, February 5, 2014 12:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:10 PM
Points: 125, Visits: 270
Did you consider setting up the stored procedure to return a 3 field table, with fields Subtotal, GrandTotal, and AdminTotal?

Post #1538335
Posted Wednesday, February 5, 2014 1:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 3:46 PM
Points: 76, Visits: 156
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
Post #1538349
Posted Wednesday, February 5, 2014 5:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:58 PM
Points: 184, Visits: 8,369
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

Post #1538421
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse