force report to render in excel

Question

Post reply

force report to render in excel

tjaybelt

SSCrazy

Points: 2604
More actions
November 3, 2004 at 2:47 pm

#182612

I have a nice report that i created for a user, and he is thrilled that he can export it to excel. But its an extra step. Prior to giving him his own report that he can run on his own time, he requested from us the data, and that data was given in excel. Now he'd like to run the report, but have it render in excel instead of the nice report.
Do i have to have a subscription or something for it to auto render in another format?

thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 1

You can use URL access to render the report directly to Excel, something like this:

xhttp://MyServer/reportserver?/MyReport&rs:Command=Render&rs:format=EXCEL

(remove the leading 'x')

This assumes that your server is MyServer and the report is MyReport.

Jeff Buckley SSC Journeyman Points: 91 More actions · Answer 2

You can always put your information into a temporary table (formatted how you want) and then BCP the information out. I would suggest multiple steps, first build the report, then the bcp.

Jeff Buckley

tjaybelt SSCrazy Points: 2604 More actions · Answer 3

tjaybelt

SSCrazy

Points: 2604

November 4, 2004 at 8:20 am

#528927

thanks guys

Nick Datt SSC Rookie Points: 29 More actions · Answer 4

You could also "subscribe" to a report in the gui interface. The report would then be delivered to the users email inbox in the selected Excel format.

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 5

We have reports in Excel (primarily pivot tables) that are auto-refreshed when the workbook is opened. We simply added some code to perform the refresh when the Workbook_Open event is triggered.

tjaybelt SSCrazy Points: 2604 More actions · Answer 6

i am not an expert in excel. do you dare share how you accomplished this?

a sample, example, etc?

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 7

If you have a pivot table that currently uses external data, then you simply need to add the code below...

Private Sub Workbook_Open()

ActiveWorkbook.RefreshAll

End Sub

It's more complicated if you actually run a query against the datasource and paste the results into a worksheet. If this is what is needed, then it'll be something like below...

Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)

Set cnn = wrkODBC.OpenConnection("yourDSN", , , "ODBC;DATABASE=yourDatabase;DSN=yourDSN")

Set db = cnn.Database

db.QueryTimeout = 0

Set rs = db.OpenRecordset(yourSQL, dbOpenDynaset)

ReDim vaTmp(rs.Fields.Count)

For x = 0 To rs.Fields.Count - 1

vaTmp(x) = rs.Fields(x).Name

Sheets("yourWorksheet").Range("A2").CopyFromRecordset rs