Solved

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

Posted on 2011-02-18
10
1,224 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
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…

733 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