Solved

help needed for reports in query to build reports

Posted on 2011-02-28
49
257 Views
Last Modified: 2012-06-22
SELECT  CustomerName
FROM     TBL_Customer

union
select 'all'

I have created a dataset call dsCustomerName to build a report.In the report parameter properties
i need to select the  customer name by query so that i can select customer names from the list in the
report.In a set of customer list i have a option called 'all' and if i select this 'all' i need to display the
reports for all the customers that exist in the list that means i need to pass all the customer names in all.

Following query let me add all in the customer drop down list but how to pass all the customer names in 'all'.

SELECT  CustomerName
FROM     TBL_Customer

union
select 'all'



please see the attached proc and please suggest me change in the proc
sampleproc.txt
0
Comment
Question by:sqlcurious
  • 25
  • 24
49 Comments
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
I am assuming that the user can only choose one value at a time from the drop down list. In that case change the customer code to this:

IF  @CustomerName <> ''  
begin  
 set @sql=@sql + ' And CU.CustomerName LIKE ''%'+CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END +'%'''    
end

Hope this helps.

P.
0
 

Author Comment

by:sqlcurious
Comment Utility
its throwing an error cu.customer couldnot be bound
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
That's right. It is because it a dynamic SQL. Let me re-write this for you.
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
I have attached the updated proc along.

Hope this helps.

P.
Query.txt
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Oops... one mistake

Can you please change this code line in the proc

FROM
and p.projectstartdate >= ISNULL(@LettingDateTo,p.projectstartdate)

TO
and p.projectstartdate <= ISNULL(@LettingDateTo,p.projectstartdate)
0
 

Author Comment

by:sqlcurious
Comment Utility

  Actually i am taking the customer name from the query in the dataset.Do i need to change to the query in the dataset too.
if yes please let me now that too

SELECT  CustomerName
FROM     TBL_Customer

union
select 'all'
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Seems okay to me.
0
 

Author Comment

by:sqlcurious
Comment Utility
i am not getting any results.In the mean time how to implement the multivalued parameter for customer and division names for your query
please help
0
 

Author Comment

by:sqlcurious
Comment Utility
i made the changes you told me still no result
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Let's try to trouble shoot the proc in SSMS first.

Can you run the following code in SSMS and see if you get results. I have commented out the last three lines of the code to see if we get results when we just use the dates as parameters.

P.
Declare  
 @LettingDateFrom datetime,  
 @LettingDateTo datetime,  
 @ProjectTxDot int=2 ,  
 @CustomerName varchar(50),  
 @DivisionName varchar(250)
   
SET @LettingDateFrom = NULL  
SET @LettingDateTo = NULL  
SET @ProjectTxDot = 2  
SET @CustomerName = 'all'  
SET @DivisionName = ''


SELECT	CU.CustomerID,  
		CU.CustomerName,  
		P.ProjectID ,  
		P.Projectname,  
		p.ProjectNumber,  
		P.ProjectCountyID,  
		convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,  
		P.projectTxdot,  
		D.Divisionname As DivisionName,  
		--P.projectCountyID As CountyID,  
		C.CountyName As CountyName,  
		--CU.CustomerName As CustomerName,  
		--CU.customerID,  
		QI.QuoteItemQuantity as Quantity,  
		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.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice) AS TotalPrice,  
		(QI.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,  
		QI.QuoteItemWeight AS ItemWeight,  
		QI.QuoteItemMarkupPercent AS MarkupPercent,  
		Q.QuoteFreightRate as FreightRate,  
		Q.QuoteNumberOfTrucks,  
		Q.QuoteSurcharge1,  
		Q.QuoteSurcharge2,  
		Q.QuoteSurcharge3,  
		((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight, 
		(((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice)-(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice)-((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)))/((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice))*100) AS MARGIN
FROM	TBL_Project P INNER JOIN  TBL_Quotes1 Q 
		ON P.ProjectID = Q.ProjectID  inner   JOIN  TBL_County C 
		ON C.CountyID = P.ProjectCountyID inner JOIN  TBL_QuoteItems QI 
		ON Q.QuoteID = QI.QuoteID INNER JOIN  TBL_Division D 
		ON D.createdby = Q.QuoteCreatedby --INNER JOIN  TBL_CustomerQuotes CU 
		-- ON CU.QuoteID = Q.QuoteID   
		inner join tbl_customer cu on cu.customercreatedby=Q.QuoteCreatedby
WHERE	p.projectstartdate >= ISNULL(@LettingDateFrom,p.projectstartdate)
		and p.projectstartdate <= ISNULL(@LettingDateTo,p.projectstartdate)
		--and D.DivisionName LIKE '''%'+ CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END + '%'''
		--and p.ProjectTxDot = CASE WHEN @projectTxDot = 2 Then p.ProjectTxDot Else @projectTxDot END
		--and CU.CustomerName LIKE '''%'+ CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END + '%'''

Open in new window

0
 

Author Comment

by:sqlcurious
Comment Utility
following sets of errors are displayed

Msg 156, Level 15, State 1, Procedure DBSP_Report_LetDate, Line 4
Incorrect syntax near the keyword 'declare'.
Msg 139, Level 15, State 1, Procedure DBSP_Report_LetDate, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 1, Procedure DBSP_Report_LetDate, Line 13
Must declare the scalar variable "@ProjectTxDot".
Msg 137, Level 15, State 1, Procedure DBSP_Report_LetDate, Line 14
Must declare the scalar variable "@CustomerName".
Msg 137, Level 15, State 1, Procedure DBSP_Report_LetDate, Line 15
Must declare the scalar variable "@DivisionName".
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Can you change the Declare statement to this and re-run

Declare  
 @LettingDateFrom datetime,  
 @LettingDateTo datetime,  
 @ProjectTxDot int,  
 @CustomerName varchar(50),  
 @DivisionName varchar(250)
0
 

Author Comment

by:sqlcurious
Comment Utility
PROC


create procedure DBSP_Report_LetDate
(
 Declare  
 @LettingDateFrom datetime,  
 @LettingDateTo datetime,  
 @ProjectTxDot int,  
 @CustomerName varchar(50),  
 @DivisionName varchar(250)
 )
SET @LettingDateFrom = NULL  
SET @LettingDateTo = NULL
SET @ProjectTxDot = 2
SET @CustomerName = 'all'
SET @DivisionName = ''


SELECT      CU.CustomerID,  
            CU.CustomerName,  
            P.ProjectID ,  
            P.Projectname,  
            p.ProjectNumber,  
            P.ProjectCountyID,  
            convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,  
            P.projectTxdot,  
            D.Divisionname As DivisionName,  
            --P.projectCountyID As CountyID,  
            C.CountyName As CountyName,  
            --CU.CustomerName As CustomerName,  
            --CU.customerID,  
            QI.QuoteItemQuantity as Quantity,  
            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.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice) AS TotalPrice,  
            (QI.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,  
            QI.QuoteItemWeight AS ItemWeight,  
            QI.QuoteItemMarkupPercent AS MarkupPercent,  
            Q.QuoteFreightRate as FreightRate,  
            Q.QuoteNumberOfTrucks,  
            Q.QuoteSurcharge1,  
            Q.QuoteSurcharge2,  
            Q.QuoteSurcharge3,  
            ((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight,
            (((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice)-(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice)-((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)))/((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice))*100) AS MARGIN
FROM      TBL_Project P INNER JOIN  TBL_Quotes1 Q
            ON P.ProjectID = Q.ProjectID  inner   JOIN  TBL_County C
            ON C.CountyID = P.ProjectCountyID inner JOIN  TBL_QuoteItems QI
            ON Q.QuoteID = QI.QuoteID INNER JOIN  TBL_Division D
            ON D.createdby = Q.QuoteCreatedby --INNER JOIN  TBL_CustomerQuotes CU
            -- ON CU.QuoteID = Q.QuoteID  
            inner join tbl_customer cu on cu.customercreatedby=Q.QuoteCreatedby
WHERE      p.projectstartdate >= ISNULL(@LettingDateFrom,p.projectstartdate)
            and p.projectstartdate <= ISNULL(@LettingDateTo,p.projectstartdate)
            --and D.DivisionName LIKE '''%'+ CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END + '%'''
            --and p.ProjectTxDot = CASE WHEN @projectTxDot = 2 Then p.ProjectTxDot Else @projectTxDot END
            --and CU.CustomerName LIKE '''%'+ CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END + '%'''
            


FOLLOWING ERRORS POPULATED


Msg 156, Level 15, State 1, Procedure DBSP_Report_LetDate, Line 4
Incorrect syntax near the keyword 'Declare'.
Msg 102, Level 15, State 1, Procedure DBSP_Report_LetDate, Line 10
Incorrect syntax near ')'.
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Oh no. You do not have to update the proc with my latest code. You need to run this in SQL Server Managment Studio by itself.

Just copy paste my code in a query window and run the code.
0
 

Author Comment

by:sqlcurious
Comment Utility
I  did copy paste and then tried first

following errors populated

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 1, Line 12
Must declare the scalar variable "@ProjectTxDot".
Msg 137, Level 15, State 1, Line 13
Must declare the scalar variable "@CustomerName".
Msg 137, Level 15, State 1, Line 14
Must declare the scalar variable "@DivisionName".


0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Okay, I am totally stumped here.

Can you run just the following code:

Declare  
 @LettingDateFrom datetime,  
 @LettingDateTo datetime,  
 @ProjectTxDot int,  
 @CustomerName varchar(50),  
 @DivisionName varchar(250)
   
SET @LettingDateFrom = NULL  
SET @LettingDateTo = NULL  
SET @ProjectTxDot = 2  
SET @CustomerName = 'all'  
SET @DivisionName = ''


Select @LettingDateFrom,  
 @LettingDateTo ,  
 @ProjectTxDot ,  
 @CustomerName ,  
 @DivisionName
0
 

Author Comment

by:sqlcurious
Comment Utility
(No column name)  no column  name
NULL                         null


ya it showed the result
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Okay, so there is no problem with the syntax.

So let's try this again. I have attached couple of files along. Can you copy the code and run them one by one to see if you get results.

I have numbered them so run them in the chronoligical order.

P.
1.txt
2.txt
0
 

Author Comment

by:sqlcurious
Comment Utility

hi expert


both files executed successfully

what will be the next step?
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Try the attached code. If this one works then we know our query is working fine.


3.txt
0
 

Author Comment

by:sqlcurious
Comment Utility
it is just showing me the headers only not returning any values
0
 

Author Comment

by:sqlcurious
Comment Utility
i want to put them inside the stored proc too
0
 

Author Comment

by:sqlcurious
Comment Utility
and D.DivisionName LIKE '''%'+ CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END + '%'''
            and p.ProjectTxDot = CASE WHEN @projectTxDot = 2 Then p.ProjectTxDot Else @projectTxDot END
            and CU.CustomerName LIKE '''%'+ CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END + '%'''

the above statement has problem
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
unfortunately i cannot run and troubleshoot this for you. can you comment out the lines and run them one by one. Meaning, comment the last two lines and run the query just with the divisionname. if it works then run it with projecttxdot and then with customer. This will help us understand which line we are having the troubles with.

Once we resolve this, we will then put this in a proc.

P.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:sqlcurious
Comment Utility
and D.DivisionName LIKE '''%'+ CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END + '%'''

 and CU.CustomerName LIKE '''%'+ CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END + '%'''

thse two lines are giving problems both of them i tried one by one
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Okay so we have managed to narrow it down. Can you please just try running the query with DivisionName and SET the @DivisionName value to something that you know wiil definetly return records.
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Okay, so I created some dummy data on my end to test this. The issue seems to be the LIKE operator. Now, since you are using a drop down for the users to choose the value from, I do not think you need to use the LIKE operator. We can just use the = operator. So the code for these two lines now look something like this:

and D.DivisionName = CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END
and CU.CustomerName = CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END

This should work. I tested this out at my end.

Let me know.

P.

0
 

Author Comment

by:sqlcurious
Comment Utility
no it didnot help
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
I am not sure why not. I created this test in my end it works fine.

Have a look and see if it helps you troubleshoot at your end.

P.
-- Create Temporary Table
CREATE TABLE #Testing (
Num     INT,
Customer Varchar(100),
Division Varchar(100)
)

-- INSERT RECORDS FOR TESTING
INSERT INTO #Testing VALUES (1,'John Smith','Division One')
INSERT INTO #Testing VALUES (2,'Charles Davenport','Division Two')
INSERT INTO #Testing VALUES (3,'Johny Boy','Division Three')
INSERT INTO #Testing VALUES (3,'Milan','Division Three')
INSERT INTO #Testing VALUES (3,'Jack Smith','Division Four')


-- TEST OUR CODE

Declare   
 @CustomerName varchar(50),  
 @DivisionName varchar(250)

-- Please remember the values have to be exact   
SET @CustomerName = 'all'  
SET @DivisionName = 'Division Three'


SELECT	*
From	#Testing
WHERE	Division = CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then Division Else @DivisionName END
        and Customer = CASE WHEN @CustomerName = 'all' Then Customer Else @CustomerName END



-- Drop Table
DROP TABLE #Testing

Open in new window

0
 

Author Comment

by:sqlcurious
Comment Utility
i dont know why its not  giving

i have exact diviison name='division description 1
                    customer name='primus'


and put like this.Still no results




Declare  
 @LettingDateFrom datetime,  
 @LettingDateTo datetime,  
 @ProjectTxDot int,  
 @CustomerName varchar(50),  
 @DivisionName varchar(250)
   
SET @LettingDateFrom = NULL  
SET @LettingDateTo = NULL  
SET @ProjectTxDot = 2  
SET @CustomerName = 'division description 1'  
SET @DivisionName = 'primus'

SELECT      CU.CustomerID,  
            CU.CustomerName,  
            P.ProjectID ,  
            P.Projectname,  
            p.ProjectNumber,  
            P.ProjectCountyID,  
            convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,  
            P.projectTxdot,  
            D.Divisionname As DivisionName,  
            --P.projectCountyID As CountyID,  
            C.CountyName As CountyName,  
            --CU.CustomerName As CustomerName,  
            --CU.customerID,  
            QI.QuoteItemQuantity as Quantity,  
            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.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice) AS TotalPrice,  
            (QI.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,  
            QI.QuoteItemWeight AS ItemWeight,  
            QI.QuoteItemMarkupPercent AS MarkupPercent,  
            Q.QuoteFreightRate as FreightRate,  
            Q.QuoteNumberOfTrucks,  
            Q.QuoteSurcharge1,  
            Q.QuoteSurcharge2,  
            Q.QuoteSurcharge3,  
            ((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight,
            (((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice)-(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice)-((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)))/((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice))*100) AS MARGIN
FROM      TBL_Project P INNER JOIN  TBL_Quotes1 Q
            ON P.ProjectID = Q.ProjectID  inner   JOIN  TBL_County C
            ON C.CountyID = P.ProjectCountyID inner JOIN  TBL_QuoteItems QI
            ON Q.QuoteID = QI.QuoteID INNER JOIN  TBL_Division D
            ON D.createdby = Q.QuoteCreatedby --INNER JOIN  TBL_CustomerQuotes CU
            -- ON CU.QuoteID = Q.QuoteID  
            inner join tbl_customer cu on cu.customercreatedby=Q.QuoteCreatedby
WHERE      p.projectstartdate >= ISNULL(@LettingDateFrom,p.projectstartdate)
            and p.projectstartdate <= ISNULL(@LettingDateTo,p.projectstartdate)
            and D.DivisionName LIKE '''%'+ CASE WHEN @DivisionName = 'division description 1' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END + '%'''
            and p.ProjectTxDot = CASE WHEN @projectTxDot = 2 Then p.ProjectTxDot Else @projectTxDot END
            and CU.CustomerName LIKE '''%'+ CASE WHEN @CustomerName = 'dcc' Then CU.CustomerName Else @CustomerName END + '%'''
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Your where clause is wrong. Update the where clause to this:

WHERE   p.projectstartdate >= ISNULL(@LettingDateFrom,p.projectstartdate)
            and p.projectstartdate <= ISNULL(@LettingDateTo,p.projectstartdate)
            and D.DivisionName = CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END
            and p.ProjectTxDot = CASE WHEN @projectTxDot = 2 Then p.ProjectTxDot Else @projectTxDot END
            and CU.CustomerName = CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END

0
 

Author Comment

by:sqlcurious
Comment Utility
i updated the where clause no result.I think something is wrong in the division name and customer name section

since
 p.projectTxdot =p.ProjectTxDot = CASE WHEN @projectTxDot = 2 Then p.ProjectTxDot Else @projectTxDot END

is working fine
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
If it is okay with you, can you please run the query without the where clause and upload the results here. I'd like to see the data to see if there are any issues there.

I especially want to see the record where the customer name is "'division description 1" and Division name is "primus" since that what you set the value as in your code above.

Thanks
P.
0
 

Author Comment

by:sqlcurious
Comment Utility
i have attached the results without the where clause
output.txt
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Okay, looking at the data you were also setting the value of the varaibles wrong. You were setting the customer value on the DivisionName variable and the division value on the CustomerName variable.

Also when setting the division name value, you have an extra space.

Give the attached code a shot and see if this works.

P.
Declare  
 @LettingDateFrom datetime,  
 @LettingDateTo datetime,  
 @ProjectTxDot int,  
 @CustomerName varchar(50),  
 @DivisionName varchar(250)
   
SET @LettingDateFrom = NULL  
SET @LettingDateTo = NULL  
SET @ProjectTxDot = 2  
SET @CustomerName = 'Primus'
SET @DivisionName = 'Division Description1'


SELECT      CU.CustomerID,  
            CU.CustomerName,  
            P.ProjectID ,  
            P.Projectname,  
            p.ProjectNumber,  
            P.ProjectCountyID,  
            convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,  
            P.projectTxdot,  
            D.Divisionname As DivisionName,  
            --P.projectCountyID As CountyID,  
            C.CountyName As CountyName,  
            --CU.CustomerName As CustomerName,  
            --CU.customerID,  
            QI.QuoteItemQuantity as Quantity,  
            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.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice) AS TotalPrice,  
            (QI.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,  
            QI.QuoteItemWeight AS ItemWeight,  
            QI.QuoteItemMarkupPercent AS MarkupPercent,  
            Q.QuoteFreightRate as FreightRate,  
            Q.QuoteNumberOfTrucks,  
            Q.QuoteSurcharge1,  
            Q.QuoteSurcharge2,  
            Q.QuoteSurcharge3,  
            ((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight, 
            (((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice)-(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice)-((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)))/((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice))*100) AS MARGIN
FROM      TBL_Project P INNER JOIN  TBL_Quotes1 Q 
            ON P.ProjectID = Q.ProjectID  inner   JOIN  TBL_County C 
            ON C.CountyID = P.ProjectCountyID inner JOIN  TBL_QuoteItems QI 
            ON Q.QuoteID = QI.QuoteID INNER JOIN  TBL_Division D 
            ON D.createdby = Q.QuoteCreatedby --INNER JOIN  TBL_CustomerQuotes CU 
            -- ON CU.QuoteID = Q.QuoteID   
            inner join tbl_customer cu on cu.customercreatedby=Q.QuoteCreatedby
WHERE		p.projectstartdate >= ISNULL(@LettingDateFrom,p.projectstartdate)
            and p.projectstartdate <= ISNULL(@LettingDateTo,p.projectstartdate)
            and D.DivisionName = CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END
            and p.ProjectTxDot = CASE WHEN @projectTxDot = 2 Then p.ProjectTxDot Else @projectTxDot END
            and CU.CustomerName = CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END

Open in new window

0
 

Author Comment

by:sqlcurious
Comment Utility
yes i was my bad

it did run now my question is how to implement multi valued parameter over here.I want to give more than one input to the division name such as in division 'description1,division description2'

in the dynamic sql split function has been used like below.Also want to put your code in stored proc

if @DivisionName <> ''


 begin
 
 SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
 end
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
I am assuming your function dbo.fneBid_SplitValue above is working as required. Based on that here is the script.
CREATE PROCEDURE dbo.usp_GetProjectQuotes (	
												@LettingDateFrom datetime,  
												@LettingDateTo datetime,  
												@ProjectTxDot int,  
												@CustomerName varchar(50),  
												@DivisionName varchar(250)
										   )

AS

BEGIN
   
SELECT      CU.CustomerID,  
            CU.CustomerName,  
            P.ProjectID ,  
            P.Projectname,  
            p.ProjectNumber,  
            P.ProjectCountyID,  
            convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,  
            P.projectTxdot,  
            D.Divisionname As DivisionName,  
            --P.projectCountyID As CountyID,  
            C.CountyName As CountyName,  
            --CU.CustomerName As CustomerName,  
            --CU.customerID,  
            QI.QuoteItemQuantity as Quantity,  
            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.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice) AS TotalPrice,  
            (QI.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,  
            QI.QuoteItemWeight AS ItemWeight,  
            QI.QuoteItemMarkupPercent AS MarkupPercent,  
            Q.QuoteFreightRate as FreightRate,  
            Q.QuoteNumberOfTrucks,  
            Q.QuoteSurcharge1,  
            Q.QuoteSurcharge2,  
            Q.QuoteSurcharge3,  
            ((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight, 
            (((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice)-(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice)-((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)))/((QI.QuoteItemQuantity*QI.QuoteItem_ItemTotalPrice))*100) AS MARGIN
FROM      TBL_Project P INNER JOIN  TBL_Quotes1 Q 
            ON P.ProjectID = Q.ProjectID  inner   JOIN  TBL_County C 
            ON C.CountyID = P.ProjectCountyID inner JOIN  TBL_QuoteItems QI 
            ON Q.QuoteID = QI.QuoteID INNER JOIN  TBL_Division D 
            ON D.createdby = Q.QuoteCreatedby --INNER JOIN  TBL_CustomerQuotes CU 
            -- ON CU.QuoteID = Q.QuoteID   
            inner join tbl_customer cu on cu.customercreatedby=Q.QuoteCreatedby
WHERE		p.projectstartdate >= ISNULL(@LettingDateFrom,p.projectstartdate)
            and p.projectstartdate <= ISNULL(@LettingDateTo,p.projectstartdate)
            and D.DivisionName IN (SELECT Item FROM dbo.fneBid_SplitValue(CASE WHEN @DivisionName = '' OR @DivisionName IS NULL Then D.DivisionName Else @DivisionName END, ','))
            and p.ProjectTxDot = CASE WHEN @projectTxDot = 2 Then p.ProjectTxDot Else @projectTxDot END
            and CU.CustomerName IN (SELECT Item FROM dbo.fneBid_SplitValue(CASE WHEN @CustomerName = 'all' Then CU.CustomerName Else @CustomerName END, ','))


END

Open in new window

0
 

Author Comment

by:sqlcurious
Comment Utility
thanks it working now in the database side.But my main concern is in report.I am selecting drop down list and when i select all then is  diaplaying random customer name in the customer name section instead of 'all customers'

i think i have to change something in the dataset

i have following dataset  query for the drop down customer


SELECT     CustomerName
FROM         TBL_Customer
UNION
SELECT     'all'
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Your dataset seems okay to me. Make sure your dataset is linked to the correct parameter in the report. Can you attach a screen shot from the SSRS where you are setting the CustomerName parameter.

I can look at the settings and suggest if anything looks wrong.

P.
0
 

Author Comment

by:sqlcurious
Comment Utility
please find the screen shot
LetDateScreenshot.doc
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Is this your first time building a report? I have attached your document. Please see me comments in red.

P.
LetDateScreenshot.doc
0
 

Author Comment

by:sqlcurious
Comment Utility
yes this is my first time
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Okay. Try go through my comments and see if the report after making those changes.

P.
0
 

Author Comment

by:sqlcurious
Comment Utility
actually i cannot check the option for multivalued parameter for customer name because this is a small project of reporting and if i select 'all' from the drop down list, then it should show the reports for all customers with their respective divisions.Hence the requirement is if i select 'all' all the customer names should pass in 'all'

in the mean time i have another problem while selecting multiple divisions in the drop down list.I need to select multiple divisions according to the requirement.

I have attached the screenshot with errors,when i tried this
error-screen-shot-for-division.doc
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
It's hard to troubleshoot these errors when you cannot work with the actual soltuion file. WOuld you be able to send me the solution file and I can then go though your settings.

I cannot run it obviously but I can atleast check for design errors.

P.
0
 

Author Comment

by:sqlcurious
Comment Utility
hi

i am able to attach only 1 solution file  i am no table to attach .rdl file as it does not take here
Letting-Date-Matrix-Report.sln
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Actually, rdl is the file I need to look at. Can you change the extension of the file to txt and then attach it. I will download and change the extension back to rdl
0
 

Author Comment

by:sqlcurious
Comment Utility
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
Comment Utility
I am unable to open. Can you zip the whole project folder and upload it to a third party website where I can download it from. A good one I know is Megaupload.

However, if this data is sensitive and confidential then I would not suggest this.

Also, this thread has become too long now. You have got your stored proc working so if you agree, can you close this question accepting an answer? Open a new question we can start there. Also, this way more experts will help out which could give you faster results.

P.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now