SSRS + BigQuery: How to pass SSRS's Parameter into BigQuery query syntax?

Question

Background:

I have an existing SSRS report (Report Builder) which is connected to SQL server as a data source. The report contains main query, and parameters. In below screenshot, they are 'MainQuery' dataset, and 'Year' and 'Month' parameters respectively.

Currently the source has been changed to BigQuery. Then I need to change the query syntax. The 'parYear' and the 'parMonth' run and produce results successfully until...

Problem:

The 'MainQuery' which has the parameters (@Year and @Month) in where clause gives me error.

Please guide me on how to fix this.

Answer 1

I don't know how BigQuery works or the syntax needed but the query can always be built using the Data Expression where the parameters can be incorporated into the text.

Press the Fx button in the Data Set Properties page (the middle of the screenshot) to open the Data Set expression builder. Then create text with the parameters injected.

="SELECT * 
FROM dbo.dimDate 
WHERE YearKey = " & Paramters!Year.Value & "
AND MonthKey = " & Paramters!Month.Value 

This would pass the SQL to the database as

SELECT * 
FROM dbo.dimDate 
WHERE YearKey = 2023
AND MonthKey = 5 

Your parameters should be selections of possible numbers and not a free form entry to avoid SQL injection </ disclaimer>.

We've used this with a few other databases where we couldn't figure out the parameter syntax.