?
Solved

help needed for reports in query to build reports

Posted on 2011-02-28
49
Medium Priority
?
309 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
[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
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
LVL 17

Accepted Solution

by:
pssandhu earned 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

764 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