SQL Server 2008 R2 Reporting Services ATOM Data Feeds

By:   |   Comments (18)   |   Related: > Reporting Services Data Sources


Problem

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.

Solution

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:

a new feature in SQL Server 2008 R2 Reporting Services called ATOM data feed

Notice the toolbar icon enclosed by the blue rectangle as shown below:

This icon has the tooltip text "Export to Data Feed"

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:.

File Download dialog

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 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

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:

A portion of the atom file

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):

The Excel 2010 Table Import Wizard is included with the PowerPivot add-in

Click Next to proceed to the Select Tables and Views dialog as shown below:

Select Tables and Views dialog

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:

click Preview and Filter to view the data

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:

a dialog will be displayed showing the import results

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):

the PowerPivot worksheet

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:

you can create all sorts of nifty pivot charts and tables

Next Steps
  • 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, January 26, 2016 - 3:47:25 PM - Ray Barley Back To Top (40507)

Trevor

The only suggestion I have is that you take a look at this link and see if it helps: https://technet.microsoft.com/en-us/library/ee210699(v=sql.105).aspx

 

 

 

Tuesday, January 26, 2016 - 1:23:01 PM - Trevor Back To Top (40505)

 I am trying to set up a data refresh for a PowerPivot file on Sharepoint 2010. I've set up the connection using an .atomsvc file for an SSRS Report Builder report. I set up the data refresh schedule to run each morning, but continue to get the same error. Is this an authentication issue?

Error Report:

 

The data refresh for 'PowerPivot Data Feed Test3.xlsx' on Supply Management has failed with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Could not find a part of the path 'C:\Users\USX28214\Desktop\Trevor Transit Time Reporting Updated2 1-20-16.atomsvc'.;Could not find a part of the path 'C:\Users\USX28214\Desktop\Trevor Transit Time Reporting Updated2 1-20-16.atomsvc'. Could not find a part of the path 'C:\Users\USX28214\Desktop\Trevor Transit Time Reporting Updated2 1-20-16.atomsvc'.. A connection could not be made to the data source with the DataSourceID of 'f2490f49-302b-4819-85ad-0fba7fa46064', Name of 'DataFeed Trevor Transit Time Reporting Updated2 1-20-16'. An error occurred while processing table 'Tablix2'. The current operation was cancelled because another operation in the transaction failed

Tuesday, July 21, 2015 - 10:22:03 AM - Ray Barley Back To Top (38264)

In this example I'm using ATOM to export the data from an existing SSRS report.  The Start Date and End Date are parameters in the report.  After you run the report you click the icon on the toolbar to do the export and it simply exports whatever data is in the report.

This is the extent of my knowledge on ATOM.

 

Monday, July 20, 2015 - 7:50:00 PM - Luke Back To Top (38263)

Hi Ray

 

In your example above, you will notice that there's a 'Start Date' and 'End Date' in the .ATOMSVC feed. Do you know of anyway to make that changable? I guess this could be domne in VBA, but I am unsure of how to make the connection in VBA to start with. What I have done, is use the .ATOMSVC document as the basis of a connection ('From other data sources' - 'ODATA Data Feed'). In the excel, break up the code in the ATOMSVC file across several cells to isolate the definable fields e.g dates etc..Then I make the date cells equal what ever I want and then "&" all the cells back together to give me the new ATOMSVC contents (which now has updated information). Then I have a marco which writes the new information to the ATOMSVC file I orignally created the feed with, and then refreshes the datafeed. I save the .ATOMSVC to a newly created folder on my C/: when I setup the connection orignally so that the VBA code can re-created it with the same name and location with the new contecnts when I give the file to someone else to run (it creates the file first, then refreshes the connection). 

The above works, but isn't awesome. Do you know of a better way?

 

Thanks.

Tuesday, July 7, 2015 - 9:23:51 AM - Ray Barley Back To Top (38144)

I googled SSRS atom data feed and found this link for SSRS 2016: https://msdn.microsoft.com/en-us/library/ff519563.aspx

Looks like the capability still exists but I have not used it since 2008 R2

 

Tuesday, July 7, 2015 - 7:05:06 AM - Luke Back To Top (38142)

Hi Ray

Is this still active?

Thursday, December 20, 2012 - 10:17:24 AM - Raymond Barley Back To Top (21055)

Take a look at this tip: http://www.mssqltips.com/sqlservertip/2820/whats-new-in-sql-server-2012-reporting-services/

 

Thursday, December 20, 2012 - 7:00:42 AM - Eran Dagan Back To Top (21051)

Hi,

Thnx for the reply. where do I check that? user feedback was that he clicked the oranfge icon for ATOM data fed export, but never used it.

Wednesday, December 19, 2012 - 6:22:07 AM - Raymond Barley Back To Top (21025)

Check if the user setup a data alert.  That would cause the report to keep executing.


Tuesday, December 18, 2012 - 8:06:09 PM - Eran Back To Top (21020)

Hi,

How can I stop ATOM feed from being executed? I had a user who exported a report to a ATOM data feed and then deleted it. Porblem is that I can still see a record in the executionlog table for the ATOM type execution. 

The ATOM is executed every minute  ,which is really stuffing myexecutionlog table and distore the execution anlysis

 

Wednesday, April 25, 2012 - 7:32:25 PM - Ray Barley Back To Top (17114)

Yes you do need PowerPivot for SharePoint; here are the instructions for installing: http://msdn.microsoft.com/en-us/library/ee210616.aspx

After you create a PowerPivot gallery (a special kind of document library) you can upload your PowerPivot spreadsheets and configure the refresh.

Depending on how you access your data sources you may need to also setup kerberos authentication; here are those instructions: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23176

Just a bit of friendly advice - the above steps are not trivial.  You have to follow them exactly else it doesn't work.

Wednesday, April 25, 2012 - 5:08:35 PM - Nancy Back To Top (17112)

so you mean we need to install the powerpivot for sharepoint also.??

i mean i am getting confused between poerpivot for excel and sharepoint? can you tell the procedure for that as well? how to put the excel file on sharepoint and configuration part? 

Wednesday, April 25, 2012 - 3:46:07 PM - Ray Barley Back To Top (17111)

When you use the PowerPivot add-in for Excel 2010 and put your Excel file in a SharePoint PowerPivot library, you can configure a refresh schedule. 

Wednesday, April 25, 2012 - 3:09:57 PM - Nancy Back To Top (17110)

after capturing the data into excel, and making pivot charts  and tables.

when you render the report again, how to directly update the  pivot tables which we made earlier. is there any way , we can do this..?

 

 

Sunday, February 20, 2011 - 11:27:59 AM - Ray Barley Back To Top (12978)

Yes.

When you are done entering the parameters and click the View Report button, the report is rendered.  At this point you can click on the "Export to Data Feed" icon to launch the ATOM feed and capture the data into Excel.

Sunday, February 20, 2011 - 10:10:58 AM - hxy0135 Back To Top (12977)

Hi,

I have a set of parameterized reports from which I want to export their data into Excel 2010. Each of my reports has five or six parameters. In order to run the report, a user has to provided the selected values for each report parameter. Does ATOM support parameterized reports?

 

thanks

 

Thursday, October 28, 2010 - 12:44:56 PM - Ray Barley Back To Top (10313)

ATOM support in SSRS 2008 R2 just gives the ability to extract the underlying data in a report.  The Excel 2010 PowerPivot just happens to be the only ATOM consumer at the moment.

Thursday, October 28, 2010 - 11:08:03 AM - jez Back To Top (10310)

Although I like the idea, if I wanted data in Excel, in most cases we'd just export straight to Excel?!  It's hard to see a killer use case for this.














get free sql tips
agree to terms