SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to create a dropdown list in which one choice will have the two possible values?


How to create a dropdown list in which one choice will have the two possible values?

Author
Message
marksquall
marksquall
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 206
Dear sqlservercentral members,

Hello and a pleasant day.

I would like to ask how to create a dropdown list in which one choice will have the two possible values? In the report I am creating for a certain customer, they need to choose between USA and EUROPE, and BOTH of them…the customer who requested the report wants a <BOTH>, USA, EUROPE should appear on the dropdown choice. In my first work, I declare a parameter (the name is @Country) that allows multiple values, so the output of the dropdown is:

(Select All)
USA
EUROPE

But like what I'd said, the customer wants a dropdown like:

<BOTH>
USA
EUROPE

So if the customer selects <BOTH>, it will be both USA and EUROPE.

By the way, here is the query I used:



SELECT * FROM tblInvoice WHERE COUNTRY IN (@Country)




Thank you for any advice that you may give.

Respectfully Yours,
MarkSquall

________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4312 Visits: 7865
Some choices, which one is best for you will depend on your setup.

First though, Country in (@Country) is misleading as @Country is one value, not a list by the time SQL gets to see it, so changing your WHERE clause to Country = @Country would achieve the same result but make it clear you understand that @Country is a single value...

1. add OR @Country='BOTH' to the query (and use "BOTH" as the value for the "BOTH" label in your dropdown list...
2. change to using Country = ISNULL(NULLIF(@Country,'BOTH'),Country)
3. change your query to use an IF
IF @Country = 'BOTH'
SELECT col1 ,col2, col3 FROM tblInvoice
ELSE
SELECT col1 ,col2, col3 FROM tblInvoice WHERE Country = @Country
4. Filter the data in the report and set the value of the label "BOTH" to USA,EUROPE in the dropdown

I am sure there are other ways also, these are just a few.

I would personally prefer options 3 or 4 but it all depends on your exact situation.

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • marksquall
    marksquall
    Valued Member
    Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

    Group: General Forum Members
    Points: 72 Visits: 206
    Dear mister.magoo,

    Hello to you. Thanks for the advise, I already got it. :-D

    Aside from your tip, I learned something new (still on comprehending but I will get there...just more and more practice I guess) about functions that returns a table and I used this to split the data into a certain delimiter (in this case, the delimiter is a comma).

    Thank you and more power.


    Respectfully Yours,

    MarkSquall

    ________________________________
    "Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search