none
Filter by in stock? RRS feed

  • Question

  • I'm having trouble setting the table filter.  How do you set a filter that will show in stock items or out of stock items based on a selection from a drop down box.

     

    I just need the info on the filter setup, I have the rest taken care of.

     

    field.qty.value > 0  and

    field.qty.value < 0

     

    I get an an each time I try to set this, hence not working 

    Friday, July 13, 2007 5:07 AM

Answers

  • What does the selection in the dropdown box look like exactly?  Is it "out of stock" for one selection, and "out of stock" for the other selection ? 

     

    Here's how you do what you want, in any case: the trick is to have an "impossible" condition for one side of the IIF() in each of two filtering conditions, tied together with an AND:

     

    =Fields!Qty.Value    >   =IIF(Parameters!myParam.Value="in",0,Nothing)     and  
    =Fields!Qty.Value   <=   =IIF(Parameters!myParam.Value="out",0,Nothing)  

     

     ... if using Nothing (null) creeps you out, you can use an impossibly high number in the first condition and an impossibly low number in the second condition instead <s>.

     

    BTW, notice that I used <=, not <, in the second condition.  I hope I am understanding your question correctly, but it seems to me that you want this. If I have not understood this, or something else, please holler. <s>

     

     

    >L<

    Saturday, July 14, 2007 2:54 PM

All replies

  • What does the selection in the dropdown box look like exactly?  Is it "out of stock" for one selection, and "out of stock" for the other selection ? 

     

    Here's how you do what you want, in any case: the trick is to have an "impossible" condition for one side of the IIF() in each of two filtering conditions, tied together with an AND:

     

    =Fields!Qty.Value    >   =IIF(Parameters!myParam.Value="in",0,Nothing)     and  
    =Fields!Qty.Value   <=   =IIF(Parameters!myParam.Value="out",0,Nothing)  

     

     ... if using Nothing (null) creeps you out, you can use an impossibly high number in the first condition and an impossibly low number in the second condition instead <s>.

     

    BTW, notice that I used <=, not <, in the second condition.  I hope I am understanding your question correctly, but it seems to me that you want this. If I have not understood this, or something else, please holler. <s>

     

     

    >L<

    Saturday, July 14, 2007 2:54 PM
  • Thank you!  Just what I was looking for.

     

    Saturday, July 14, 2007 3:19 PM
  • Actually, not working. 

     

    Here's the code:

    =Fields!InStockQty.Value   >    =IIF(Parameters!ViewBy.Value = "1", 0 , 10000000)     and

    =Fields!InStockQty.Value   <=    =IIF(Parameters!ViewBy.Value = "2", 0 , -10000000)

     

    Any ideas?  I have no control over the 'and' between statement.

    Saturday, July 14, 2007 7:35 PM
  • More information, please. <s>

     

    * -- does an error occur, or do you just not get the results you're looking for?

     

    * -- what do the values "1" and "2" mean (which is "in stock" and which is "out of stock")?

     

    >L<

     

     

     

     

    Saturday, July 14, 2007 9:17 PM
  • 1 is in stock, 2 is out...each statement works fine indepedently...the problem is the 'and' between the two statements, whch I have no control over...it needs to be 'or'.  Is there a way to get control over the and/or?
    Sunday, July 15, 2007 12:56 AM
  • no, actually, you don't need "or" <s>. "And" should work ...  I think you're not understanding how this works... or maybe I've gone insane, which right now is possible...

     

     but hang on a second, I'm in the middle of a production deploy and will write the example properly when I finish.

     

    >L<

    Sunday, July 15, 2007 1:18 AM
  • cool, thanks for helping out and I'm looking forward to your example
    Sunday, July 15, 2007 2:08 AM
  • I got it, thanks to you...no example necessarry!
    Sunday, July 15, 2007 2:46 AM
  • OK, I'm not insane, this really does work the way I told you <s>. 

     

    In your case (I have to write it again so I can see it here):

     

    1 is "in stock" and  2 is "out of stock".

     

    You wrote

     

    =Fields!InStockQty.Value   >    =IIF(Parameters!ViewBy.Value = "1", 0 , 10000000)     and

    =Fields!InStockQty.Value   <=    =IIF(Parameters!ViewBy.Value = "2", 0 , -10000000)

     

    ... and what you should have written is:

     

    =Fields!InStockQty.Value   >    =IIF(Parameters!ViewBy.Value = "1", 0 , -10000000)     and

    =Fields!InStockQty.Value   <=    =IIF(Parameters!ViewBy.Value = "2", 0 , 10000000)

     

    How this works:

     

    Let's think about a quantity of 5. 

     

    When ViewBy.Value = "1", you want this one to show up.  The first line of the filter says "is this quantity > 0 ? It gets the answer "yes".  Then the second line of the filter asks "is this quantity <= 1000000 ?" (because it uses the second half of the IIF()).  It, too, gets the answer "yes".  So this record is included in the report.

     

    When ViewBy.Value = "2", you don't want this record to show up. The first line of the filter says "is this quantity > 10000000?" It gets the answer "no", so it doesn't matter what the second half of the filter says, although the second half of the filter would also tell it "no".  The record is not included in the report.

     

    Now let's think about a quantity of 0 or -1 or some other negative number (I don't know if you have negative quantities for out of stock or not).

     

    When ViewBy.Value = "1", you don't want this one to show up.  The first line of this filter says "is this quantity > 0? " It gets the answer "no", so it doesn't show up.  I know the second line of the filter says "yes" but both conditions are not satisfied. It doesn't show up.

     

    When ViewBy.Value = "2", you do  want this one to show up.  The first line of this filter says "is this quantity > -1000000?"   It gets the answer "yes".  The second line of this filter also tells it "yes", so both conditions are satisfied.

     

    That's the best I can describe it.  I admit I didn't happen to have something with negative numbers to play with, I had to contrive an example, and in my example 0 is the lowest possible value (I used LEN() of a non-required field).  But it seemed to work fine to give me only one half of the record set, back and forth between the two values (in my case, only records with 0 length for this value, versus a positive value). I don't think it should matter that I used string valuels that were more recognizable than your "1" and "2" <g>. IAC, here are the values I used in the filter dialog.

     

    =LEN(Fields!Title.Value) > = IIF(Parameters!test.Value="titles_in",0,-100000) [and]

    =LEN(Fields!Title.Value) <=  =IIF(Parameters!test.Value="titles_out",0,100000)

     

    ... it works... I swear <g>.

     

    >L<

     

     

     

     

     

    Sunday, July 15, 2007 2:50 AM
  • I can't believe I just spent all that time trying to explain this <rofl>.

     

    Good for you!!

     

    Onwards and upwards...

     

    >L<

     

     

    Sunday, July 15, 2007 2:52 AM