none
Query filter is not working

    Question

  • Hi All,

    I created a SSRS getting values from a stored procedure ie the dataset1 . Then once the report displays i need to put a filter on age & gender to display only some limited set of values . I created an another dataset 2 wrote some query to get age & gender  filter and added thses as parameter in report also .

    but my report is not working when i apply filter at Age/Gender .

     

    Please any one helpme with this issue .

     

    Thanks

    SS

     

    Wednesday, November 02, 2011 10:57 AM

Answers

  • Hi Mark,

     

    As mentioned by you the columns age & gender is avalible in my dataset1 , and i have changed my filter to the dataset1  ,

    It is working , but the issue which i am facing now is, if i am not adding any filter at age/Gender my report wont show any values(those filter became a mandatory in report now ) , how will i put the filter as optional . I have already tired with allowing blank and null values option into the parameter .

     

    thanks

    SS

    • Marked as answer by CRM_Learner Thursday, November 03, 2011 3:27 PM
    Thursday, November 03, 2011 12:07 PM

All replies

  • Are you getting an error or no results?
    Wednesday, November 02, 2011 11:00 AM
  • Hi Mark,

    I am not getting any error, it is just not filtering the query when i apply filter on the report .

    Thanks,


    SS

    Wednesday, November 02, 2011 11:04 AM
  • Be sure that the first dataset is using the newly created parameters.  The parameter needs to be referenced in the query as well as mapped to the report parameter in the parameter tab of the dataset properties.

    Wednesday, November 02, 2011 11:24 AM
  • Hi Mark,

     

    I have Created 2 dataset2 , Dataset1 contains stored procedure dataset2 conatins a simple query which get age and gender form table .

    i have created 2 parameters from dataset2 .

    Thanks

    SS

    Wednesday, November 02, 2011 11:58 AM
  • Does dataset1's stored proc accept parameters?

    If so, add the parameters to the proc and I noted above.

    If not, you can add a filter to the dataset.  Goto dataset properties.  Click on the Filter tab and hit the button to add a filter.  Choose the column that you want to filter.  Then, in the value field, click on the expression button and choose the appropriate parameter as the value to pass in.

    Mark

    Wednesday, November 02, 2011 12:50 PM
  • Hi Mark,

    I have got 3 -parameter to run the report .  parameter1 is the mandatory i/p parameter from stored procedure (ID) it is from dataset1, parameter 2 is the query level filter parameter (age) this parameter is from dataset2 , Parameter 3 is the query level filter parameter (Gender) is from dataset2 .

    I have already added filter at dataset2 properties and chsoe the field also .

    Thanks

    SS

    Wednesday, November 02, 2011 1:03 PM
  • HI CR_Learner,

    You can use the parameters as condition in the main dataset "Dataset1" (in where clause)

    "where column_name=@parameter_name"

    Wednesday, November 02, 2011 1:12 PM
  • What is the relationship between dataset1 and dataset2?  Dataset2 is not being used to supply parameters to dataset1.  Each Dataset uses different parameters.  I missing what your expected outcome is.  Are you expecting parm2 and 3 to somehow filter dataset 1?  Yet, dataset1 does not accept those parms?
    Wednesday, November 02, 2011 4:27 PM
  •  

    Hi Mark,

     

    Currently I dindt make any relation ship between dataset1 & dataset2 .
    Yes i am expecting Parmeter 2 & 3 to some how filter dataset 1 on run time of the report .

    Please help me is any thing i missed out .

     

    Thanks

     

    SS

     

     

    Wednesday, November 02, 2011 4:37 PM
  • If the stored proc contains the columns age and gender, than you can use a filter on your dataset.  Follow the steps I mentioned above.  Does that make sense?

    mark

    Wednesday, November 02, 2011 9:28 PM
  • Hi Mark,

     

    As mentioned by you the columns age & gender is avalible in my dataset1 , and i have changed my filter to the dataset1  ,

    It is working , but the issue which i am facing now is, if i am not adding any filter at age/Gender my report wont show any values(those filter became a mandatory in report now ) , how will i put the filter as optional . I have already tired with allowing blank and null values option into the parameter .

     

    thanks

    SS

    • Marked as answer by CRM_Learner Thursday, November 03, 2011 3:27 PM
    Thursday, November 03, 2011 12:07 PM
  • Hi,

    Did you create a fliter on dataset or on the report field.

    Thursday, November 03, 2011 12:23 PM
  • Hi Praveen,

     

    I have create filter at dataset1 level .

     

    Thanks

     

    SS

    Thursday, November 03, 2011 12:39 PM
  • Change the parameters to start with a default value like "All".  So the parameters is values that you just entered, make "All" another option.

    Then in the filter, instead of choosing the column that you are filtering from the drop down, hit the expression button and put in something like this:

    =IIF(Parameters!Parm1.Value =

    "All", "All", Fields!Gender.Value)

    If the user chooser All, then the expression will evaulate to All = All.  If the choose something else, the column will be used and will compare against that.

    Mark

    Thursday, November 03, 2011 9:14 PM