February 15, 2017 at 8:37 am
I am creating a letter type report and I need to get values from 5 different datasets that are linked to a MAIN POPULATION data set.
Here are some of the dataset queries:
MAIN POPULATION:select DISTINCT a.externalID1, d.name, c.FirstName, c.LastName
from dbo.dataExtract_AwardDisbursement_View a, dbo.dataExtract_Fund_Config_View b, dbo.dataExtract_Student_View c, dbo.dataExtract_Location_Config_View d
where a.fund_externalID1 = b.fund_externalID
and a.locationExternalId = d.locationExternalId
and a.externalID1 = c.externalID1
and a.locationExternalID = b.fund_locationExternalId
and a.awardPeriod_federalAwardYear = @AwardYear
and a.award_awardStatusCode = 'ACCEPTED'
and a.award_acceptedAmount > 0
and exists (select 'y' from dataExtract_CourseData_View y
where y.externalId1 = a.externalId1
and y.externalTransferYN = '0'
and y.internalTransferYN = '0'
and y.startDate>= @CrseStart
AND y.withdrawalDate is null)
State Grantsselect DISTINCT a.externalID1, sum(distinct award_acceptedAmount)
from dbo.dataExtract_AwardDisbursement_View a, dbo.dataExtract_Fund_Config_View b
where a.fund_externalID1 = b.fund_externalID
and a.locationExternalID = b.fund_locationExternalId
and b.fund_fundSource = 'State'
and a.awardPeriod_federalAwardYear = @AwardYear
and a.award_acceptedAmount > 0
and exists (select 'y' from dataExtract_CourseData_View y
where y.externalId1 = a.externalId1
and y.externalTransferYN = '0'
and y.internalTransferYN = '0'
and y.startDate >= @CrseStart
AND y.withdrawalDate is null)
group by a.externalId1;
Federal Grantsselect DISTINCT a.externalID1, sum(distinct award_acceptedAmount)
from dbo.dataExtract_AwardDisbursement_View a, dbo.dataExtract_Fund_Config_View b
where a.fund_externalID1 = b.fund_externalID
and a.locationExternalID = b.fund_locationExternalId
and b.fund_fundSource = 'Federal'
and b.fund_fundType in ('Grant','Scholarship','Other')
and a.awardPeriod_federalAwardYear = @AwardYear
and a.award_acceptedAmount > 0
and exists (select 'y' from dataExtract_CourseData_View y
where y.externalId1 = a.externalId1
and y.externalTransferYN = '0'
and y.internalTransferYN = '0'
and y.startDate >= @CrseStart
AND y.withdrawalDate is null)
group by a.externalId1;
Federal Loanselect DISTINCT a.externalID1, sum(distinct award_acceptedAmount), case when (sum(award_acceptedAmount)*.0106<40.00) then Round((log(2,Power((1-(SUM(award_acceptedAmount)*.000104)),-1))/Log(2,1.00417))*40,-1)-sum(award_acceptedAmount)
from dbo.dataExtract_AwardDisbursement_View a, dbo.dataExtract_Fund_Config_View b
where a.fund_externalID1 = b.fund_externalID
and a.locationExternalID = b.fund_locationExternalId
and b.fund_fundSource = 'Federal'
and b.fund_fundType in ('Loan')
and a.awardPeriod_federalAwardYear = @AwardYear
and a.award_acceptedAmount > 0
and exists (select 'y' from dataExtract_CourseData_View y
where y.externalId1 = a.externalId1
and y.externalTransferYN = '0'
and y.internalTransferYN = '0'
and y.startDate >= @CrseStart
AND y.withdrawalDate is null)
group by a.externalId1;
March 20, 2017 at 7:41 am
Just guessing what you question is here, as it looks like your question may have gotten cut off.
1) Is it possible to combine these into one dataset? (I didn't look close enough at your queries to see if it looked impossible). This would probably be the easiest way to create your letter.
2) Is there a field in common between the datasets that you could use the SSRS Lookup() function to find the corresponding records in the other datasets?
3) You could potentially do some preprocessing where you populate a table/temporary table to make you reporting more straight-forward.
Just some ideas.
I hope this helps,
Rob
March 20, 2017 at 11:06 am
Hi Maria, just use UNION ALL to join the queries of the 3 parts (State Grants, Federal Grants, Federal Loan) into one query.
For SSRS, it's always best to use UNION ALL than trying to link the 3 queries via JOIN.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply