April 25, 2014 at 8:41 am
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
April 26, 2014 at 1:41 pm
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?
April 26, 2014 at 1:47 pm
How about using UNION ALL to merge the three results into one?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply