Solved

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

Posted on 2011-02-18
10
1,218 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
Comment Utility
Sorry, You have to gives us more info than that.
What key are you talking about?
0
 

Author Comment

by:sqlcurious
Comment Utility
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
Comment Utility
Can you post your query so we can see if there is a problem in the way it is written.
0
 

Author Comment

by:sqlcurious
Comment Utility

 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
Comment Utility
I mat not have this just right, but go ahead and run it
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 27

Expert Comment

by:planocz
Comment Utility


-- 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
i figured it out myself
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now