CODEWITHSUNDEEP
functionreporting-servicesexpressionssrs-2012
Jonathan Porter
Jonathan Porter

Reputation: 1556

SSRS: How to filter query with custom function

Using VS 2015 and SSRS version 14.0.608.142

I have a function in the code section of my report properties similar to below and I'm trying to use it to filter out values in my dataset but it's not returning any values or giving any errors. The value inside the function would be changed at run time by another application so the value inside it right now is just a placeholder.

Function FilterBy() As String
    FilterBy = "'REMIT'"
End Function

I have the function being used as a default value in a parameter and the parameter is in the where clause of my dataset to filter out the necessary values.

So, inside parameter default value:

=code.FilterBy

Dataset query:

SELECT Blah 
FROM dbo.Table 
WHERE ID = @parameter;

Upvotes: 0

Views: 567

Answers (1)

Lucky
Lucky

Reputation: 4493

So, I'm noticing in your function that you're setting a variable but not returning it. If you're trying to set a Variable in the report properties, they can't be modified. Those things are static. You can, however, declare a variable in your code and use set and gets to retrieve them.

dim FilterBy = "REMIT" Function getFilter() As String return FilterBy End Function Function setFilter(input as String) FilterBy = input End Function

Therefore your parameter default value would be: =Code.getFilter()

Also notice that I'm not adding single quotes to REMIT. When you pass a parameter in your query you do not need to add single quotes to it, it will be added by SSRS. Unless the value is 'REMIT' in your database, then by all means, carry on.

Upvotes: 0

Related Questions