Hello guys, I am new in SSRS 2005 and I need some help from you. I am developing my first reports and my issue is the following. After having executed the report I should be able to filter for instance by using a drop box. in a few words, selecting the parameter, the data is shown in the report depending on the parameter selected in the dropbox. for example, the dataset produce the report: col1, col2, col3, colfilter1, colfilter2, colfilter3 I need a filter that select for example: colfilter3, so the report now gets: col1, col2, col3, colfilter3 Then, from the drop box I select colfilter1, and I get: col1, col2, col3, colfilter1 and so on..... I know that building a query in the dataset with @paramter as a filter I have the dropbox; but this drop box is used before running the report just to set the parameters. What I need, it is a filter tool like a dropbox to be used when the report is already shown. Otherwise if I have lots of parametr I need to launch again the report and select all the dropbox available to run the report. Could you help me Many thanks

Hi, Based on the example you posted, some columns in the report need to be hidden and shown conditionally. Generally, we can also control the visibility of columns by using parameter, please refer to the steps below: 1. Add a new parameter to the report with the following settings. Name: ColumnFilter Data type: String Available values: Label Value colfilter1 colfilter1 colfilter2 colfilter2 colfilter3 colfilter3 2. Select the handle of the column colfilter1, specify the Hidden property to the expression like = NOT (Parameters!ColumnFilter.Value="colfilter1") 3. Repeat the step2 to set Hidden of cofilter2 and colfilter3. After that, the columns colfilter1, colfilter2, colfilter3 can be shown only if the corresponding value is selected in the parameter. We can also specify multiple values in a parameter, please refer to the steps below: 1. In the Report Parameters dialog, check the option Multi-value. 2. Modify the expression in the Hidden property of each column to: = (InStr(Join(Parameters!ColumnFilter.Value,","),"colfilter1")=0) For more information about Using Parameters to Control Report Appearance, please refer to: http://msdn.microsoft.com/en-us/library/aa337155(v=SQL.90).aspx About Using Single-Valued and Multivalued Parameters, please refer to: http://msdn.microsoft.com/en-us/library/aa337292(v=SQL.90).aspx If you have any question, please feel free to ask. Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Thank for your help, I am going to apply your suggestions. I will ask more help in case of help. Many thanks

Hi, going through the development, I have had to modify the query therefore now the filter is not applied on the column, but there is only one column on which the filter should be applied. After having seleted the appropriate value from the drodown box, the report show only records that satisfy the secection. The filter must be applied not in runtime, but a report level. Could you help me? Kind regards.

Hi, Do you mean you are using a matrix in the report, therefore in Layout mode there is only one column and it will be converted to multiple columns in Preview? If I have misunderstood, please point out. If you are using a matrix, instead of adding parameter in the query level to filter columns, we can add the filter on the matrix level. For example, if we have specified a data field Fields!Year.Value to the column group of the matrix, then in Preview the report would look like 2002 2003 2004 Category1 xxx xxx xxx Category2 xxx xxx xxx Category3 xxx xxx xxx … … … … In order to control the visibility of year columns by using parameter, please refer to the steps below: 1. Add a parameter ColumnFilter with the following settings. Name: ColumnFilter Multi-value: Checked Available values: Label Value 2002 2002 2003 2003 2004 2004 2. Right click the handle of the matrix, select Properties. 3. In the Matrix Properties dialog, select the Filters tab. 4. Add a new item in the Filter list with the following settings: Expression: =Fields!Year.Value Operator: In Value: =Parameters!ColumnFilter.Value After that, we can select values of the parameter to control visibility of corresponding columns. For more information about How to Add a Filter, please refer to: http://msdn.microsoft.com/en-us/library/ms156270(v=SQL.90).aspx Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi and thank for your contribution. The report shows just as example: Col1 col2 house1 green house2 red house3 green I need a dropdown box (green, red, all) From the drodown box I select "green", the report gets: Col1 col2 house1 green house3 green Then, from the drodown box I select "red", the report gets: Col1 col2 house2 red From the drodown box I select "All" the report gets: Col1 col2 house1 green house2 red house3 green Could you help me? Many thanks

Hi fasttrack, Thank you for providing the example. We can also achieve this by adding a filter to the table. 1. Add a parameter ColumnFilter with Multi-value checked. Also specify its Available values. 2. Right click the handle of the table, select Properties. 3. In the Table Properties dialog, select the Filters tab. 4. Add a new item in the Filter list with the following settings: Expression: =Fields!Color.Value Operator: In Value: =Parameters!ColumnFilter.Value Please change the field name and parameter name based on your report. After that, we can select values of the parameter to filter the rows in the table. Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi, and thanks very much for your info very detailed and I am going to apply. I need a further help: when you write: Add a parameter ColumnFilter with..... What do you mean? could you give me the right directions on how to add the parameter. Many thanks.

Many thanks for your advise: I have applied your suggestions and I found this: 1) the dropbox used to filter is applied directly on the report or run again the query? I ask for this since the query takes time to be executed. 2) What learnt is very interisting, but the filter is applied on "=Fields!Color.Value" . In my case the "green", "red"... are not from queries but edited directly in the table in the design view. So my question is: how can I filter on row if I CANNOT pass the "=Fields!Color.Value" . I mean: hide/show the TableRowN by applying the filter from the dropdown box? Thanks.

Hi, Based on the discussion above, in order to solve the issue we need to confirm the requirement and the current report structure at first. Currently, from my understanding, some rows with static values (edited directly in the table) need to be hidden or shown based on a parameter with available values (dropdown box). If change the value in the dropdown box and click the View Report button, you don’t want to execute the query again, correct? If I misunderstood, please point out with no hesitation. If possible, please also post a screenshot of the current report for elaborating the needs. Generally, in order to improve the performance of report execution, we can use cache or snapshot. In this way, the query of the report is not executed for each request. For more information about Setting Report Execution Properties, please refer to: http://msdn.microsoft.com/en-US/library/ms159241(v=SQL.90).aspx For detailed steps about adding a report parameter, please refer to the following link: http://msdn.microsoft.com/en-us/library/ms159266(v=SQL.90).aspx Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

YES, I have a picture for you, but I do not how to load: I do not see any item to load picture. Yes it looks like well undestood: there some rows: the first column have a static valued edited directly in the design. the other columns keep values from dataset. For example the table is (col1 is edited and static from the report design, col2 and col3 have data from the dataset): col1 col2 col3 row1 green field21.value field31.value row2 red field22.value field32.value row3 green + red field23.value field33.value The report should show result data as follow: 1) from the dropdown box: GREEN col1 col2 col3 row1 green field21.value field31.value 2) from the dropdown box: RED col1 col2 col3 row2 red field22.value field32.value 3) from the dropdown box: ALL row3 green + red field23.value field33.value Many thanks for very useful help.

YES, I have a picture for you, but I do not how to load: I do not see any item to load picture. Yes it looks like well undestood: there some rows: the first column have a static valued edited directly in the design. the other columns keep values from dataset. For example the table is (col1 is edited and static from the report design, col2 and col3 have data from the dataset): col1 col2 col3 row1 green field21.value field31.value row2 red field22.value field32.value row3 green + red field23.value field33.value The report should show result data as follow: 1) from the dropdown box: GREEN col1 col2 col3 row1 green field21.value field31.value 2) from the dropdown box: RED col1 col2 col3 row2 red field22.value field32.value 3) from the dropdown box: ALL row3 green + red field23.value field33.value Could you give me more information on how to get it? Many thank

YES, I have a picture for you, but I do not how to load: I do not see any item to load picture. Yes it looks like well undestood: there some rows: the first column have a static valued edited directly in the design. the other columns keep values from dataset. For example the table is (col1 is edited and static from the report design, col2 and col3 have data from the dataset): col1 col2 col3 row1 green field21.value field31.value row2 red field22.value field32.value row3 green + red field23.value field33.value The report should show result data as follow: 1) from the dropdown box: GREEN col1 col2 col3 row1 green field21.value field31.value 2) from the dropdown box: RED col1 col2 col3 row2 red field22.value field32.value 3) from the dropdown box: ALL row3 green + red field23.value field33.value Could you give me more information on how to get it? Many thank

Hi fasttrack, Just based the example, we can set the Hidden property of each row dynamically by specifying expression. For example: 1. Add a parameter named Color with the available values Green and Red, also enable Multi-value. 2. Select row1 by click its handle, specify the Visibility -> Hidden property to the expression like: =(InStr(Join(Parameters!Color.Value,”,”),”Green”)<=0) 3. Select row1 by click its handle, specify the Visibility -> Hidden property to the expression like: =(InStr(Join(Parameters!Color.Value,”,”),”Red”)<=0) 4. Select row1 by click its handle, specify the Visibility -> Hidden property to the expression like: =(InStr(Join(Parameters!Color.Value,”,”),”Green,Red”)<=0) In order to improve the performance, as I posted, we can configure Report Execution Properties to use cache or snapshot after deployed the report to the Report Sever. Since the query of the report is not change for different parameter value specified, the query would not be executed again. Additionally, in order to post a picture in the reply, we can first upload the picture to the public folder in skydrive, and then copy it in the reply. Hope this helps. Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hello Tony, Following your examples I am learning lots of things. About the last case (column with fixed values): I tried to apply what you explained, but running the report I got all the entire table, and filtering I get the entire table again. In the "Edit expression" (in the visibility property) I get the pop up: "Wrong number of arguments" (I copied and pasted the line above, may be wrong?). Any help will be very appreciated, Many thanks

Hello Tony, Following your examples I am learning lots of things. About the last case (column with fixed values): I tried to apply what you explained, but running the report I got all the entire table, and filtering I get the entire table again. In the "Edit expression" (in the visibility property) I get the pop up: "Wrong number of arguments" (I copied and pasted the line above, may be wrong?). Please let me know. Many thanks

Update, thank very much for the help. I tried again and now it is working.