April 1, 2008 at 4:30 am
Hi,
I am new to SSRS and am currently trying to make a report that will show my users how many trial kits can be manufactured based on stock availability.
The requirement is for the report to be able to return data for multiple kits and also for user-defined quantities of each kit.
So far I have managed to create a pick-list multi-value parameter for choosing the kits to be made based on a simple query.
Is there any way I can expand the parameter or use an additional linked parameter so the users can specify how many of each of the chosen kits they wish to build?
Any help or advice would be greatly appreciated.
Thanks.
April 3, 2008 at 12:33 am
Ummm... add a second report parameter?
:ermm:
Regards,
Jacob
April 3, 2008 at 1:54 am
I have added a second parameter to the report. The problem I am experiencing is that the items in the first multi-value parameter are not linked with those in the second.
For example
First Parameter Second Parameter
Item A 10
Item B 20
Item C 30
How do I link the parameters so that the report knows that I wish to have 10 of item A, 20 of item B etc when both parameters are multi-value?
Apologies if I didn't explain this well initially.
April 3, 2008 at 11:02 am
Why don't you post your query script here. I'm sure someone can direct you once they know how you're collecting your data.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
April 3, 2008 at 12:43 pm
Please post your full code, and also, what are you using to design this report? Report Builder? VS?
April 4, 2008 at 2:18 am
Thanks for the replys.
I am using SQL Server Business Intelligence Development Studio/VS 2005 to develop my report.
There are two queries used in my report. The first is just a lookup that is used to populate my list of parts for the first multi-value parameter, called @PART:
SELECT DISTINCT a.PartCode, a.PartDesc
FROM [tablea] a
WHERE a.Status IN ('Active','Deprecated')
The main query uses a CTE to drill-down from parent parts to child parts to retrieve a complete list of the parts required to make a trial kit and uses @QtyRequired which is the second multi-value list of amounts of kits that I need to link somehow to the list of kits:
WITH DrillDown (PartLevel, ParentPartCode, ChildPartCode, QtyTotal, QtyParentPart, QtyChildPart)
AS
(
SELECT 1 AS PartLevel,
ParentPartCode,
ChildPartCode,
CAST(@QtyRequired AS float) AS QtyTotal,
a.QtyUsed AS QtyParentPart,
a.QtyUsed AS QtyChildPart
FROM [Tablea] a
WHERE a.ParentPartCode IN (@PART)
UNION ALL
SELECT PartLevel + 1,
ParentPartCode,
ChildPartCode,
QtyTotal = QtyTotal * QtyParentPart * QtyChildPart,
QtyParentPart,
QtyChildPart
FROM [Tablea] a
JOIN DrillDown AS d
ON a.ChildPartCode = d.ChildPartOnly
)
SELECT DISTINCT d.PartLevel,
b.ParentPartDesc,
d.ParentPartCode,
b.ChildPartDesc,
d.ChildPartCode,
b.InStock,
b.AllocatedToExistingKits,
b.PartsOnOrder,
FLOOR((b.InStock + b.AllocatedToExistingKits) / d.QtyChildPart) AS CanMakeGoShort,
FLOOR((b.InStock / d.QtyChildPart) AS CanMakeDontGoShort,
FLOOR((b.InStock + b.PartsOnOrder) / d.QtyChildPart) AS CanMakeAllowForOrdered,
FLOOR((b.InStock + b.AllocatedToExistingKits + b.PartsOnOrder) / d.QtyChildPart) AS CanMakeAllowandGoShort,
d.QtyTotal,
d.QtyChildPart
FROM [Tableb] b
JOIN DrillDown d
ON b.PartCode = d.ChildPartCode
As before any suggestions would be greatly appreciated as I am still stuck fast on this. Thanks.
April 6, 2008 at 6:14 pm
OK - first of all you can't do this:
...WHERE a.ParentPartCode IN (@PART)
unless you manually expand @PART into it's value using dynamic SQL. Have a read of Erland Sommarskog's excellent article on the various ways you can convert your delimited multivalue input parameter from RS into a table.
Your query should either join on the results of a table-valued function that pivots your input parameter or insert those results into a temp table/table variable and join on that.
Now back to your original question - if you need the user to be able to input multiple product types each with its own quantity value it will be a bit trickier. The one area where RS falls down is when you need complex input parameters (unless you go the route of writing your own custom controls, which is not trivial). A quick way could be to modify one of the above methods to take a two-dimensional array packed into a delimited string (so that every value in an odd ordinal position is the product code and every value in an even ordinal position is the preceding product's quantity) like so:
'widget A,10,doodad B,15,superwidget Y,8'
etc. and pivot it into a table or tables.
The obvious problem with this is your input control will need to be a textbox - say goodbye to your nice dropdown of product codes. Alternatively you could combine this approach with your dropdown, and have your product codes come from the dropdown but the quantities come from a textbox - but the user would then need to ensure that they enter the quantities in exactly the same order as the products come through in the delimited parameter.
A nicer approach for the users would be either a custom RS input control, or wrap your RS report into a custom web app of your own that takes a more complicated input and simply calls the report to operate on that input. But you'd need to comfortable with one of the dotNET languages...
Regards,
Jacob
April 6, 2008 at 11:12 pm
To further Jacob's suggestion... here's the test code...
--===== Do a couple of presets for appearance
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
--===== Simulate a 2 part paramter
DECLARE @SomeParam VARCHAR(50) --Can be any length including MAX
SET @SomeParam = 'widget A,10,doodad B,15,superwidget Y,8'
--===== Now, split it. Output could be directed to a temp table or table variable
;WITH
cteSplit AS
(
SELECT Row = (ROW_NUMBER() OVER (ORDER BY t.N)-1)/2,
Col = (ROW_NUMBER() OVER (ORDER BY t.N)-1)%2,
Val = SUBSTRING(','+@SomeParam, t.N+1, CHARINDEX(',', @SomeParam+',', t.N+1)-t.N)
FROM dbo.Tally t
WHERE SUBSTRING(','+@SomeParam, t.N, 1) = ','
AND t.N <= LEN(','+@SomeParam)
)
SELECT MAX(CASE WHEN Col = 0 THEN Val ELSE NULL END) AS Product,
MAX(CASE WHEN Col = 1 THEN Val ELSE NULL END) AS Quantity
FROM cteSplit
GROUP BY Row
... and here's the results...
Product Quantity
--------------------------------------------------- ---------------------------------------------------
widget A 10
doodad B 15
superwidget Y 8
--Jeff Moden
April 6, 2008 at 11:27 pm
My apologies... see the following URL for how to make a Tally table...
http://qa.sqlservercentral.com/scripts/Advanced+SQL/62486/
--Jeff Moden
April 17, 2008 at 9:51 am
Thanks for the excellent advice guys.
Having done some further work on this along the lines you suggested I have reached the conclusion that a custom control in my report to collect value pairs of parts and amounts is the best solution for this.
Time to dust off my VB. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply