July 7, 2016 at 9:37 am
I've got a complex bit of sql that took a lot of work to get working to view our bill of material levels at the same time. I created a function that the query now uses with 2 parameters but I'm struggling to get this into a useable format for our users because of the parameters being set in the FROM statement, it wont work in excel using '?' or in reporting services. Below is the script, can anyone advise a way I can have this for the users for them to just enter the two parameters:
two example parameters are filled in:
DECLARE @warehouse char(2)
DECLARE @product char(20)
SET @warehouse = 'BF'
SET @product = 'B001702'
SELECT
bomlvl AS [BOM Level]
, subasmbly_wh AS [Sub Warehouse]
, subasmbly_prod AS [Sub Product
, subasmbly AS [Has Sub Assembly]
, subasmbly_desc AS [Sub Description]
, subasmbly_qty AS [Sub Qty]
, phantom AS [Is Phantom]
, warehouse [Warehouse]
, product [Product]
, prod_desc AS [Description]
, usage_qty AS [Usage Qty]
FROM pres.scheme.mt_ass_BomExplosion(@warehouse,@product,3,0,0)
WHERE warehouse not like 'd%'
July 7, 2016 at 10:12 am
dandrews 45259 (7/7/2016)
I've got a complex bit of sql that took a lot of work to get working to view our bill of material levels at the same time. I created a function that the query now uses with 2 parameters but I'm struggling to get this into a useable format for our users because of the parameters being set in the FROM statement, it wont work in excel using '?' or in reporting services. Below is the script, can anyone advise a way I can have this for the users for them to just enter the two parameters:two example parameters are filled in:
DECLARE @warehouse char(2)
DECLARE @product char(20)
SET @warehouse = 'BF'
SET @product = 'B001702'
SELECT
bomlvl AS [BOM Level]
, subasmbly_wh AS [Sub Warehouse]
, subasmbly_prod AS [Sub Product
, subasmbly AS [Has Sub Assembly]
, subasmbly_desc AS [Sub Description]
, subasmbly_qty AS [Sub Qty]
, phantom AS [Is Phantom]
, warehouse [Warehouse]
, product [Product]
, prod_desc AS [Description]
, usage_qty AS [Usage Qty]
FROM pres.scheme.mt_ass_BomExplosion(@warehouse,@product,3,0,0)
WHERE warehouse not like 'd%'
Not sure what interface you are using but if you are using SSRS this is simple. Once you create the report and how you want to the data to display from the above SQL you would create 2 report parameters and change the SQL as follows:
SET @warehouse = ?
SET @product = ?
Whether you use ? or @P1, @P2 depends on the data driver you are connecting to the server with (OLEDB, ADO.NET, SQL Native Client etc...). You can then test the report parameters and insure they are text based input fields and place anything you like and it will work (provided the values are valid inputs).
Most other interfaces you would just substitute the ? or @P1 with variables from whatever those interfaces are using but hopefully you get the idea....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply