Developing SSRS Report Using Data Source From Database and Excel

0
votes

I am developing a SSRS report pulling most of the data from a SQL Server database.

However, there is a small set of data not available in the database which I get from an Excel sheet daily.

Is it possible to develop this report pulling data from both the database and the Excel sheet?

1
excelreporting-servicesssrs-2012

1 Answers

2
votes

In short, yes you can do this.

NOTE: Both the below approaches require some drivers to be installed on your server

Option 1: Direct access from SSRS

You could add a second datasource that points to your Excel file directly via an ODBC or OLEDB connection . You would then need to use lookups to effectively 'join' the data to your SQL data. There are several guides on how to use Excel data as an SSRS datasource out there.

Option 2: Access Excel data from t-SQL

However, my approach would be to do this on your SQL Server using OPENROWSET. This way you can access the data directly in your query and join it to your SQL data before you pass the results to your report.

Here's some a typical code snippet I would run to get Excel data into my database for further processing

SET @Cols = 'Country, Category, Period, Amount'
SET @Select = 'SELECT FROM [myExcelSheetName$A1:Z1000] '
SET @path = '\\myfileserver\myfolder\'
SET @filename = 'myExcelFile.xlsx'

SET @ExecCmd = 'INSERT INTO myExcelImportTable (' + @cols + ')
            SELECT ' + @cols + ' 
            FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database=' + @path + @filename + ';HDR=YES;IMEX=0'',''' +  @select + ''')
            WHERE Country != ''Country'' AND ISNULL(Country,'''') != '''' '

PRINT @ExecCmd
EXEC (@ExecCmd) AS LOGIN= 'myElevatedLoginName'

**Note: ** myElevatedLoginName is a SQL login that has admin level access. During testing/debugging I usually connect to my server with this login so you can run the t-sql commands directly.