Report Export Options
When previewing an SSRS report there are multiple options for exporting the report to various formats. These options are found under the drop down menu in the tool bar, next to the save icon. Some of the available options are: Word, Excel, CSV, XML and Data Feed. This document will review two of these options, “Excel” and “Data Feed”.
Example: Document Listing Report
This report is found under the Contact Manager section of the Reports menu –> Document Listing.
Shown below are the results of the report. The selection criteria included a single campus “Campus Institute of Art” and only includes “Active” documents.
Report in the report viewer:
Export to Excel
On the save menu, selecting “Excel” will export the report and when the export is opened using Excel, the report will be avaiable and will be formatted similar to the report preview.
Document Listing Exported to Excel
Export to a Data Feed
Selecting “Data Feed” from the save menu, will create a file with the “.atomsvc” extension.
If you have the file association setup on your workstation, you can then select to open the file with a program that can use the atomsvc file. For example, you may have the PowerPivot add-on installed within Excel. If so, you can then select to open the .atomsvc file with Excel and the data feed will be imported into a new workbook.
To manually open the atomsvc file within Excel:
- Open Excel – Blank workbook
- Select (on the Data tab): “From Other Sources” –> “From OData Data Feed”
- Enter the location of your atomsvc file, or use the Browse button to locate the file.
Set the login credentials as necessary
- Select the Table to be used. Some reports have multiple data sources. If you are unsure which one contains the data for your report, you can select them all to view the contents.
- Click “Finish” to view the results within Excel
The raw data source for the report is now available in Excel. You will notice extra columns which are part of the underlying data, that was not visible in the report preview. This information may or may not be useful – you can always hide columns that are not important.
The data is not sorted as it was on the report, as the sorting is part of the report output. If you need it sorted, you can sort within Excel.
Refreshing the Exported Data
Now – return to the Campus Nexus Web application and navigate to the “Documents” list/reference item. Change the Active setting on one of the documents and edit the description of another.
Edit the Active setting on one document:
Change the Name property of another:
Go back to Excel where the spreadsheet based on the OData feed is open. On the Data tab, select “Refresh”. The document that was switched from Active “No” to “Yes” should now appear in the spreadsheet, and the Name of the document that was modified will also be updated in the spreadsheet. Refreshing the data re-runs the original query against the current state of the data items.
Select “Refresh” from the Data tab:
If you go back to the Excel spreadsheet that was exported directly from the report viewer, you will notice that the data remains static, as of the date and time that the report was originally run. This data cannot be refreshed without re-running the report and re-exporting the results.
Export to Excel
- Only data items that are visible on the report are exported
- Data remains static providing an “as of” view of the data
- Rows are sorted in the same order as they were in the report viewer
Export to Data Feed
- All elements in the data set used for the report are exported, even those that are not visible in the report viewer
- Data may be refreshed to provide the current view of the data
- When refreshed, the original query is re-run against the back end and the data in the spreadsheet is updated
This document uses a very simple example based on the Documents List. But, when applied to report output such as “Past Due Students”, “Student Class Rosters”, “Students on Hold Report”, etc. it will become more obvious how useful having an active data source within an Excel spreadsheet can be. The data can be refreshed when needed and any analysis or tables or pivot tables based on the data will also be refreshed.