By: Ray Barley | Last Updated: 2010-10-19 | Comments (19) | Reporting Services Data Sources
I noticed a new feature in SQL Server 2008 R2 Reporting Services called ATOM data feeds. This sounds very interesting. Can you provide an example of how I can use this? Check out this tip to find out.
The ATOM data feed allows you to extract the data from a SQL Server 2008 R2 Reporting Services report. It's as if the report itself is a data source. Each data region on the report becomes a data feed. Currently there are two choices for consuming the data feed; you can use Excel 2010 with the PowerPivot add-in or use the tool of your choice to operate on the data which is in XML format.
As an example I created a simple report using Report Builder 3.0 and deployed it to the Report Manager. A portion of the report rendered in the Report Manager is shown below:
Notice the toolbar icon enclosed by the blue rectangle as shown below:
This icon has the tooltip text "Export to Data Feed". Click the icon to begin the export. We'll go through the export by first looking at the raw files then we'll import the data feed into an Excel 2010 PowerPivot worksheet.
Working with the ATOM Files
After clicking the Export to Data Feed icon you will be prompted with the familiar File Download dialog where you can choose Open, Save or Cancel as shown below:.
Clicking Save will put a copy of the file ResellerSales.atomsvc in the folder of your choice; clicking Open will launch Excel 2010 and step you through a wizard to import the data into PowerPivot. Let's first take a look at the atomsvc file; we'll go through the Excel 2010 PowerPivot wizard later. The atomsvc file is an XML file as shown below:
The following are the main points about the atomsvc XML file:
atom:title directly under workspace is the name of the report
There is a collection element for each data feed in the report (the sample report contains only one data feed)
The href attribute of the collection element is the URL of the atom data feed; you can navigate to this URL in your browser (make sure to change & to &) to get at the data provided by the data feed (it will be in XML format)
The atom:title inside the collection is the name of the data region in the report
The URL is pretty standard for launching a report; note that the Format parameter is ATOM and the DataFeed parameter is a hexadecimal value that is automatically assigned
If you put the URL of the atom data feed in your browser, you'll get the familiar File Download dialog with the Open, Save and Cancel options as shown below:
Click Save to download the atom file that contains the data in the data feed in XML format. A portion of the atom file is shown below:
The entry element represents a single row of data in the data feed. By specifying the URL you can directly access the atom file and render the XML by writing code or using the tool of your choice.
Working with the Excel 2010 Table Import Wizard
The Excel 2010 Table Import Wizard is included with the PowerPivot add-in; you can download the add-in from the Microsoft SQL Server 2008 R2 Feature Pack site. PowerPivot provides enhanced pivot tables and charts for Excel 2010. You can launch the Table Import Wizard by clicking on the Export to Data Feed icon on the report toolbar or clicking From Report on the PowerPivot ribbon. I'll launch the wizard from the Export to Data Feed icon on the report toolbar.
After clicking the Export to Data Feed icon on the report toolbar, you will be presented with the familiar File Download dialog (as shown in the previous section); click Open to launch the Table Import Wizard. The Connect to a Data Feed dialog will be displayed as shown below (I filled in the Friendly connection name):
Click Next to proceed to the Select Tables and Views dialog as shown below:
Click the checkbox(es) of the tables and views in the data feed that you want to import. The Source Table name of Tablix1 is the name of a data region in the report (you should specify a more descriptive name in the report properties). You can click Preview and Filter to view the data and select which columns you want to import. The dialog is shown below:
By default all columns are selected; you can uncheck any columns that you do not want to import. Click OK to complete the table wizard. A dialog will be displayed showing the import results as shown below:
You can now view your data feed(s) in the PowerPivot worksheet as shown below (only a portion of the sheet is shown; there will be a separate worksheet for each data feed that you imported):
Now that you have imported the data feed into PowerPivot, you can create all sorts of nifty pivot charts and tables; e.g. here is a pivot chart that shows the quantity ordered by category and sliced by reseller:
- The new Export to Data Feed functionality opens up some interesting possibilities for using reports as data sources that you can import into Excel 2010 PowerPivot worksheets.
- Even if you don't have Excel 2010 and PowerPivot you can still use Export to Data Feed by working directly with the atomsvc and atom XML files.
- You can deploy PowerPivot worksheets to SharePoint 2010 and schedule an automatic refresh of the data feed.
- Check out the Microsoft SQL Server 2008 R2 Feature Pack for all sorts of downloads like Report Builder 3.0 and PowerPivot for Excel 2010.
- You can download the AdventureWorks sample database used in the report from this CodePlex site.
- You can download the sample report, Excel 2010 workbook, atomsvc and atom files here.
Last Updated: 2010-10-19
About the author
View all my tips