• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

sql query for sql reporting services 2008-IF CONDITION

I have a query that needs to be executed based on the  radio button is checked in the sql reporting services 2008. on the reporting side i am adding all the values from the different queries as a calculated column for the Gross requirement which the addition of different queries.
i need to check if the check box is checked on the report it should execute the query otherwise it should not execute the query. example block is Gross requriment.

how would i do

Grossreq= 0
IF box1 = True
   Then Grossreq = Grossreq+ Value1
End
IF box2 = True
    Then Grossreq = Grossreq + Value 2
End
IF box3 = True
     Then Grossreq = Grossreq + value 3
End

etc. any ideas will be appreciated as i have beginner knowledge in t.sql.
@IncludeSalesOrderQty   bit = 0
	@KitSalesLineQty	bit =0,
	@ScheduledQty           bit=0,
	@TransferOrderShipment	bit=0,
	@PlanningIssuedQty      bit=0,
	
	 @GrossRequirement Decimal= 0 

	  
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT  I.[No_],I.[Description],I.[Planner Code],

(SELECT Top 1 [Work Center No_]  FROM  [Test Routing Line] 
WHERE
I.[No_]=[Test Routing Line].[Routing No_]
ORDER BY [Test Routing Line].[Operation No_] ASC ) 

as [Initial Work Center],

(SELECT Top 1 [Work Center No_]  FROM [Test Routing Line] 
	WHERE
	I.[No_]=[Test Routing Line].[Routing No_]
	ORDER BY [Test Routing Line].[Operation No_] DESC 
	) as [Final Work Center],

(SELECT SUM(Quantity) FROM [Test Item Ledger Entry]
	WHERE [Test Item Ledger Entry].[Item No_]=I.[No_] 
	)	as [Quantity on Hand],  
	
	I.[Low-Level Code],

----GROSS REQUIREMENT ---------------@GrossRequirement

 --NEED TO EXECUTE EACH QUERY BASED ON IF  @IncludeSalesOrderQty is true
 
(SELECT SUM([Outstanding Qty_ (Base)])  FROM [Test Sales Line] 
 WHERE [Test Sales Line].[No_]=I.[No_]
 AND  [Document Type]='1' and [Type]='2'
) as [Qty on Sales Order] ,


 --NEED TO EXECUTE EACH QUERY BASED ON IF  @KitSalesLineQty is true

(SELECT SUM([Outstanding Qty_ (Base)])  FROM [Test Kit Sales Line] 
 WHERE [Test Kit Sales Line].[No_]=I.[No_]
 AND  [Document Type]='1'
 ) as [Qty on Kit Sales Line] ,
 
 

 --NEED TO EXECUTE EACH QUERY BASED ON IF  @ScheduledQty is true
 (SELECT SUM([Outstanding Qty_ (Base)])  FROM [Test Transfer Line] 
 WHERE [Test Transfer Line].[Item No_]=I.[No_]
 AND  [Test Transfer Line].[Derived From Line No_]='0' 
 ) as [Qty on on Transfer Order shipment] ,
 

 --NEED TO EXECUTE EACH QUERY BASED ON IF  @TransferOrderShipment is true
  (SELECT SUM([Remaining Qty_ (Base)]) FROM [Test Prod_ Order Component] 
 WHERE [Test Prod_ Order Component].[Item No_]=I.[No_]
 AND  [Status] in (1,2,3)
 ) as [Scheduled Need (QTY)],
 
 --NEED TO EXECUTE EACH QUERY BASED ON IF  @PlanningIssuedQty 
  is true 
  
(SELECT SUM([Expected Quantity (Base)]) FROM [Test Planning Component] 
 WHERE [Test Planning Component].[Item No_]=I.[No_]
) as [Planning Issues (QTY)],

----END OF GROSS REQUIREMENT---------------------


 
FROM  [tesTItem] as I

Open in new window

0
malik1977
Asked:
malik1977
  • 2
1 Solution
 
jogosCommented:
This is how you do it when only one selection can be true, if more you can nest cases
case when box1 = True
   Then Grossreq+ Value1
 when box2 = True
    Then  Grossreq + Value 2
when box3 = True
     Then Grossreq + value 3
else 0
End

Open in new window

0
 
jogosCommented:
In your sql
...
----GROSS REQUIREMENT ---------------@GrossRequirement

 --NEED TO EXECUTE EACH QUERY BASED ON IF  @IncludeSalesOrderQty is true
 case @IncludeSalesOrderQty =true
 then
(SELECT SUM([Outstanding Qty_ (Base)])  FROM [Test Sales Line] 
 WHERE [Test Sales Line].[No_]=I.[No_]
 AND  [Document Type]='1' and [Type]='2'
) 
else NULL END as [Qty on Sales Order] ,


 --NEED TO EXECUTE EACH QUERY BASED ON IF  @KitSalesLineQty is true
case when KitSalesLineQty = True
then 
(SELECT SUM([Outstanding Qty_ (Base)])  FROM [Test Kit Sales Line] 
 WHERE [Test Kit Sales Line].[No_]=I.[No_]
 AND  [Document Type]='1'
 ) else NULL END as [Qty on Kit Sales Line] ,
 ....

Open in new window

0
 
mlmccCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for jogos's comment http:/Q_27409723.html#37010869

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
malik1977Author Commented:
I am trying to close this question and award the points. please remove the abondon status so i can close the question and award 500 points to the solution.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now