We are implementing Microsoft Reporting Services 2008 and we are very new to this product. I am working on my first report and I am having a problem setting up a complex filter. We are migrating from Oracle Reports and are on an Oracle database. Here is my question. I have an AR Aging report. I need to limit the content of the report based on a parameter selected at run time. The parameters are too complex to include in the SQL query so I am trying to set the filter on the dataset with the following, which I know is not correct: ---- if the parameter = “FOREIGN” retrieve all records that do not equal ‘USD’ ---- if the parameter = “USD” retrieve all records that equal ‘USD’ ---- otherwise retrieve all records =IIF(Parameters!CURRGROUP.Value Like "FOREIGN", Fields!CURRENCY.Value <> "USD", IIF( Parameters!CURRGROUP.Value Like "USD", Fields!CURRENCY.Value Like "USD", Fields!CURRENCY.Value=Fields!CURRENCY.Value)) In Oracle we have the following logic in the after parameter form: IF :p_curr_group like 'FOREIGN' THEN :p_curr_group_clause := 'AND currency not like ''USD'''; elsif :p_curr_group like 'USD' THEN :p_curr_group_clause := ' AND currency like ''USD'''; END IF; Also, for this particular report I have 3 other similar type filters that are even more complex. I would appreciate it if you could point me in the right direction for this information. Regards, Trish LeppaTrish Leppa

Hi Trish, try this, = IIf ( Parameters!CURRGROUP.Value = "FOREIGN", Fields!CURRENCY.Value <> "USD", IIf ( Parameters!CURRGROUP.Value = "USD", Fields!CURRENCY.Value = "USD", Fields!CURRENCY.Value ) ) Please let us know your feedback. Thanks KumarKG, MCTS

Hi, If I leave the CurrGROUP parameter to the default (ALL) then the report returns no data. If I change it at run time to USD or Foreign I get an error. 'The processing of the filter expression 'DataSet1' can not be performed. Can not compare data types System.boolean and System.string. Thanks, Trish Trish Leppa

Hi Trish, I think your Paramter Data-type doesn't matches with the DataSet column data-type. Please match accordingly. I think you are doing here some minor mistake, its not a big deal. Thanks KumarKG, MCTS

Hi Trish, To achieve your purpose, you need to do a little change which add a non-USD currency identitifer in your query. Here i take an example in T-SQL.I believe it is the same concept in Oracle. select*, Case CURRENCY then 1 else 0 end as CurrencyIdfy from your table. now, you can either add a where clause in the above query or set up a filter on the report dataset to filter your query data. The report Multiple-valued parameter has 3 values, Label "FOREIGN" - Value 0, Label "USD" - Value 1. The change to add a where clause shoud be like this: select*, Case CURRENCY then 1 else 0 end as CurrencyIdfy from your table where CurrencyIdfy in (@parameter) The change to add a filter on the dataset should be: Field expression: Fields!CurrencyIdfy.value Operator: In Operator: @Parameter If you have more question, please let me know. To Kumar03, The error message occurs because the true/false parts in your expression is also a logical expression = IIf ( Parameters!CURRGROUP.Value = "FOREIGN", Fields!CURRENCY.Value <> "USD", IIf ( Parameters!CURRGROUP.Value = "USD", Fields!CURRENCY.Value = "USD", Fields!CURRENCY.Value ) ) so the expression will return true or false (system.boolean), which will compare to the feild Fields!Currency (System.String) of the filter. So the error occurs in the customer report. thanks, Jerry

ok, thanks. I think I get what you are suggesting. THanks so much!Trish Leppa

Hi, Ok, I added the case statement to the Select to make the column be a selectable value as you suggested. Then I added a filter to the Dataset. NOw I find that if I set the filter to be tied to a parameter with 'Multiple values", it defaults to the first value in the list. Ayn suggestions on the filter allowing multiple values? THanks, TrishTrish Leppa

Hi, Ok, I added the case statement to the Select to make the column be a selectable value as you suggested. Then I added a filter to the Dataset. NOw I find that if I set the filter to be tied to a parameter with 'Multiple values", it defaults to the first value in the list. Ayn suggestions on the filter allowing multiple values? Also, I wanted to mention that I have only two values in parameter. I have added them both to the default values. Now if I select each one individually the report retrievs the data ok, but if I select the 'ALL' option it only retrieves the first default value. THanks, Trish Trish Leppa

Hi, I finally figured this out. First I added the case statements to the Query and give it an alias: Case sys_currency_code when 'USD' then 'USD' else 'FOREIGN' end CURRGROUP Now create a parameter named CURRGROUP and add two valid values: USD FOREIGN Add the same values as the default values on the parameter definition. Now create a filter on the dataset selecting the Field/expression CURRGROUP with an operator of IN and now set the value to [@CURRGOUP] including the brackets. Thanks, Trish Trish Leppa