stored-proceduresreporting-servicesssrs-2012
SSRS report: Could not update a list of fields for the query. An item with same key has already been added
I have written a stored procedure as follows:
ALTER PROCEDURE [dbo].[Transaction]
(
@date_start date,
@date_end date
)
AS
BEGIN
with
rntexportbatch as (
Select
fb.BatchId,
rt.TransactionId,
count(*) as line_Count
from CxWarehouse.dbo.FinancialBatchPostingDetail fbpd
INNER JOIN CxWarehouse.dbo.FinancialBatchPosting fb
on (fb.BatchPostingId = fbpd.BatchPostingId and NominalAccount='12000' ) ---and NominalAccount='12000'
INNER JOIN CxWarehouse.dbo.FinancialBatch finb
on (finb.BatchId = fb.BatchId)
INNER JOIN CxWarehouse.dbo.SystemLookup l
on (l.LookupReference = finb.TransactionTypeId and LookupTypeId = 42)
INNER JOIN CxWarehouse.dbo.Financialtransaction ft
on (ft.TransactionId = fbpd.TransactionId)
LEFT JOIN CxWarehouse.dbo.RentTransactionElement rte
on (rte.TransactionElementId = ft.EntityId)
LEFT JOIN CxWarehouse.dbo.RentElement re
ON (re.ElementId = rte.ElementId)
LEFT JOIN CxWarehouse.dbo.RentTransaction rt
ON (rt.TransactionId = rte.TransactionId)
Group by rt.TransactionId,fb.BatchId
)
Select
ast.AssetId,
ast.AssetReference,
ast.[Agreement Description],
ast.CompanyIds,
rntAcc.AccountId,
rntAcc.AccountReference,
rntAcc.PaymentReference,
rt.TransactionId,
rt.AccountId,
rt.TransactionDate,
rt.TransactionTypeId,
[Transaction Type Id Description],
rt.PostingDate,
rt.PeriodNumber,
rt.Description as 'Rent Transaction Description',
rt.Notes,
[ElementId Description] ,
rntPay.BatchReference as 'Import BatchId',
rntPay.[Import Value],
rntexportbatch.BatchId as 'Export BatchId',
final.Value as 'Export Value'
from CxWarehouse.dbo.RentTransaction as rt
LEFT Join (
Select LookupId,
LookupTypeId,
LookupReference,
Description as 'Transaction Type Id Description'
from CxWarehouse.dbo.SystemLookup
where LookupTypeId = 46
) lu
on rt.TransactionTypeId = lu.LookupReference
LEFT Join (
Select
te.TransactionId,
te.TransactionElementId,
te.ElementId,
te.Value,
rntElm.Description as 'ElementId Description'
from CxWarehouse.dbo.RentTransactionElement as te
LEFT Join (
Select * from CxWarehouse.dbo.RentElement
) rntElm
on te.ElementId = rntElm.ElementId
) final
on rt.TransactionId = final.TransactionId
LEFT JOIN (
Select
t.AccountId,
t.AccountReference,
t.PaymentReference,
typ.Description as 'AccountType Description'
from CxWarehouse.dbo.RentAccount as t
left Join CxWarehouse.dbo.RentAccountType as typ
on t.AccountTypeId = typ.AccountTypeId
) rntAcc
on rt.AccountId = rntAcc.AccountId
Left Join (
Select
ast.AssetId,
AssetReference,
rntInf.AccountId,
rntInf.[Agreement Description],
rntInf.CompanyIds
from CxWarehouse.dbo.Asset as ast
left join (
Select
rntAgAs.AgreementAssetId,
rntAgAs.AgreementId,
rntAgAs.AssetId,
rntAgr.AccountId,
rntAgr.[Agreement Description],
rntAgr.CompanyIds
from CxWarehouse.dbo.RentAgreementAsset as rntAgAs
left join (
Select
rntAgmt.AgreementId,
rntAgmt.AgreementReference,
rntAgmt.AgreementTypeId,
rntAgmtTyp.Description as 'Agreement Description',
rntAgmtTyp.CompanyIds,
accEpi.AccountId
from CxWarehouse.dbo.RentAgreement as rntAgmt
LEFT JOIN CxWarehouse.dbo.RentAgreementType rntAgmtTyp
on rntAgmt.AgreementTypeId = rntAgmtTyp.AgreementTypeId
Left Join (
Select
rntAgEp.AgreementEpisodeId,
rntAgEp.AgreementId,
rntAgAc.AccountId
from CxWarehouse.dbo.RentAgreementEpisode as rntAgEp
Left Join CxWarehouse.dbo.RentAgreementAccount as rntAgAc
on rntAgEp.AgreementEpisodeId =rntAgAc.AgreementEpisodeId
) accEpi
on rntAgmt.AgreementId = accEpi.AgreementId
) rntAgr
on rntAgAs.AgreementId = rntAgr.AgreementId
) rntInf
on ast.AssetId = rntInf.AssetId
) ast
on rntAcc.AccountId = ast.AccountId
Left Join (
Select
rp.PaymentId,
rp.BatchReference,
rpd.PaymentDetailId,
rpd.Value as 'Import Value',
GeneratedTransactionId
from CxWarehouse.dbo.RentPayment as rp
left Join CxWarehouse.dbo.RentPaymentDetail rpd
on rp.PaymentId = rpd.PaymentId
left join CxWarehouse.dbo.RentPaymentPosting rpp
on rpd.PaymentDetailId = rpp.PaymentDetailId
) rntPay
on rt.TransactionId = rntPay.GeneratedTransactionId
LEFT JOIN rntexportbatch
on rntexportbatch.TransactionId = rt.TransactionId
where rt.PostingDate between @date_start and @date_end
END
I used that stored procedure in SSRS report. When I clicked on the dataset option of the report and refresh the data set, it gave following error:
I looked at the forum and found that error comes due to two or more columns have same name. I looked at the code and made sure that no column have same name. However, the problem still exist. Could anyone help me where I am making the mistake?
Solution
You have two columns both called AccountID being returned from your query
...
rntAcc.AccountId,
rntAcc.AccountReference,
rntAcc.PaymentReference,
rt.TransactionId,
rt.AccountId,
....
Alias or remove one of these columns.
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?