SSRS with Snowflake

Question

We have many Scheduled, and User-Run SSRS Reports that were running against a SQL Server Database.

Since we have moved our Production DB to Snowflake, we want to recreate the reports to get their data from Snowflake.

As a first step, I have re-worked the SQL in a 3rd Party SQL Query Designer named 'DataView' to pull in the required data from Snowflake.

The next thing I have done is use 'OpenQuery...' syntax in an SSRS report to create a very basic SSRS report.

Here's the Dataset syntax:

Select * From OPENQUERY 
    (
    SNOWFLAKE, 
    '
    Select Top 10 CAST(FirstName as VARCHAR(8000)) as FirstName 
    From DB_Name.Schema_Name.Patient
    ORDER BY FirstName
    '
    ) 

This works fine in SSRS but I have a few questions.

The actual SQL I will be using is around 350 lines long. Do I have to Cast each String value as Varchar(8000) in order for the SSRS report to run? How do I pass Start Date and End Date variables into the SSRS report to filter the output? What is the ideal way to use Snowflake SQL to create a report for Users to run with their own Date Range?

If SSRS [using OpenQuery syntax] is not the most elegant way to create User-Run reports, I'd like to know what IS the best Reporting tool [Power BI?] to use.

I'd appreciate any pointers I can get.

Thanks in advance!

Answer 1

To connect to Snowflake in a paginated report, install the Snowflake ODBC driver

Create 64-bit system DSN following the directions in the Snowflake documentation.

Then in PowerBI Report builder (or SSRS Report Builder) create a data source referencing that DSN:

and set the credentials