Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Allow for Single or Multiple Value Select in Report Parameter Expand / Collapse
Author
Message
Posted Tuesday, September 15, 2009 4:10 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
Hi. I have developed quite a few reports but have not yet mastered allowing a user to SELECT 1 or multiple values from a report parameter.

I have used:
SELECT -1 Prefix,'' AS PrefixId
UNION
SELECT DISTINCT Prefix,PrefixId
FROM Prefix

This allowed me to return all values for prefixes then filter on a single prefix. I adapted the above from something I found in another report. To be honest I am unsure what the SELECT -1 does so feel free to educate me!

I have the main dataset query.....something like:

SELECT po.PurchaseOrderId,p.PrefixId
FROM PurchaseOrders AS po
INNER JOIN Prefix AS p ON po.Prefix = p.prefix
WHERE p.PrefixId = @Prefix

I would then create a second dataset for the lookup values for the prefixes:

SELECT p.PrefixId
FROM Prefixes

I would then set the @Prefix parameter from Query. This allows for a single value to be select.

I have read a little on COALESCE + Cursors & temp tables + UDF. I have only worked with temp tables & CTE's.

Can anyone offer me some advice on how to piece it all together?
Many Thanks,
Phil.


-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper
Post #788603
Posted Tuesday, September 15, 2009 8:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 9, 2010 2:41 AM
Points: 140, Visits: 1,019
I am unsure what the SELECT -1 does

This alows you to display a simple, maybe default, empty selection in your combo, maybe to allow for "every option" or "ignore this filter", as I'll show you here:

SELECT po.PurchaseOrderId,p.PrefixId
FROM PurchaseOrders AS po
INNER JOIN Prefix AS p ON po.Prefix = p.prefix
WHERE p.PrefixId = @Prefix
OR @Prefix = -1

This OR @Prefix = -1 allows you to pass -1 and actually ignore the filter to return all rows.


Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

Post #788676
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse