February 16, 2017 at 10:32 am
I wonder if it is possible to link 5 different data sets to one dataset and if yes, how do I do that?
February 16, 2017 at 10:55 am
It depends on what you mean by "link". The obvious options are JOIN or UNION. If you want something more specific than that, you're going to need to provide more details about what your are trying to accomplish. You can find information about how to do that in the first link in my signature.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 16, 2017 at 11:04 am
Yes I meant to say JOIN. I was going to try to do one big SQL but that doesn't seem to work. I think creating separate datasets for different populations is the easiest way but I'm unsure how to JOIN.
I added a Filter from one dataset to another and when the data came out it was not the correct amount for the specific student ID.
Here is how my data set are set up:
MAIN POPULATION Fields
ExternalID1
Location
FirstName
LastName
STATE (fund source = STATE)
ExternalID1
Amount
PRIVATE (fund source = PRIVATE, fundType in Grant, Scholarship, Other)
ExternalID1
Amount
Federal Grants (fundSource = Federal, fundType in Grant, Scholarship, Other
ExternalID1
Amount
and so on.
Here are the SQLs:
MAIN POPULATIONselect 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)
Stateselect DISTINCT a.externalID1, sum(distinct award_acceptedAmount) as StateAmt
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 Grantselect DISTINCT a.externalID1, sum(distinct award_acceptedAmount) as FedGrantAmt
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;
there are 3 more similar SQLs.
I'm basically creating a letter and putting each of the different values from the different datasets into the letter.
Letter example:
Student ID: MAIN POPULATION.ExternalID1
Dear MAIN POPULATION.FirstName
First few paragraphs of the letter.
Grants are considered ‘gift aid’ and do not need to be repaid. Below is the information about the cumulative grants that you have received while attending MAIN POPULATION.Location, including any accepted grants for the upcoming 2017-2018 academic year.
Here are the grants you are receiving:
State Grant State.StateAmt
Federal Grant FederalGrant.FedGrantAmt
and so forth
February 16, 2017 at 11:50 am
Sorry, didn't realize this was in SSRS. You don't want five separate datasets. You should either be creating two datasets (one for the main report and one for the grant details) or one dataset with all of the information and using group header/footers for the main letter and details (or a subgroup) for the grant details.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 16, 2017 at 12:21 pm
I'm getting ready to go through some tutorials on Report Data Sets. So hopefully I'll be able to figure something out.
Thanks!
Maria
February 17, 2017 at 2:49 pm
maria.lindquist - Thursday, February 16, 2017 12:21 PMI'm getting ready to go through some tutorials on Report Data Sets. So hopefully I'll be able to figure something out.Thanks!
Maria
I took a quick look at your queries, and there appears to be some serious commonality that can be exploited so as to combine your data into a single dataset.
Take a look at the following query and see if you can see how I put things together:
WITH MAIN_POPULATION AS (
SELECT DISTINCT a.externalID1, d.name, c.FirstName, c.LastName
FROM dbo.dataExtract_AwardDisbursement_View AS a
INNER JOIN dbo.dataExtract_Fund_Config_View AS b
ON a.locationExternalID = b.fund_locationExternalId
AND a.fund_externalID1 = b.fund_externalID
INNER JOIN dbo.dataExtract_Student_View AS c
ON a.externalID1 = c.externalID1
INNER JOIN dbo.dataExtract_Location_Config_View AS d
ON a.locationExternalId = d.locationExternalId
WHERE a.awardPeriod_federalAwardYear = @AwardYear
AND a.award_awardStatusCode = 'ACCEPTED'
AND a.award_acceptedAmount > 0
AND EXISTS (
SELECT 1 --'y'
FROM dbo.dataExtract_CourseData_View AS y
WHERE y.externalId1 = a.externalId1
AND y.externalTransferYN = '0'
AND y.internalTransferYN = '0'
AND y.startDate >= @CrseStart
AND y.withdrawalDate IS NULL
)
),
STATE_FEDERAL_PRIVATE AS (
SELECT a.externalID1,
b.fund_fundSource AS FundSource,
SUM(award_acceptedAmount) AS Amount
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'
OR
(
b.fund_fundSource IN ('Federal', 'Private')
AND
b.fund_fundType IN ('Grant','Scholarship','Other')
)
)
AND a.awardPeriod_federalAwardYear = @AwardYear
AND a.award_acceptedAmount > 0
AND EXISTS (
SELECT 1 --'y'
FROM dataExtract_CourseData_View AS 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
)
SELECT MP.*, S.Amount AS StateAmt, F.Amount AS FedGrantAmt, P.Amount AS PrivGrantAmt
FROM MAIN_POPULATION AS MP
OUTER APPLY (
SELECT *
FROM STATE_FEDERAL_PRIVATE AS SFP
WHERE SFP.externalID1 = MP.externalID1
AND SFP.FundSource = 'State'
) AS S
OUTER APPLY (
SELECT *
FROM STATE_FEDERAL_PRIVATE AS SFP
WHERE SFP.externalID1 = MP.externalID1
AND SFP.FundSource = 'Federal'
) AS F
OUTER APPLY (
SELECT *
FROM STATE_FEDERAL_PRIVATE AS SFP
WHERE SFP.externalID1 = MP.externalID1
AND SFP.FundSource = 'Private'
) AS P
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply