SSRS BIDS 2005 to 2015 Data Tools migration: Filters with IN operator migrating improperly

Question

I am migrating an SSRS 2005 BIDS project to Visual Studio 2015 Data Tools and I am seeing issues with migrating DataSet filters that use the IN operator. The reports seem to work correctly, but when I view the Filter expression in the designer only the first operand in the list appears, and the rest are missing. However, if I view the code (.rdl source) I see all of the operands. Worse yet, If I close the Filters window using the OK button only the first operand remains and the rest are removed from the .rdl file.

Here is an example filter from the .rdl code:

<Filter>
  <FilterExpression>=Fields!DecodeType.Value</FilterExpression>
  <Operator>In</Operator>
  <FilterValues>
    <FilterValue>="Team"</FilterValue>
    <FilterValue>="ALL"</FilterValue>
  </FilterValues>
</Filter>

The .rdl code for the filter operator and filter values is identical in both 2005 and 2015 tools. The issue appears to be with the 2015 (or 2008 for that matter) designer.

Questions:

Am I doing something wrong, or is this a known issue?

What syntax should I be using instead. Split("Team,ALL",",") which works, but some of my filters check for Nothing in the list of possible values (see .rdl filter values below)

<Filter>
  <FilterExpression>=Fields!DecodeType.Value</FilterExpression>
  <Operator>In</Operator>
  <FilterValues>
    <FilterValue>=2</FilterValue>
    <FilterValue>=Nothing</FilterValue>
  </FilterValues>
</Filter>

NOTE: This same issue occurs migrating from 2005 BIDS to 2008 BIDS. I found this by trying a progressive migration through each version :(

KyleLib · Answer 1 · 2016-09-27T12:34:10.607

I found that the issue really has to do with how the Data Tools are dealing with the IN filter at design time. My solution was to use a work around using =split() that I don't really like, but it works:

="Team,ALL".split(",")

The documentation says that I should simply be able use the following in my IN clause without any equal sign:

"Team","ALL"

However next time I open the filter it looks like this without the quotes:

Team, ALL

Then, if I click OK on the Data Set Properties page again and reopen the filter the Values then looks like this:

"""Team""","""ALL"""

Not really an option, thus the reason for using =split().

Just FYI, when your field is an integer then you can list the parameters without an equal sign (=) in the expression like this:

2,4,9

Hope this helps the next person.

* Update * In the scenario where Nothing (null) was a possibility I tried this and it seems to work:

Edit the Filter and in the Expression (where you'd typically select a field from your dataset) add this as the expression:

=iif(IsNothing(Fields!myField.Value), -1, Fields!myField.Value)

In my case that begets an integer, so I set my datatype for the expression to Integer. Sadly I'm resorting to a magic number here (-1 in this case)

Now you can say this in the Value field of the filter:

-1,2,4,9

Alternatively, if your testing strings and "Nothing" is one of the options use this expression instead:

=iif(IsNothing(Fields!myField.Value), "NULL", Fields!myField.Value)

And then the test in the Value field of the filter looks like this:

=="Team,ALL,NULL".split(",")

Yet another magic thing (in this case "NULL"), but it gets me around the limitations of the tool.

Again, I hope this helps others.

SSRS BIDS 2005 to 2015 Data Tools migration: Filters with IN operator migrating improperly

1 Answers1