Post reply

Allow for Single or Multiple Value Select in Report Parameter

  • September 15, 2009 at 4:10 pm

    #226143

    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

  • September 15, 2009 at 8:42 pm

    #1054226

    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)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply