none
Made a drop down list from report view result set table column and after that apply filter on that.. using SSRS

    Question



  • I want to make a drop down list from the report view table result set .

    I want to column name as value of drop down list .

    After that I want to filter result set on selected value filed + operator 

    like,=,>,  + text box value.

    expression like --

    ddl value + operator = 100;

    salary(ddl value) >= 2000(text box value) .


    Is it Possible ? 

    Please suggest me.

     

    Tuesday, August 19, 2014 10:42 AM

Answers

  • Hi Mukesh,

    According to your description, you want to select column name in the parameter drop-down list, then use the corresponding field to filter the tablix.

    After testing the issue in my environment, we can manually type the field name as the parameter available values, then use the parameter value as the field name to work around this issue. For more details, please refer to the following steps:

    1. Create a parameter named column in the report.
    2. Specify the field name as the Available Values:
      Label : name               Value: name
      Label : salary               Value: salary
      Label : age                   Value: age
    3. Add a filter like below to the tablix:
      Expression: =Fields(Parameters!column.Value).Value +20
      Operator: >=
      Value: 2000


    If there are any other questions, please feel free to ask.

    Regards,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, August 20, 2014 5:58 AM
    Moderator

All replies

  • This forum supports .NET Framework setup.

    Your questions on SQL Server Reporting Services will be best addressed here:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlreportingservices

    Tuesday, August 19, 2014 1:36 PM
  • I want to make a drop down list from the report view table result set .

    I want to column name as value of drop down list .

    After that I want to filter result set on selected value filed + operator 

    like,=,>,  + text box value.

    expression like --

    ddl value + operator = 100;

    salary(ddl value) >= 2000(text box value) .


    Is it Possible ? 

    Please suggest me.
    Tuesday, August 19, 2014 2:16 PM
  • Hi,

    You can easlly show parameter value in Column Name in Columntextbox where you want to show Parameter value

    right click -> expression -- > =Parameters!Name_of_parameter.Value.

    For Filter Right click on Tablix -> go to Filter tab  and Follow somthing like.;

    Please correct me if you require something else.

    Thanks

    Tuesday, August 19, 2014 2:51 PM
  • Thanks for Kind information..

    Yes , I have already applied such type of filtering on report. 

    But My Client requirement is He wants to populate drop down list values from table column name .

    Suppose I report table 

    which has column name,salary, age and many mores.

    Client wants dorp down value will be   name,salary, age and many mores  columns... after display report in report viewer.

    Is is possible? 

    Please suggest ..

    Tuesday, August 19, 2014 3:45 PM
  • It means you require column Names in Parameter Available Value Then

    As per my knowledge there is no direct approach for this If any workaround is ,then I'm not aware of that.

    but there is workaround for this ;

    if you are using any object in dataset to show data in tablix .

    then for parameter you can create one more data as .

    select 
        o.name,c.name 
        from sys.columns            c
            inner join sys.objects  o 
    		on c.object_id=o.object_id
     where o.name = 'DimPromotion'

    As i'm populating DimPromotion table data in Report.

    and in Parameter all the column name of DimPromotion .

    same way you can use your procedures (Type=P). 

    I know this is not the right way but forcefully solution. :)

    Note : Nothing Direct option we have in SSRS to show column Names in Parameter Available.

    Thanks

    Tuesday, August 19, 2014 4:28 PM
  • Thanks for your kind info....

    Is there any link on msdn for the same ?

    So, I can show it to my client  for clarification.

    Please suggest.

    Tuesday, August 19, 2014 7:00 PM
  • If you have static column name then you can write it manually.
    Please share your scenario so that I can answer it accordingly.

    but columns name are report item and ReportItem are not accessible in Parameter available value.

    You can show them below error msg to them.

    Wednesday, August 20, 2014 5:45 AM
  • Hi Mukesh,

    According to your description, you want to select column name in the parameter drop-down list, then use the corresponding field to filter the tablix.

    After testing the issue in my environment, we can manually type the field name as the parameter available values, then use the parameter value as the field name to work around this issue. For more details, please refer to the following steps:

    1. Create a parameter named column in the report.
    2. Specify the field name as the Available Values:
      Label : name               Value: name
      Label : salary               Value: salary
      Label : age                   Value: age
    3. Add a filter like below to the tablix:
      Expression: =Fields(Parameters!column.Value).Value +20
      Operator: >=
      Value: 2000


    If there are any other questions, please feel free to ask.

    Regards,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, August 20, 2014 5:58 AM
    Moderator