sql server reporting services: multi value parm derived from other multi value parm problem -- sql server reporting services

multi value parm derived from other multi value parm problem

OyVey
11/10/2006 8:42:01 AM

sql server reporting services:

Hey all,

I have a sql query dataset that uses parameters derived from 2 previous sql
query datasets:

SELECT MachineID,
ModelNumber,
ProductName

FROM Admin.dbo.PricingMachine
WHERE MachineType IN (@GroupType)

AND CurrentProduct IN (@Status)

I have the parameters for this dataset defined as
@GroupType =JOIN(Parameters!GroupType.Value,",")
@Status =JOIN(Parameters!Status.Value,",")

Here are the 2 other queries that create the parameters:

For @Status:

SELECT Distinct CurrentProduct AS Status, Description = CASE CurrentProduct
WHEN 'Y' THEN 'Current'

WHEN 'N' THEN 'Discontinued'

WHEN 'X' THEN 'NonSellable'

END
FROM Admin.dbo.PricingMachine
ORDER BY Description

For @GroupType:
SELECT DISTINCT MachineType, Description = CASE MachineType WHEN 'C' THEN
'Analog Copiers'

WHEN 'E' THEN 'Engineering'

WHEN 'CO' THEN 'Full Color Copiers'

WHEN 'M' THEN 'Multifunction Copiers'

WHEN 'PC' THEN 'Printers - Color'

WHEN 'P' THEN 'Printers - B&W'

WHEN 'EP' THEN 'Priports' END

FROM Admin.dbo.PricingMachine
WHERE MachineType IN ('C','E','CO','M','PC','P','EP')
ORDER BY MachineType

If only one grouptype is selected it works fine, even with multiple statuses
selected (which correctly pulls in all data for all selected statuses, no
problem). If I select multiple grouptypes (regardless of # of status
selected) the resulting dataset is empty. Any ideas?

Thanks for any and all help!

RE: multi value parm derived from other multi value parm problem

OyVey
11/10/2006 1:11:02 PM

Needed to update this as the first query actually includes an IF clause,
which I think is the problem:

IF @GroupType = 'SS'

BEGIN

SELECT DISTINCT ProductSeriesID AS 'MachineID',
A.Series AS 'ProductName'
FROM Admin.dbo.Products_Series A RIGHT OUTER JOIN
Admin.dbo.Products_SeriesMachines B ON A.ProductSeriesID = B.SeriesID

WHERE NOT A.Series = ' '
END

ELSE

SELECT MachineID,
ProductName

FROM Admin.dbo.PricingMachine
WHERE MachineType IN (@GroupType)

AND CurrentProduct IN (@Status)

I've tried adding BEGIN/END to the ELSE clause also with no luck.

--
Cheri

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.