Using SSRS SQL Expressions

Why you would want to do this?

When you use an SSRS expression vs the SSRS built in filtering there are a couple of advantages

the lower level of your tech stack is sending fewer packets to the next level.
RDBMSs are really good at filtering data. (This means faster reports)
It will standardize where data is filtered in our ssrs reports (As there are several ways to filter in SSRS)

Tools needed:

Hard Returns remover:

I recommend the following:

https://www.textfixer.com/tools/remove-line-breaks.php

SQL Formatter

I Recommend the following:

http://poorsql.com/

When Using SQL Server:

I would suggest writing your SQL Statement with a common table expression. That way, when the parameters need adjustment or you need to add more of them, they are easy to find because they are at the top of your SQL Statement. When using multiple values, I typically do not use a common table expression (Not saying you can't, but you will have top get creative)

When using Progress DB

Unfortunately, OpenEdge Databases do not support Common Table Expressions at this time, so you will have to hunt in your SQL statement for the WHERE clause and put your parameter there.

Using multiple values:

I would suggest using the IN Command for this.

When using multiple values, if the values are text, they must be surrounded with 'single quotes'.

To accomplish this in progress I will set the parameter options to a query that has a SELECT list that looks like the following.


			SELECT CONCAT(CONCAT('''', mytable.myfield),'''') AS value            
				, mytable.myfield                            AS label
			FROM pub.mytable

Then the main query that the SSRS Report will execute will have a WHERE clause that looks like the following:

WHERE table.field IN (" + JOIN(Parameters!ParameterName.Value,"," + ")

Troubleshooting:

Compile time error:

When you save the report it refuses to save and is saying something about your main query

You probably have a quote that is out of place or you missed an SSRS concatenation operator.

Run time error:

The report will save, however it will not run and it is saying that your main query is broken.

You will need to make 2 dummy parameters that will be deleted later. Copy the sql expression to one of the parameters. After you fill in your other parameter values, this new parameter containing your sql expression will populate itself. Copy the code you your SQL formatting tool and then from there copy your formatted SQL to your favorite SQL IDE (I recommend DataGrip or Dbeaver depending on your situation)

From there you should be able to find where the problem is with your SQL statement either with syntax highlighting or when you try to run it, it will tell you a pretty good idea of what the problem is.