Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 8 Next »

We no longer recommend this method of connecting Excel files to Projector reports. A bug in Excel can cause a 115 character limit on web service URLs. This bug is limited to a small subset of Excel installations. We now recommend you use CSV instead. CSV import is more lightweight, faster, and also allows you to more easily amend reports to add or exclude columns.

eXtensible Markup Language (XML) is a common data form used by developers. It is also supported by Microsoft Excel and Google Docs for importing data. To learn more about how to pull your XML data directly into these products, please see our Report Web Services.

Microsoft Excel

Import your data directly into Microsoft Excel. By binding your Excel report to a report web url, you can rerun the report, updating all your numbers, but not lose all the custom formatting that you may have applied in Excel. This is a great alternative to Access Databases because it removes the overhead of saving out of Projector and having access to a network drive. The following instructions are for Excel 2010.

1. Click the Data tab for a worksheet, then choose From Web

2. In the dialog that appears enter your report url. Remember to set the format to XML. If you don't provide authentication information in the URL, either via user password, auth token, or session ticket - then you'll be prompted to enter your username/password. 

3. Click Import

4. If you receive the following error dialog it can be safely ignored

5. Select which cell you want to be the upper left of the new table

6. To refresh the data, click the Design tab and then choose Refresh


Excel asks me to log in

If you choose not to provide auth information, you'll need to enter it again at some point in the future. You can do that from the Connection | Properties | Definition | Edit Query section of Excel.

Google Docs

You can import your data into a Google spreadsheet by using Google's importXml feature. The importXml call accepts the report url so that it can find the report and an XPath query to parse it. It is really very easy to do.

  1. Make sure that you have a working report URL. See Report URLs above if you need help with this. 
    1. The format parameter must be set to xml. For example, http://app.projectorpsa.com/myReport?format=xml
    2. The dateformat parameter must be set to googledocs to parse dates. For example, http://app.projectorpsa.com/myReport?dateformat=googledocs
    3. The columnheaders parameter must be set to firstrow if you want each column to be named. For example, http://app.projectorpsa.com/myReport?columnheaders=firstrow
  2. Test your URL by cutting and pasting it into your web browser
  3. Select the first cell in the Google Docs spreadsheet.
  4. In the value field, enter the following, replacing url with the your tested url
Google Spreadsheet
=importXml("url", "Report/Rows/Row")


 

  • No labels