Caveats of using an Expression for SQL Script in SSRS.

Why: Most of the time, when you want a flexibility of your SQL query you can use parameterization. However there might be a situation when you'd need to build a dynamic query. In my case I used SQL query within an expression to feed it to multiple data sources targeting different servers with the exact same query.

DataSet creation: Creation of a simple dataset.

I've created a sample dataset with a sample query:

-- That is a sample query
select top 10 * 
from sys.messages

After we created the dataset we can create a Tablix in our report and test it:

Creation of an Expression: 

1. Create a new parameter called "SQLExpression"

2. Go to the "Default Values" tab select "Specify values" and choose "fx" box

3. Specify Expression value:

Assign Expression to the DataSet: Return to the Sample DataSet and Specify newly created parameter as the source for DataSet Expression.

Within the expression replace the query by the parameter:

Save changes and try your report.

If  you've done exactly like I did you get an empty report.

Troubleshooting: If you specified your parameter as "Visible" you might notice that report processor aggregated all 3 rows of our query into the one, making it a single comment

Other cases: My case was very easy, but in the most of the cases you'll usually get a weird error message for your query, while the query itself runs fine in SSMS.

Lessons Learned: When we use SQL Query Expressions we have to follow these rules:

1. Use only DOUBLE comment or do not use comments at all.

2. Put at least one space or a tabulation before the very first symbol on each line.

3. Use semicolon symbol to separate multiple SQL instructions.

4. Do not use "GO" command.

The Fix: Change the default value for "SQLExpression" parameter to following query: 

/* That is the Fixed SQL Query
 refurbished for SSRS expression usage*/
 DECLARE @MessageID INT = 101;
 SELECT *
 FROM sys.messages
 WHERE message_id = @MessageID;

Save the parameter and re-run the report.

Enjoy the result of your SQL Query Expression:

Conclusion: Use of SQL Expressions is very easy if you fallow those 4 rules. 

Please let me know if you hit any other situation in question and I'll add it for others to avoid.