0

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.

enter image description here

Please guide me on how to fix this.

Pizza
  • 21
  • 5
  • Are the parameters mapped in the parameter tab of the Data Set? Did you enter a year and month for the parameters in the query designer? – Hannover Fist May 23 '23 at 23:10
  • 1. I try both; mapped and unmapped, still error. 2. not enter year and month for the parameters, I just mark as pass on both, and click Ok per the screenshot above. – Pizza May 24 '23 at 03:59
  • Does it work if you pass valid values? If you leave the parameters as NULL, the WHERE clause would be `WHERE Year = AND Month = ` which would give an error. – Hannover Fist May 24 '23 at 16:12

1 Answers1

0

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.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Thank you for the help. Unfortunately once I run the whole report, it gives me error "An error occured during local report processing, The definition of the report " is invalid. An unexpected error occurred while compiling expressions." – Pizza May 24 '23 at 03:55