Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2008 R2 Reporting Services ATOM Data Feeds


By:   |   Last Updated: 2010-10-19   |   Comments (19)   |   Related Tips: > 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.


Last Updated: 2010-10-19


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, June 05, 2017 - 1:50:43 AM - Alvincyday Back To Top
michael kors clothing collection veste palace x adidas reebok nano 6 mizuno jobs ralph lauren paris st germain telephone asics gel fuji trabuco 5 air max world tennis mizuno daichi femme nike free rn turquoise new balance en palma de mallorca lacoste grenoble horaires chanel grand sac classique chaussure homme vans authentic vend basket running new balance sac longchamp roseau port? travers maillot hollister femme bracelet lancel dali abercrombie paris pages jaunes ralph lauren outfits pinterest lacoste golf homme mizuno thermagrip vans vente flash ralph lauren big pony polo goedkoop longchamp promo philippines magasin abercrombie dijon nike free run femme 3.0 sacoche coach asics gel 3 femme sac c?line luggage occasion adidas les moins cher converse cuir avis nike blazer vintage rouge hollister soldes femme l'adjani de lancel prix vente dior sac lille burberry brit parfum femme prix quoi acheter chez hollister adidas stan ensemble lacoste bebe chaussure fitflop homme chanel maquillage recrutement dior homme parfum nike blazer comme des garcons jimmy choo handbags mizuno wave creation 12 homme hollister co magasin avis longchamp planetes small tote long handle col roul? lacoste sac hollister prix ferragamo solde homme veste ralph lauren sans manche air max 1 87 pas cher rip curl aloha bikini dbz lacoste asics gel lyte 3 mt magasin hollister velizy 2 air force one homme avec jeans sac longchamp pliage 31x30x19 ecco chateau d'olonne fake nike air max plus mizuno prophecy homme fitflop zalando pochette lancel femme prix lancel my flirt maison mbt sac miu miu pas cher abercrombie bruxelles ouverture bikini transbronzant avis parka mizuno michael kors selma medium dark dune mbt d?finition sac shopping lancel ralph lauren vintage chino pants surv?tement adidas femme 2015 veste de ski napapijri femme veste sentinel abercrombie air force 1 pas cher acheter new balance minimus musique hollister londres converse haute fourrure nike free orange ray ban de vue 2015 sac converse bandouliere femme dior moins cher jean pascal lacoste star academy air max bw original release chanel platinum egoiste jak rozpoznac oryginal air force blanche femme sacoche adidas pas cher ralph lauren polo europe bottes timberland paris adresse magasin mbt paris nike shox promotion hollister maillot de bain femme 2012 reebok j carter nike free run cuir air jordan 1 mid femme abercrombie nice acheter chaussure puma rouge costard ralph lauren mizuno morelia neo cl mix asics gel blast 6 vans belgique liege hermes marseille tel prix sac hermes birkin 35 neuf timberland pas cher femme france nike air max bw x huarache new balance minimus trail hiking ralph lauren golf and tennis be the difference adidas chaussure lacoste carnaby tondeuse rowenta air force magasin converse laval nike air max one femme pas cher adidas 2016 chaussure new balance en france under armour france pas chere asics gel phoenix 7 w ralph lauren olympic shorts nike air max vintage femme new balance u410 azul rojo chemise ralph lauren avis air max pas cher vietnam nike free run qs la nouvelle collection converse timberland roll top homme pas cher bikini kiwi 2013 puma pas cher chaussure jogging polo ralph lauren surv?tement homme lacoste air force hotte ilot sac lancel cuir marron survetement adidas garcon 4 ans hollister sous vetements hermes foulard eperon d'or chaussure under armour pas cher chaussures style louboutin air force hombre baratas converse pas cher boutique nikelab air force 1 low vachetta tan sac lancel ? prix discount balenciaga le dix perfume review air max classic femme pas cher donald trump air force one corinne marjollet hermes running asics promo basket air jordan pointure 39 mbt amx leclerc adidas stan smith femme noir patrick lacoste nike free mercurial superfly white new balance barefoot trail asics militaire air max skyline czy 90 promo converse all star nike air max pas cher avis sac gucci ou louis vuitton chaussures vans era converse bas prix cover iphone 6 jimmy choo grip mizuno jpx 850 tricouri polo ralph lauren xxl new balance sans cuir abercrombie milano indirizzo crampons asics lethal nouvelle nike free run sellier chez hermes air jordan fille 37 air max femme 2013 prix nike shox 44 converse all star jamaica hermes international tracking number sac chanel vernis prix mizuno wave advance 2 femme new balance en palermo burberry brit femme sephora montre michael kors femme blanche portefeuille lancel compagnon bottega veneta boots homme nike air max 90 70 portefeuille lacoste pas cher expos? sur la marque gucci converse all star bordeaux femme magasin polo ralph lauren pas cher polaire ralph lauren homme air max one promo lancel sac l prix supra skytop sneakers hollister london near oxford street magasin burberry shopping air jordan air jordan pas cher air jordan femme chaussure vans paris adresse new balance en paris air jordan france boutique avis adidas snake superstar air presto el corte ingles chaussure asics kayano air max 90 canvas mizuno wave ultima m photos abercrombie parfum burberry canada nike air force one low burberry short gathered skirt trench coat tetine dior en ligne lacoste rose femme adidas superstar metallics - femme chaussures catalogue lacoste acheter sac balenciaga city hollister co france converse pour bebe pas cher asics gel blast 7 nouveau parfum burberry brit manteau napapijri avis nike blazer high suede vintage diavolo veste prada attori nike wmns nike free 5.0 timberland split rock pas cher homme magasin converse ? bruxelles converse haute toile site officiel abercrombie acheter new balance v45 veste coupe vent adidas converse basse marron hommes boutique en ligne lacoste polo lacoste pas cher homme hollister bayan sweat air jordan 7 noir avis brazilian bikini shop magasin napapijri paris nike free ace all black converse slim original kaskus ralph lauren zipped hoodie nike air jordan retro 1 celtics adidas varial low gris fitflop cha cha ralph lauren homme belgique tete aspirateur rowenta air force 18v nike free run 6.0 release date lunette ray ban prix algerie new balance chaussure de running 1226 femme lancel com collection ralph lauren newbury chino nike air max leopard 36 nike shox r2 nike free mercurial superfly ebay asics gel nimbus avis new balance soldees hollister barcelona gran via moccasin with gucci script logo on heel coop?rative herm?s adidas x ray ban jackie ohh polarized mizuno baseball custom gloves chaussure running homme mizuno decathlon marque vans vente bikini tunisie gafas dsquared2 barcelona hollister parfum billig chaussure michael kors pas cher gt 2000 asics femme t?nis nike air max tailwind+ 5 reebok hi strap sandale basket converse chaussure de securite puma avis mizuno breath thermo test caoutchouc air max vans pas cher 37 personnaliser des air max bw adidas zx junior vernis ongles chanel soldes lunettes ray ban de vue prix mizuno wave exceed tour burberry weekend paris mizuno table tennis shoes nike air force femme blanche air max plus tn iii mizuno pour coureur lourd beret lacoste noir asics noosa tri 6 acheter chaussures mizuno reebok b 5.8e vente de chaussure nike air max asics gel beyond 4 mt manteau napapijri femme moins cher new balance mr 1064 asics gel-kayano trainer evo chaussures lacoste chiffre d'affaire bague kelly hermes argent asics gel lyte 3 25th anniversary veste sport adidas the converse x missoni archive project bikini b?b? fille test club mizuno lxvii ralph lauren polo kimono karat? adidas climacool chaussure femme pas cher vans bottega veneta 01 basket new balance occasion sac a main lancel galerie lafayette katrina in bikini original pics Related Articles

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

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

 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

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

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 07, 2015 - 9:23:51 AM - Ray Barley Back To Top

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 07, 2015 - 7:05:06 AM - Luke Back To Top

Hi Ray

Is this still active?


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

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

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

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

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

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

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

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

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

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

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

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

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.


Learn more about SQL Server tools