?
Solved

SSRS R2 reporting

Posted on 2011-02-20
2
Medium Priority
?
374 Views
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
)      
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
0
Comment
Question by:sqlcurious
2 Comments
 
LVL 17

Accepted Solution

by:
MIKE earned 1500 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...

0
 

Author Closing Comment

by:sqlcurious
ID: 35070897
artially complete
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
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…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…

590 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