Based on the information you have provided I would do this:
SELECT
SCH.SystemID,
SCH.ProgramID,
SS.SystemName,
CASE WHEN SCH.ProgramName = 'M' THEN 'L'
ELSE SCH.ProgramName
END AS 'ProgramName',
SCH.AdjustmentsSequenceNo,
SUM(SCH.Paid) AS Paid,
SUM(SCH.Free) AS Free,
SUM(SCH.Reduced) AS Reduced,
SUM(SCH.paidamount),
SUM(SCH.freeamount),
SUM(SCH.reducedamount),
SUM(SCH.paidamount) + SUM(freeamount) + SUM(reducedamount) AS dollars
FROM
[dbo].[vForAllSystemsForOneMonth] SCH JOIN
[dbo].[vSystemSchool] AS SS ON SCH.SystemID = SS.SystemID
WHERE
SCH.FiscalYear = @FiscalYear AND
SCH.ClaimMonth = @ClaimMonth AND
SCH.AdjustmentsSequenceNo >= 0
GROUP BY
SCH.systemID,
SS.SystemName,
SCH.programID,
SCH.ProgramName,
SCH.AdjustmentsSequenceNo
ORDER BY
SCH.AdjustmentsSequenceNo;
Then I would let SSRS do the higher level grouping based only on SystemID, ProgramID, and ProgramName. I may be misunderstanding why queries 2 and 3 in your example are needed, but I think you can bring the data across at the granularity that you need (SystemID, ProgramID, ProgramName, AdjustmentSequenceNo) and then handle the rest using SSRS grouping and filtering.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question