SSRS R2 reporting

i was trying to pass the multiple values from the drop down selected list in SSRS  to get the reports but it throws an error like below when i select more than one check box
procedure or function dbsp_report_projectdetailcost has two many arguments specified.

my proc is as below.Similarly it does not give the desired output when i pass one and more than one values in the parameter @DivisionName
IT displays all the records which is not desirable





-- exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName='Division description 2,DIVISION DESCRIPTION 4,DIVISION DESCRIPTION 11'
-- exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='',@ProjectLettingDateTo=''
-- =============================================
-- Create date: <01/21/2011>
-- Description:      <stored procedure to find total Project Costs>
-- =============================================
ALTER    PROCEDURE [dbo].[DBSP_Report_GetProjectDetailCost1]
(      
      --@projectID int =null,
      @ProjectNumber VARCHAR(50)=Null,
      @ProjectControlNumber VARCHAR(50)= Null,
      @ProjectLettingDateFrom DATETIME,
      @ProjectLettingDateTo DATETIME,
      @ProjectName varchar(50) = Null,
      @DivisionName VARCHAR(250) = Null
)      
AS      
begin
--DECLARE @SQL VARCHAR(8000)
--declare @param  varchar(50)
 
 

   SELECT
--P.ProjectID,
distinct P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
 convert(varchar(10),p.ProjectStartDate,110)as ProjectStartDate,
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.QuoteItemMarkupPercent AS MarkupPercent,
D. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TotalFreight

FROM TBL_EBID_Project P

inner  join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner  JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner  join TBL_Division D ON D.CreatedBy = Q.QuoteCreatedBy
inner  join TBL_County C ON P.ProjectCountyID = C.CountyID
where
P.ProjectStartDate >= @ProjectLettingDateFrom
and P.ProjectStartDate<=@projectLettingDateTo


-- P.ProjectNumber like @ProjectNumber
--and
--P.ProjectControlNumber like @ProjectControlNumber
--and
And D.DivisionName in(Select d.divisionname from dbo.fneBid_SplitValue(@DivisionName,','))

--WHERE D.DivisionName IN (select D.DivisionName from dbo.fneBid_SplitValue(@DivisionName='ALL'))

--if @projectname  <> ''
--begin
-- set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%'''
--end

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

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

--if @DivisionName <> ''

-- begin
 --set @SQL= @Sql + ' And D.DivisionName LIKE ''%'+@DivisionName+'%'''
 --end
 
---- begin
---- SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','','')'
----end
 
-- --set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('''+@DivisionName+''','','')) '
 
 
-- --end
 
-- --set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%'+@DivisionName+'%'','',''))'

----set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('+@DivisionName+','',''))'  

-- --SET @SQL= @Sql + ' AND (exists(SELECT * FROM dbo.fneBid_SplitValue('+@DivisionName))'
----end
-- --set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%'+@DivisionName+'%'',",")) '
-- --set @SQL= @Sql + ' And D.DivisionID in (select  *from dbo.fneBid_SplitValue(@DivisionName)) '
----end


--print @sql
--exec (@sql)

END
sqlcuriousAsked:
Who is Participating?
 
MIKESoftware Solutions ConsultantCommented:
Use this type of format when using @Parameters and the "IN"

WHERE D.DivisionName IN (@DivisionName)

SSRS will know how to handle the "IN" requirement.....

Hope it helps...

0
 
sqlcuriousAuthor Commented:
artially complete
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.