Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

An item with the same key has already been added in ssrs 2008

Posted on 2011-02-18
10
1,223 Views
Last Modified: 2012-05-11
I GOT THIS ERROR WHILE DEVELOPING REPORTS IN SSRS 2008R2
0
Comment
Question by:sqlcurious
  • 5
  • 5
10 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 34930413
Sorry, You have to gives us more info than that.
What key are you talking about?
0
 

Author Comment

by:sqlcurious
ID: 34931516
it is related to ssrs 2008 r2 I  tried to create a dataset in the query window.

dbsp_report_projectdetailcost

@leetingdatefrom,
@lettingdateto,
@customername,
@divisionname

and after clicking ok,it prompts the message an item with the same key has already been added
0
 
LVL 27

Expert Comment

by:planocz
ID: 34933357
Can you post your query so we can see if there is a problem in the way it is written.
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:sqlcurious
ID: 34933385

 I wrote the stored proc and passed the variables mentioned above.whenever you write the stored proc and give the parameters name it should populate the fields that are used for reporting.It works fine for other proc.

-- exec DBSP_Report_LettingDate '01/01/2006','01/01/2011','1',''
-- Description:      <Procedure for Report by Letting Date >
-- =============================================
CREATE  PROCEDURE [dbo].[DBSP_Report_LettingDate]
      (
      @LettingDateFrom datetime,
      @LettingDateTo datetime,
      @ProjectTxDot int =2,
      @CustomerName varchar(50),
      @DivisionName varchar(250) = NULL
      
)
      
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
 Set @SQL= 'SELECT
 CU.CustomerID,
 CU.CustomerName,
P.ProjectID ,
P.Projectname,
p.ProjectNumber,
P.ProjectCountyID,
convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,
P.projectTxdot,
D.Divisionname As DivisionName,
P.projectCountyID As CountyID,
C.CountyName As CountyName,
CU.CustomerName As CustomerName,
CU.customerID,
QI.QuoteItemQuantity as Quantity,
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPrice As TotalPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,      
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS QtyItemTotalPrice,
(QI.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,
QI.QuoteItemWeight AS ItemWeight,
QI.QuoteItemMarkupPercent AS MarkupPercent,
Q.QuoteFreightRate as FreightRate,
Q.QuoteNumberOfTrucks,
Q.QuoteSurcharge1,
Q.QuoteSurcharge2,
Q.QuoteSurcharge3,
((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight

        FROM TBL_Ebid_Project P
            INNER JOIN  TBL_Quotes1 Q ON P.ProjectID = Q.ProjectID
            inner   JOIN  TBL_County C ON C.CountyID = P.ProjectCountyID
            inner JOIN  TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
            inner  JOIN  TBL_Division D ON D.createdby = Q.QuoteCreatedby
            --INNER JOIN  TBL_CustomerQuotes CU ON CU.QuoteID = Q.QuoteID      
            inner join tbl_customer cu on cu.customercreatedby=Q.QuoteCreatedby'
            
      
            
If @LettingDateFrom Is Not Null
      Begin
            Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@LettingDateFrom As Varchar(30))+''''
      End

If @LettingDateTo Is Not Null
      Begin
            Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@LettingDateTo As Varchar(30))+''''
      End
      

if @DivisionName <> ''

 begin
 set @SQL= @Sql + ' And D.DivisionName LIKE ''%'+@DivisionName+'%'''  
end

if (@projectTxDot <> 2 )
 begin  
  SET @SQL =  @SQL + ' and p.ProjectTxDot =  '+ convert(varchar(5), @projectTxDot)
end

IF  @CustomerName <> ''
begin
 set @sql=@sql + ' And CU.CustomerName LIKE ''%'+@CustomerName+'%'''  
end

print @sql
exec (@sql)

END
0
 
LVL 27

Expert Comment

by:planocz
ID: 34935335
I mat not have this just right, but go ahead and run it
0
 
LVL 27

Expert Comment

by:planocz
ID: 34935340


-- exec DBSP_Report_LettingDate '01/01/2006','01/01/2011','1',''
-- Description:      <Procedure for Report by Letting Date >
-- =============================================
CREATE  PROCEDURE [dbo].[DBSP_Report_LettingDate]
      (
      @StartDate      datetime,
      @EndDate            datetime,
      @ProjectTxDot int =2,
      @CustomerName varchar(50),
      @DivisionName varchar(250) = NULL    
)
     
AS
Set NOCOUNT ON

If RTRIM(isnull(@StartDate,'')) = ''
Begin
      Select @StartDate = '%'
End
Else
Begin
      Select @StartDate = '%' + @StartDate + '%'
End
If RTRIM(isnull(@EndDate,'')) = ''
Begin
      Select @EndDate = '%'
End
Else
Begin
      Select @EndDate = '%' + @EndDate + '%'
End
If RTRIM(isnull(@DivisionName,'')) = ''
Begin
      Select @DivisionName = '%'
End
Else
Begin
      Select @DivisionName = '%' + @DivisionName + '%'
End
If RTRIM(isnull(@CustomerName,'')) = ''
Begin
      Select @CustomerName = '%'
End
Else
Begin
      Select @CustomerName = '%' + @CustomerName + '%'
End

BEGIN
SELECT
 CU.CustomerID,
 CU.CustomerName,
P.ProjectID ,
P.Projectname,
p.ProjectNumber,
P.ProjectCountyID,
convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,
P.projectTxdot,
D.Divisionname As DivisionName,
P.projectCountyID As CountyID,
C.CountyName As CountyName,
CU.CustomerName As CustomerName,
CU.customerID,
QI.QuoteItemQuantity as Quantity,
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPrice As TotalPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,      
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS QtyItemTotalPrice,
(QI.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,
QI.QuoteItemWeight AS ItemWeight,
QI.QuoteItemMarkupPercent AS MarkupPercent,
Q.QuoteFreightRate as FreightRate,
Q.QuoteNumberOfTrucks,
Q.QuoteSurcharge1,
Q.QuoteSurcharge2,
Q.QuoteSurcharge3,
((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight

FROM TBL_Ebid_Project P
INNER JOIN  TBL_Quotes1 Q
ON P.ProjectID = Q.ProjectID
INNER JOIN  TBL_County C
ON C.CountyID = P.ProjectCountyID
INNER JOIN  TBL_QuoteItems QI
ON Q.QuoteID = QI.QuoteID
INNER JOIN  TBL_Division D
ON D.createdby = Q.QuoteCreatedby
INNER JOIN  tbl_customer cu
ON cu.customercreatedby = Q.QuoteCreatedby
           
WHERE p.projectstartdate >=  @StartDate  
      AND      p.projectstartdate <= @EndDate
      AND      D.DivisionName like   @DivisionName  
      AND  p.ProjectTxDot = + convert(varchar(5), @projectTxDot)
      AND CU.CustomerName like  @CustomerName  
      ORDER BY
            CU.CustomerName
 END  
0
 

Author Comment

by:sqlcurious
ID: 34938058
The above proc runs But When i execute the following statement it does not give me any results

exec DBSP_Report_LettingDate '01/01/2006','01/01/2011','1','','division description 2,division description 4
However it gives me the result when i pass only single value in @DIvisionName parameter

My problem is i want the result to be displayed both

Division description 2 and Division Description 4

When i pass two values in the DivisionName parameter

Can you modify the proc so that i can get the desired result
0
 
LVL 27

Expert Comment

by:planocz
ID: 34939648
are you talking about the division name or a different field of division description?
you do not have a division description listed in your SP.
0
 

Accepted Solution

by:
sqlcurious earned 0 total points
ID: 34939888
i am talking about 'division name' column
DivisionName is passed as a paramter.
You can see in the 'DivisionName' parameter several values has been passed.Instead of displaying only the passed values it is displaying all.

Another Problem in the above procedure.I got  following error

'Conversion failed when converting datetime from character string'
in the statement

0
 

Author Closing Comment

by:sqlcurious
ID: 34995563
i figured it out myself
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question