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

Need to mingle different select statements into one (Stored procedure) Expand / Collapse
Author
Message
Posted Friday, April 25, 2014 8:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 1:06 PM
Points: 55, Visits: 109
Hello,
I am creating SSRS report I am trying to call stored procedure but it has 3 select statements how can I do into one because ssrs will take only first dataset.

I can do with different datasets like 3 select statements I can make 3 datasets but its grouping with system so if it is grouping with some field I think all the columns should come from same dataset.

here is the stored procedure

select distinct SystemID, SystemName from vsystemschool
where schoolyear = @FiscalYear

SELECT SystemID,ProgramID,
CASE WHEN ProgramName='M' THEN 'L' ELSE ProgramName END AS 'ProgramName',
AdjustmentsSequenceNo,sum(Paid) as Paid,sum(Free) as Free,sum(Reduced) as Reduced,sum(paidamount),sum(freeamount),sum(reducedamount),
sum(paidamount)+sum(freeamount)+sum(reducedamount) as dollars
from vDE107sForAllSystemsForOneMonth SCH
where SCH.FiscalYear = @FiscalYear and SCH.ClaimMonth =@ClaimMonth
and AdjustmentsSequenceNo=0
GROUP BY systemID,programID,ProgramName,AdjustmentsSequenceNo
order by AdjustmentsSequenceNo

SELECT SystemID,ProgramID,
CASE WHEN ProgramName='M' THEN 'L' ELSE ProgramName END AS 'ProgramName',
sum(Paid) as Paid,sum(Free) as Free,sum(Reduced) as Reduced,IsNull(sum(paidamount),0),IsNull(sum(freeamount),0),IsNull(sum(reducedamount),0),
isNull(sum(paidamount)+sum(freeamount)+sum(reducedamount),0) as dollars
from ForAllSystemsForOneMonth SCH
where SCH.FiscalYear = @FiscalYear and SCH.ClaimMonth =@ClaimMonth
and AdjustmentsSequenceNo>0
GROUP BY systemID,programID,ProgramName
order by systemID,ProgramID

END
Post #1565126
Posted Saturday, April 26, 2014 1:41 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 9:46 PM
Points: 746, Visits: 4,793
Are you trying to create a single dataset to use in your report? If so, it looks like you can union the results together.

SELECT...
FROM v_SomeView
WHERE...
UNION ALL
SELECT...
FROM v_SomeOtherView
WHERE...

and then just pass your params in all the WHERE clauses. (they would use the same parameters but map to the proper fields).

Or did you mean return 3 distinct result sets?
Post #1565362
Posted Saturday, April 26, 2014 1:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:36 PM
Points: 7,161, Visits: 13,235
How about using UNION ALL to merge the three results into one?



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1565364
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse