asked on

help in reports

Hi experts

I have a long issue

I am bulding a report called project detail cost.In the drop down list for the project as shown in the attached screenshot,
when i select empty,the report should display all the project names that lies within that specified date range as shown in the report sample.
but it s not showing.

another issue is when i select multiple value from the drop down list i am getting the error as 'procedure has too many arguments specified'.

i have attached the screen shot and detailed dataset design along with the sample report plus the stored procedure

thanks
Screenshot-projectdetailcost.doc
rpteBidProjectDetailCosting.pdf
ProjectDetailCost-StoredPrc.txt

ASKER CERTIFIED SOLUTION

sureshbabukrish

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlcurious

ASKER

hi have changed the proc as u said.PLease check below

now my req is, if i select blank space,all the project name should be displayed.Can u please check the dataset fro rpoject name and what should be the query there.How to use table variable or temp table in the proc to display the report acooording to the format

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(Max) = Null
)
AS
begin
DECLARE @SQL VARCHAR(8000)

set @SQL=
'SELECT t1.*,((TotalPrice - TotalCost - TotalFreight)/TotalCost)*100 as Markup,
((TotalPrice - TotalCost - TotalFreight)/TotalBidPrice)*100 as Margin,
       t2.TotalBidPrice

FROM
(SELECT
P.ProjectID,
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 UnitPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS TotalPrice,
--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_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.projectID>=1'

if(@projectnumber is not null and @projectnumber <> '')
Begin
Set @SQL = @SQL + ' and p.projectnumber = '''+ convert(varchar(5),@projectnumber ) +''''
End

if(@Projectcontrolnumber is not null and @Projectcontrolnumber <> '')
Begin
Set @SQL = @SQL + ' and p.projectcontrolnumber = '''+ convert(varchar(5),@Projectcontrolnumber) + ''''
End

if (@projectname <> '' or @projectname is not null)
begin
SET @SQL= @Sql + ' AND P.ProjectName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @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 in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
end

SET @SQL = @SQL + ') t1 '

SET @SQL = @SQL + '
cross join (
select SUM(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS TotalBidPrice
FROM TBL_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.projectID>=1'

if(@projectnumber is not null and @projectnumber <> '')
Begin
Set @SQL = @SQL + ' and p.projectnumber = '''+ convert(varchar(5),@projectnumber ) +''''
End

if(@Projectcontrolnumber is not null and @Projectcontrolnumber <> '')
Begin
Set @SQL = @SQL + ' and p.projectcontrolnumber = '''+ convert(varchar(5),@Projectcontrolnumber) + ''''
End

if (@projectname <> '' or @projectname is not null)
begin
SET @SQL= @Sql + ' AND P.ProjectName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @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 in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
end

SET @SQL = @SQL + ') t2'

SET @SQL = @SQL + ' cross join (SELECT 1 AS A
UNION
SELECT 2 AS A) t3'
print @sql
exec (@sql)

END