Dynamics Community Forum Thread Details

Dataset contains a definition for the field, the filed is missing from returned result

Hi All,

When I run SSRS, to view the table, one filed is missing data and blank, I see the following message :

Warning [rsMissingFieldInDataSet] The dataset 'ds_GLDetail' contains a definition for the Field 'Associated_Invoice_Description'. This field is missing from the returned result set from the data source.

Warning [rsErrorReadingDataSetField] The dataset 'ds_GLDetail' contains a definition for the Field 'Associated_Invoice_Description'. The data extension returned an error during reading the field. There is no data for the field at position 10.

I am using Visual Studio to editing SSRS report.

I have inserted two new fields,

one is customer name

one is invoice description.

My SQL query is running correctly in sql server. The query can show the whole data.

Can someone advise how I should work on it ?

SELECT
GLD.PA_gldetailId AS 'GL Detail Transaction GUID',
GLD.PA_transactiondate AS 'GL Detail Transaction Date',
GLD.PA_batchnumber AS 'GL Detail Transaction Batch Number',
GLDCoA.PA_AccountNumber As 'GL Detail Transaction Target GL',
GLD.PA_amountdebit AS 'GL Detail Transaction Debit',
GLD.PA_amountcredit AS 'GL Detail Transaction Credit',
I.InvoiceNumber AS 'Associated Invoice Number',
I.PA_InvoiceDate AS 'Associated Invoice Date',
I.CustomeridName AS 'Assocoated Invoice Customer',
Invde.Description AS 'Asscoiated Invoice Description',
I.TotalLineItemAmount 'Associated Invoice Current Net',
I.TotalTax AS 'Associated Invoice Current Tax',
I.TotalAmount AS 'Associated Invoice Current Gross'
FROM
PA_gldetail AS GLD
LEFT OUTER JOIN Invoice AS I ON I.InvoiceId = GLD.pa_invoiceid
LEFT OUTER JOIN PA_ChartofAccounts AS GLDCoA ON GLD.pa_glaccountid = GLDCoA.PA_ChartofAccountsId
LEFT OUTER JOIN InvoiceDetail AS Invde ON Invde.InvoiceId = GLD.pa_invoiceid
WHERE
YEAR(GLD.PA_transactiondate) = @param_Year
AND MONTH(GLD.PA_transactiondate) = @param_Month
ORDER BY
3 ASC, 2 ASC, 4 ASC

SELECT
DISTINCT YEAR(pa_transactiondate) AS Year
FROM
PA_GLDetail
WHERE
StateCode = 0
ORDER BY
1 ASC

SELECT
DISTINCT MONTH(pa_transactiondate) AS MonthValue,
CASE
WHEN MONTH(pa_transactiondate) = 1 THEN 'January'
WHEN MONTH(pa_transactiondate)= 2 THEN 'February'
WHEN MONTH(pa_transactiondate)= 3 THEN 'March'
WHEN MONTH(pa_transactiondate)= 4 THEN 'April'
WHEN MONTH(pa_transactiondate)= 5 THEN 'May'
WHEN MONTH(pa_transactiondate)= 6 THEN 'June'
WHEN MONTH(pa_transactiondate)= 7 THEN 'July'
WHEN MONTH(pa_transactiondate)= 8 THEN 'August'
WHEN MONTH(pa_transactiondate)= 9 THEN 'September'
WHEN MONTH(pa_transactiondate)= 10 THEN 'October'
WHEN MONTH(pa_transactiondate)= 11 THEN 'November'
WHEN MONTH(pa_transactiondate)= 12 THEN 'December'
ELSE 'ERROR'
END AS MonthLabel
FROM
PA_GLDetail
WHERE
StateCode = 0
AND YEAR(pa_transactiondate) = @param_Year
ORDER BY
1 ASC