CODEWITHSUNDEEP
sql-serverreporting-servicesexport-to-excel
jjohnson
jjohnson

Reputation: 203

Export SQL query to excel with multiple worksheets and custom headers

What I need to do is, a client wants to have a report in an excel doc with multiple worksheets with custom headers. I have tried SSRS 2008 Report Builder 2.0 but naming worksheets is not available with SSRS 2008 Report Builder 2.0. I have tried bcp in SQL Server Management Studio, but am not able to export it into multiple worksheets. I have put the queries into temp tables, is there a way to export those queries into the same excel doc but different worksheets with a different header for each worksheet.

Like thisenter image description here enter image description here enter image description here enter image description here

Notice how each worksheet has a different name and a different header.

Is this possible to do with SQL or is there a workaround for SSRS 2008 Report Builder 2.0?

Upvotes: 2

Views: 4128

Answers (2)

Francesco Mantovani
Francesco Mantovani

Reputation: 12277

I know, I know... you too you faced the error:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

You can do it in SSMS through T-SQL, follow this example:

First you need to allow SSMS to bypass the error:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

Then you can save the result in a precise Excel Tab this way:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM [Sheet1$]') 
SELECT * FROM dbo.DimScenario

The file .XLSX must already be there with the tab having the precise name [Sheet1$]

Upvotes: 1

Greg the Incredulous
Greg the Incredulous

Reputation: 1846

You could use SQL Server Integration Services 2008R2 (SSIS) to do this. SSIS has an Excel Data Flow Destination that accepts a worksheet name as a parameter. You could construct your SSIS package to populate the various worksheets of a spreadsheet this way.

Upvotes: 3

Related Questions