Solved

help needed for reports in query to build reports

Posted on 2011-02-28
49
279 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
ID: 35001403
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
ID: 35001610
its throwing an error cu.customer couldnot be bound
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 35001705
That's right. It is because it a dynamic SQL. Let me re-write this for you.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:pssandhu
ID: 35001900
I have attached the updated proc along.

Hope this helps.

P.
Query.txt
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 35001918
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
ID: 35001920

  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
ID: 35001930
Seems okay to me.
0
 

Author Comment

by:sqlcurious
ID: 35002121
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
ID: 35002132
i made the changes you told me still no result
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 35002238
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
ID: 35002357
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
ID: 35002419
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
ID: 35002479
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
ID: 35002507
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
ID: 35002525
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
ID: 35002574
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
ID: 35002679
(No column name)  no column  name
NULL                         null


ya it showed the result
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 35003380
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
ID: 35003736

hi expert


both files executed successfully

what will be the next step?
0
 
LVL 17

Expert Comment

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


3.txt
0
 

Author Comment

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

Author Comment

by:sqlcurious
ID: 35003870
i want to put them inside the stored proc too
0
 

Author Comment

by:sqlcurious
ID: 35003922
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
ID: 35003956
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
 

Author Comment

by:sqlcurious
ID: 35003989
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
ID: 35004015
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
ID: 35004066
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
ID: 35004087
no it didnot help
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 35004103
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
ID: 35004160
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
ID: 35004225
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
ID: 35004342
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
ID: 35004401
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
ID: 35008167
i have attached the results without the where clause
output.txt
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 35008350
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
ID: 35008532
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
ID: 35008712
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
ID: 35009020
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
ID: 35009184
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
ID: 35009340
please find the screen shot
LetDateScreenshot.doc
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 35009562
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
ID: 35009624
yes this is my first time
0
 
LVL 17

Expert Comment

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

P.
0
 

Author Comment

by:sqlcurious
ID: 35010429
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
ID: 35010654
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
ID: 35010754
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
ID: 35011085
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
ID: 35011146
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 35011789
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Removing SQL Replication from Microsoft SQL Server 2008 R2 2 33
Sql query 107 63
Query Syntax 17 35
While in ##Table - Help 4 15
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 …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

772 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