I believe this is the correct way to go about the problem, but if there is another way please let me know.
For my report, the user enters the start date, end date, and a supplier ID. In my report I also have four variables that are affected by the user's input. This is where the problem is, currently the date and supplier ID are hard coded in for these variables, below is an example:
(
SELECT SUM( ROUND([DCT].[Quantity_Stk] *[ICP].[UnitCost], 2 ))
FROM IC_Products [PC]
INNER JOIN DC_Transactions [DCT]
ON [PC].ProductKey = [DCT].ProductKey
INNER JOIN AR_Customers
ON [DCT].CustomerKey = AR_Customers.CustomerKey
INNER JOIN IC_ProductLots [ICPL]
ON [DCT].LotKey = [ICPL].LotKey
LEFT OUTER JOIN IC_ProductCosts [ICP]
ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5
WHERE (ICPL.ProductionDate >= { ts '2015-04-24 00:00:00' } AND (ICPL.ProductionDate <= { ts '2015-04-24 00:00:00' } OR ICPL.ProductionDate Is Null))
AND ((1=1) AND AR_Customers.CustomerKey IN (124) )
) - @Cost
I know that I can add place holders such as:
declare @d1 datetime,
@d2 datetime,
@sID int;
set @d1 = 'user's input start date';
set @d2 = 'user's input end date';
set @sID = 'user's selected supplier ID';
And then fill in @d1, @d2, @sID in the variable's WHERE statements.
Currently the WHERE statement for the whole report looks like this:
WHERE
(IC_ProductLots.ProductionDate >= { ts '2015-04-24 00:00:00' }
AND (IC_ProductLots.ProductionDate <= { ts '2015-04-24 00:00:00' }
OR IC_ProductLots.ProductionDate IS NULL))
AND ((1=1)
AND AR_Customers.CustomerKey IN (123) )
So when the user clicks 'Print' a dialog box pops up and they select the start date, end date, and supplier key and that is what populates the main WHERE statement, however I am not sure of how to make it correlate to the WHERE statements in my variables.
I am using Microsoft SQL 2005, and no other languages besides SQL.
Please let me know if you need more information.
Aucun commentaire:
Enregistrer un commentaire