I am trying to replace place holders with user input.
When the user goes to run the report, a GUI dialog pops up asking them to specify the start date, end date, and customer ID. I have several variables that store expressions to calculate certain outcomes. However, the data in the WHERE statements for the variables is hard-coded in, for example:
@InputWeight - (
SELECT Sum([ICPL].[OriginalQuantity_Stk])
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-06-24 00:00:00' } AND ([ICPL].ProductionDate <= { ts '2015-06-24 00:00:00' } OR [ICPL].ProductionDate Is Null))
AND (AR_Customers.CustomerCode = 904)
)
This is problematic because the date will not always be the 24th of June and the customer code may not always be 904.
I created a variable called @sID and made it appear in the GUI dialog box. Then, I set AR_Customers.CustomerCode = @sID. So in the dialog box in the sID field if I enter 904 it will display the proper data.
The problem is, when I attempt to replace 904 with @sID in the WHERE statement for the variables, it doesn't display any data for that field. This is how it looks:
@InputWeight - (
SELECT Sum([ICPL].[OriginalQuantity_Stk])
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-06-24 00:00:00' } AND ([ICPL].ProductionDate <= { ts '2015-06-24 00:00:00' } OR [ICPL].ProductionDate Is Null))
AND (AR_Customers.CustomerCode = @sID)
)
When I have the variable expression as it is in my first example it displays correctly, but when I swap out 904 for @sID, nothing appears in that field. What am I doing wrong that it stops displaying the value necessary? Am I on the correct track?
I am using Microsoft SQL Sever 2005.
Full Code if this helps clarify things:
SET NOCOUNT ON;
DECLARE @PurchaseCost Decimal(19,8);
DECLARE @InputWeight Decimal(19,8);
DECLARE @Shrink Decimal(19,8);
DECLARE @Prod_CostLBS Decimal(19,8);
DECLARE @Cost Decimal(19,8);
DECLARE @Profit Decimal(19,8);
DECLARE @Proj Decimal(19,8);
DECLARE @sID Int;
SET @PurchaseCost = 2.58;
SET @InputWeight = 18100;
SET @Shrink = @InputWeight - (
SELECT Sum([ICPL].[OriginalQuantity_Stk])
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-06-24 00:00:00' } AND (ICPL.ProductionDate <= { ts '2015-06-24 00:00:00' } OR ICPL.ProductionDate Is Null))
AND (AR_Customers.CustomerCode IN (904))
);
SET @Prod_CostLBS = 0.15;
SET @Cost = ROUND((@PurchaseCost + @Prod_CostLBS) * (
SELECT Sum([ICPL].[OriginalQuantity_Stk])
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-06-24 00:00:00' } AND (ICPL.ProductionDate <= { ts '2015-06-24 00:00:00' } OR ICPL.ProductionDate Is Null))
AND ((1=1) AND AR_Customers.CustomerKey IN (124))
), 2);
SET @Profit = (
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-06-24 00:00:00' } AND (ICPL.ProductionDate <= { ts '2015-06-24 00:00:00' } OR ICPL.ProductionDate Is Null))
AND ((1=1) AND AR_Customers.CustomerKey IN (124))
) - @Cost;
SET @Proj = ROUND((@Profit) / (
SELECT Sum([ICPL].[OriginalQuantity_Stk])
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-06-24 00:00:00' } AND (ICPL.ProductionDate <= { ts '2015-06-24 00:00:00' } OR ICPL.ProductionDate Is Null))
AND ((1=1) AND AR_Customers.CustomerKey IN (124))
), 2)
;
SET @sID = 904;
SELECT DISTINCT
CAST([AR_Customers].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([AR_Customers].[Name] AS NVARCHAR(40)) AS [Supplier]
, [PC].ProductCode
, [PC].Description1
, Count(IC_ProductLots.OriginalQuantity_Alt) AS [Boxes]
, IC_ProductLots.UnitOfMeasure_Alt
, Sum(IC_ProductLots.OriginalQuantity_Stk) AS [Weight]
, IC_ProductLots.UnitOfMeasure_Stk
, [ICP].UnitCost AS [Unit Cost]
, Sum(ROUND([DCT].[Quantity_Stk] *[ICP].[UnitCost], 2)) AS [Total Sales]
, Avg(([IC_ProductLots].[OriginalQuantity_Stk] / [IC_ProductLots].[OriginalQuantity_Alt])) AS [Avg. Box Weight]
, Sum([IC_ProductLots].[OriginalQuantity_Stk] / @InputWeight) AS [Yield]
, @Shrink AS [Shrink]
, @Cost AS [Cost]
, @Profit AS [Profit]
, @Proj AS [Proj]
, @sID AS [sID]
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
ON [DCT].LotKey = IC_ProductLots.LotKey)
LEFT OUTER JOIN IC_ProductCosts [ICP]
ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
WHERE
(IC_ProductLots.ProductionDate >= { ts '2015-06-24 00:00:00' } AND (IC_ProductLots.ProductionDate <= { ts '2015-06-24 00:00:00' } OR IC_ProductLots.ProductionDate Is Null))
GROUP BY
CAST([AR_Customers].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([AR_Customers].[Name] AS NVARCHAR(40))
, [PC].ProductCode
, [PC].Description1
, IC_ProductLots.UnitOfMeasure_Alt
, IC_ProductLots.UnitOfMeasure_Stk
, [ICP].UnitCost
, IC_ProductLots.ProductionDate
, AR_Customers.CustomerCode
HAVING
(AR_Customers.CustomerCode = @sID)
ORDER BY
CAST([AR_Customers].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([AR_Customers].[Name] AS NVARCHAR(40))