reporting-services - ssrs 中的错误。已添加具有相同 key 的项目

Question

我正在 SSRS 中生成报告，其中出现错误。我认为这与我的数据集有关。我想制作多选项参数报告。所以我的查询是。

 IF @QueryParameterType=1
  (
     SELECT
              cast(fddateofissue as date) As IssueDate
            , cast(fddateofcash as  date) as CashDate
            , Substring(fsreckey,1,3)[DIVISION]
            , FNGROUP-100[GROUP]
            , FNCYCLE[CYCLE]
            , SUBSTRING(FSRECKEY,4,4)[LEDGER]
            , FNACCNO[ACCNO]
            , FSNAME[NAME]
            , FSADDRESS[ADDRESS]
            , fsVillage [VILLAGE]
            , fsFeederCode [CIN DATA]
            , fnSecurityAmount [ACD AMOUNT]
            , fnConnLoad[LOAD]
            , fnMtrMultiplier[MTR MULTIPLIER]
            , fnMtrPhase [MTR PHASE]
            , fnCurrReading[CURRENT READING]
            , fnPrevReading[PREVIOUS READING]
            , fnBillUnits[UNIT BILLED]
            , fnAvgUnits [AVERAGE UNIT]
            , fnBillPeriod [BILL PERIOD]
            , fnArrSOP +fnArrFSA +fnArrED +fnArrMCT +fnArrSURC[ARREAR        TOTAL]
            , fnPaidTotal[PAID TOTAL]
            , fdPaidDate
            , fnCurrSOP[CURRENT SOP]
            , fnCurrFSA[CURRENT FSA]
            , fnCurrED[CURRENT ED]
            , fnCurrMCT[CURRENT MCT]
            , fnNetCapPenalty[CAP PENALTY]
            , fnNetMdiPenalty[MDI]
            , fnFixcharges[FIXED CHARGES]
            , fnChrSOP +fnChrFSA +fnChrED +fnChrMCT[CHARGES]
            , fsChrBNO[BNO]
            , fnChrPNO [PNO]
            , fnAlwSOP+fnAlwFSA +fnAlwED +fnAlwMCT[ALLOWANCE]
            , fsAlwBNO [BNO]
            , fnAlwPNO [PNO]
            , fnNetMtrRent[MTR RENT]
            , fnNetTotal[NET TOTAL]
            , fnNetSURC[NET SURCHARGE]
            , fnNetGross[GROSS TOTAL]
            , CASE
                       WHEN fnflgPDCO>2
                                THEN 'PDCO'
                                ELSE 'LIVE'
              END 'STATUS'
            , fsCurrMtrStatus
            , fnMdiReading
            , fnNetMdiPenalty


          FROM
              (


                     SELECT *
                          , NULL AS FDBACKUPDATE
                          , 109  AS FANSESSIONCODE
                     FROM
                            tblLedger
                     UNION ALL
                     SELECT *
                          , NULL AS FDBACKUPDATE
                          , 109  AS FANSESSIONCODE
                     FROM
                            tblPREVIOUSLEDGER
                     UNION ALL
                     SELECT *
                     FROM
                            UH_BACKUP..tblbackupledger

              )
              AS T
              --WHERE T.FSSUBDIV=102 AND T.FNGROUP=102 AND T.FNCATEGORY=6                        and fsledger=2829  and fnaccno =2
     WHERE
              T.FSSUBDIV      =@subdiv
              AND T.FNGROUP   =@group
              AND T.FNCATEGORY=@category
              and t.fsledger  =@ledger
              and t.fnaccno   =@accno
              and t.fddateofissue between @issueDate1 and @issueDate2




            )

             else
         if @QueryParameterType=2
          (
           SELECT
              cast(fddateofissue as date) As IssueDate
            , cast(fddateofcash as  date) as CashDate
            , Substring(fsreckey,1,3)[DIVISION]
            , FNGROUP-100[GROUP]
            , FNCYCLE[CYCLE]
            , SUBSTRING(FSRECKEY,4,4)[LEDGER]
            , FNACCNO[ACCNO]
            , FSNAME[NAME]
            , FSADDRESS[ADDRESS]
            , fsVillage [VILLAGE]
            , fsFeederCode [CIN DATA]
            , fnSecurityAmount [ACD AMOUNT]
            , fnConnLoad[LOAD]
            , fnMtrMultiplier[MTR MULTIPLIER]
            , fnMtrPhase [MTR PHASE]
            , fnCurrReading[CURRENT READING]
            , fnPrevReading[PREVIOUS READING]
            , fnBillUnits[UNIT BILLED]
            , fnAvgUnits [AVERAGE UNIT]
            , fnBillPeriod [BILL PERIOD]
            , fnArrSOP +fnArrFSA +fnArrED +fnArrMCT +fnArrSURC[ARREAR TOTAL]
            , fnPaidTotal[PAID TOTAL]
            , fdPaidDate
            , fnCurrSOP[CURRENT SOP]
            , fnCurrFSA[CURRENT FSA]
            , fnCurrED[CURRENT ED]
            , fnCurrMCT[CURRENT MCT]
            , fnNetCapPenalty[CAP PENALTY]
            , fnNetMdiPenalty[MDI]
            , fnFixcharges[FIXED CHARGES]
            , fnChrSOP +fnChrFSA +fnChrED +fnChrMCT[CHARGES]
            , fsChrBNO[BNO]
            , fnChrPNO [PNO]
            , fnAlwSOP+fnAlwFSA +fnAlwED +fnAlwMCT[ALLOWANCE]
            , fsAlwBNO [BNO]
            , fnAlwPNO [PNO]
            , fnNetMtrRent[MTR RENT]
            , fnNetTotal[NET TOTAL]
            , fnNetSURC[NET SURCHARGE]
            , fnNetGross[GROSS TOTAL]
            , CASE
                       WHEN fnflgPDCO>2
                                THEN 'PDCO'
                                ELSE 'LIVE'
              END 'STATUS'
            , fsCurrMtrStatus
            , fnMdiReading
            , fnNetMdiPenalty


     FROM
              (


                     SELECT *
                          , NULL AS FDBACKUPDATE
                          , 109  AS FANSESSIONCODE
                     FROM
                            tblLedger
                     UNION ALL
                     SELECT *
                          , NULL AS FDBACKUPDATE
                          , 109  AS FANSESSIONCODE
                     FROM
                            tblPREVIOUSLEDGER
                     UNION ALL
                     SELECT *
                     FROM
                            UH_BACKUP..tblbackupledger

              )
              AS T
              --WHERE T.FSSUBDIV=102 AND T.FNGROUP=102 AND T.FNCATEGORY=6 and fsledger=2829  and fnaccno =2
     WHERE
              T.FSSUBDIV      =@subdiv
              AND T.FNGROUP   =@group
              AND T.FNCATEGORY=@category
              and t.fsledger  =@ledger
              and t.fnaccno   =@accno




           )

它在查询设计器中工作正常，但是当我保存此数据集时，它给出

An item with the same key has already been added.

我想知道查询是否有问题。

Answer 1

此错误通常是由于具有相同别名的字段造成的，在您的查询中似乎有 2 个 BNO 和 2 个 PNO 列。可能还有其他列，请检查所有列并确保它们都有不同的别名。