Solved

SSRS R2 reporting

Posted on 2011-02-20
2
361 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 17

Accepted Solution

by:
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...

0
 

Author Closing Comment

by:sqlcurious
ID: 35070897
artially complete
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
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 …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 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