none
Show all Data or Filter by a Word in SSRS 2010

    Question

  • Hey all, I am creating a report.  Basically I am searching for the word XSEC in the column FINAL_XSEC.  The column either contains the word XSEC or InBid for each record.  What I would like my parameter to do (which is called XSEC_or_Not) is be a drop down box that either allows someone to choose to search just for XSEC and return only records with XSEC in the FINAL_XSEC column or Return all results with XSEC or InBid in the FINAL_XSEC column.  I cant quite figure out how to set up this though in the filtering and in the parameter options.  Can anyone help?
    Friday, March 14, 2014 8:05 PM

Answers

  • Hey there Martina,

    When I do that it gives me data for one or the other (either InBid or XSEC) even when I select both in the drop down box.  If I do select both it gives me only XSEC and not InBid as well.  I have the report parameter set up as allow multiple values (named XSEC_or_Not).  Available values are Label: XSEC Value: XSEC and Label: InBid and Value: InBid.  Then I go down to my dataset, right click and hit field properties and go to the filters tab and put in as you said above: Expression:[FINAL_XSEC], Operator: In, and Value: =Parameters!XSEC_or_Not.Value(0).  Again this will give me results for one or the other, but not both.  If I just select XSEC, it gives me all XSEC's.  This is the same for InBid if I select it alone.  If I select both (or select all, or XSEC, InBid) it only gives me XSEC.  I really appreciate your help, I hope that this last small part can be fixed. Thanks!

    Monday, March 17, 2014 1:17 PM

All replies

  • Set up your @XSEC_or_Not parameter to have two available values in it XSEC and ALL

    In your report query WHERE clause use:

    WHERE FINAL_XSEC IN (@XSEC_or_Not)   OR   'ALL' IN (@XSEC_or_Not)


    Martina White

    Friday, March 14, 2014 8:27 PM
  • The XSEC_or_Not Parameter and the column FINAL_XSEC were made after the SQL query inside the report.  Rather, it is a report parameter and a calculated field (for the FINAL_XSEC) so I can't put them directly in the SQL to retrieve data from the server.  I need to use the filter area (right clicking on data set and clicking the dataset properties and then going to filter) but I just can't figure out how to get it to work in the filter area for this particular case.
    Friday, March 14, 2014 8:35 PM
  • In that case, make your parameter multi-select, allow the user to select XSEC and/or InBid.  Complete the filter area as, where FINAL_XSEC IN @XSEC_or_Not

    Martina White

    Friday, March 14, 2014 8:59 PM
  • Hey there Martina,

    When I do that it gives me data for one or the other (either InBid or XSEC) even when I select both in the drop down box.  If I do select both it gives me only XSEC and not InBid as well.  I have the report parameter set up as allow multiple values (named XSEC_or_Not).  Available values are Label: XSEC Value: XSEC and Label: InBid and Value: InBid.  Then I go down to my dataset, right click and hit field properties and go to the filters tab and put in as you said above: Expression:[FINAL_XSEC], Operator: In, and Value: =Parameters!XSEC_or_Not.Value(0).  Again this will give me results for one or the other, but not both.  If I just select XSEC, it gives me all XSEC's.  This is the same for InBid if I select it alone.  If I select both (or select all, or XSEC, InBid) it only gives me XSEC.  I really appreciate your help, I hope that this last small part can be fixed. Thanks!

    Monday, March 17, 2014 1:17 PM
  • Hi Graham,  Just remove the (0) from the filter Value =Parameters!XSEC_or_Not.Value(0)  It should read =Parameters!XSEC_or_Not.Value    Otherwise you are choosing the first value from the array.

    Martina White

    Monday, March 17, 2014 1:26 PM
  • Oh gotcha!  Thanks so much for your help, I appreciate it!
    Monday, March 17, 2014 1:34 PM