February 16, 2017 at 8:59 am
I'm receiving the following error message when I try to Refresh Fields after adding a query in the Dataset:
Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. Well I took the query syntax and ran it in SQL Server Management Studio without issues:
Here is the SQL:
;with studentData as (
select distinct sv.studentId,sv.externalId1,sv.externalId2,sv.locationExternalId,sv.siteExternalId,sv.FirstName,sv.LastName
from dataExtract_Student_View sv
join (select cdv.studentid
from dataExtract_CourseData_View cdv
where cdv.externalTransferYN = '0'
and cdv.internalTransferYN = '0'
and cdv.startDate >= @CrseStart
and cdv.withdrawalDate is null) courseData
on courseData.studentId = sv.studentId
join dataExtract_Award_View_v002 av
on av.studentid = sv.studentid
and av.deleted = 0
and av.awardStatusCode = 'ACCEPTED'
and av.acceptedAmount > 0
join dataExtract_LoanPeriod_View_v001 lp
on lp.loanPeriodId = av.loanPeriodId
and lp.deleted = 0
and lp.federalAwardYear = @federalAwardYear
), stateData as (
select studentId,fund_name,awardId,sum(amount) sumAmount, sum(netAmount) sumNetAmount, sum(paidAmount) sumPaidAmount
from (select distinct sv.studentId,fcv.fund_name,av.awardId,dv.disbursementId,dv.disbursementstatuscode,dv.amount,dv.netamount,dv.paidamount
from dataExtract_Student_View sv
join studentData sd
on sd.studentId = sv.studentId
join dataExtract_Award_View_v002 av
on av.studentid = sv.studentid
and av.deleted = 0
and av.awardStatusCode = 'ACCEPTED'
and av.acceptedAmount > 0
join dataExtract_Fund_Config_View fcv
on fcv.fundId = av.fundId
and fcv.fund_fundSource = 'State'
join dataExtract_LoanPeriod_View_v001 lp
on lp.loanPeriodId = av.loanPeriodId
and lp.deleted = 0
and lp.federalAwardYear = @federalAwardYear
join dataExtract_AwardDisbursement_View_v001 adv
on adv.awardId = av.awardId
and adv.deleted = 0
join dataExtract_Disbursement_View_v001 dv
on dv.awardDisbursementId = adv.awardDisbursementId
and dv.deleted = 0
) data
group by studentId,fund_name,awardId
), privateData as (
select studentId,fund_name,awardId,sum(amount) sumAmount, sum(netAmount) sumNetAmount, sum(paidAmount) sumPaidAmount
from (select distinct sv.studentId,fcv.fund_name,av.awardId,dv.disbursementId,dv.disbursementstatuscode,dv.amount,dv.netamount,dv.paidamount
from dataExtract_Student_View sv
join studentData sd
on sd.studentId = sv.studentId
join dataExtract_Award_View_v002 av
on av.studentid = sv.studentid
and av.deleted = 0
and av.awardStatusCode = 'ACCEPTED'
and av.acceptedAmount > 0
join dataExtract_Fund_Config_View fcv
on fcv.fundId = av.fundId
and fcv.fund_fundSource = 'Private'
and fcv.fund_fundType in ('Grant','Scholarship','Other')
join dataExtract_LoanPeriod_View_v001 lp
on lp.loanPeriodId = av.loanPeriodId
and lp.deleted = 0
and lp.federalAwardYear = @federalAwardYear
join dataExtract_AwardDisbursement_View_v001 adv
on adv.awardId = av.awardId
and adv.deleted = 0
join dataExtract_Disbursement_View_v001 dv
on dv.awardDisbursementId = adv.awardDisbursementId
and dv.deleted = 0
) data
group by studentId,fund_name,awardId
), instGrantsData as (
select studentId,fund_name,awardId,sum(amount) sumAmount, sum(netAmount) sumNetAmount, sum(paidAmount) sumPaidAmount
from (select distinct sv.studentId,fcv.fund_name,av.awardId,dv.disbursementId,dv.disbursementstatuscode,dv.amount,dv.netamount,dv.paidamount
from dataExtract_Student_View sv
join studentData sd
on sd.studentId = sv.studentId
join dataExtract_Award_View_v002 av
on av.studentid = sv.studentid
and av.deleted = 0
and av.awardStatusCode = 'ACCEPTED'
and av.acceptedAmount > 0
join dataExtract_Fund_Config_View fcv
on fcv.fundId = av.fundId
and fcv.fund_fundSource = 'Institutional'
and fcv.fund_fundType in ('Grant','Scholarship','Other')
join dataExtract_LoanPeriod_View_v001 lp
on lp.loanPeriodId = av.loanPeriodId
and lp.deleted = 0
and lp.federalAwardYear = @federalAwardYear
join dataExtract_AwardDisbursement_View_v001 adv
on adv.awardId = av.awardId
and adv.deleted = 0
join dataExtract_Disbursement_View_v001 dv
on dv.awardDisbursementId = adv.awardDisbursementId
and dv.deleted = 0
) data
group by studentId,fund_name,awardId
), fedGrantsData as (
select studentId,fund_name,awardId,sum(amount) sumAmount, sum(netAmount) sumNetAmount, sum(paidAmount) sumPaidAmount
from (select distinct sv.studentId,fcv.fund_name,av.awardId,dv.disbursementId,dv.disbursementstatuscode,dv.amount,dv.netamount,dv.paidamount
from dataExtract_Student_View sv
join studentData sd
on sd.studentId = sv.studentId
join dataExtract_Award_View_v002 av
on av.studentid = sv.studentid
and av.deleted = 0
and av.awardStatusCode = 'ACCEPTED'
and av.acceptedAmount > 0
join dataExtract_Fund_Config_View fcv
on fcv.fundId = av.fundId
and fcv.fund_fundSource = 'Federal'
and fcv.fund_fundType in ('Grant','Scholarship','Other')
join dataExtract_LoanPeriod_View_v001 lp
on lp.loanPeriodId = av.loanPeriodId
and lp.deleted = 0
and lp.federalAwardYear = @federalAwardYear
join dataExtract_AwardDisbursement_View_v001 adv
on adv.awardId = av.awardId
and adv.deleted = 0
join dataExtract_Disbursement_View_v001 dv
on dv.awardDisbursementId = adv.awardDisbursementId
and dv.deleted = 0
) data
group by studentId,fund_name,awardId
)
select distinct sdv.studentId,sdv.externalId1,sdv.externalId2,sdv.locationExternalId,sdv.siteExternalId,sdv.FirstName,sdv.LastName
,case when sd.studentId is not null then 1 else 0 END hasStateYN,sd.sumAmount,sd.sumNetAmount,sd.sumPaidAmount
,case when pd.studentId is not null then 1 else 0 END hasPrivateYN,pd.sumAmount,pd.sumNetAmount,pd.sumPaidAmount
,case when igd.studentId is not null then 1 else 0 END hasInstGrantsYN,igd.sumAmount,igd.sumNetAmount,igd.sumPaidAmount
,case when fgd.studentId is not null then 1 else 0 END hasFedGrantsYN,fgd.sumAmount,fgd.sumNetAmount,fgd.sumPaidAmount
from studentData sdv
left join stateData sd on sd.studentId = sdv.studentId
left join privateData pd on pd.studentId = sdv.studentId
left join instGrantsData igd on igd.studentId = sdv.studentId
left join fedGrantsData fgd on fgd.studentId = sdv.studentId
February 16, 2017 at 9:40 am
Did you set your parameters on the report?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 16, 2017 at 9:48 am
I don't typically have to do that. When I click on Refresh Fields it adds my fields and parameters. I added the parameters and clicked on Refresh Fields button. Still the same error.
February 16, 2017 at 10:09 am
Does your parameter have a default set? If not, and you don't enter anything, the report won't run because the stored procedure can't return any data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply