SSRS R2 reporting

Posted on 2011-02-20
Last Modified: 2012-05-11
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
--declare @param  varchar(50)

distinct P.Projectname,
 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,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TotalFreight


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
P.ProjectStartDate >= @ProjectLettingDateFrom
and P.ProjectStartDate<=@projectLettingDateTo

-- P.ProjectNumber like @ProjectNumber
--P.ProjectControlNumber like @ProjectControlNumber
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  <> ''
-- set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%'''

--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+'%'''
---- begin
---- 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+''','','')) '
-- --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))'
-- --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)) '

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

Question by:sqlcurious
LVL 17

Accepted Solution

MIKE earned 500 total points
ID: 34939742
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...


Author Closing Comment

ID: 35070897
artially complete

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2008 using rectangles with text boxes 6 68
SSRS Expression 2 38
SSRS and SQL server 4 60
performance query 4 20
Written by Valentino Vranken. Introduction: In a previous article ( I announced that I would writ…
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

785 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