Use OLAP Data Connections for XL based reports

Posted By Posted by: Dan Stopher on March 27, 2013

Use OLAP Data Connections for XL based reports

by Dan Stopher
in How To, Reporting
27 Mar 2013  |  0 Comments

How To: Use OLAP Data Connections for XL based reports

EPMonDemand allows users to use access OLAP Data Connections to support default and custom Excel services reporting. Access is via Excel and is direct over the web with no need for remote desktop services or similar.

This article describes the steps to write OLAP based reports for a Project Server 2010 EPMonDemand instance, however the steps remain similar for 2013 instances.

Prerequisites

  • MS Excel 2007/2010 (Excel 2013 is only supported for EPMonDemand 2013 customers)
  • Microsoft® SQL Server® 2008 R2 SP2 Feature Pack component – SQLSERVER2008_ASOLEDB10_x86.msi

Steps

Note: Before commencing the below steps please review the frequently experienced issues and there resolutions as documented within this document

  1. Open Internet Explorer and browse to the PWA business intelligence centre
  2. Browse to the desired data connection. eg Business Intelligence Centre -> Data Connections -> English -> “Cube Name

  3. Click the Name (e.g. OlapPortfolioAnalyzer ) of the desired Office Data Connection.
  4. Select “Ok” to the Open Document Dialogue box.
  5. When prompted enter your username and password, check the sign me in automatically checkbox and click Sign In

  6. Enable the data connection if prompted
  7. Enter your username and password into the Multidimensional Connection and after ensuring Analysis Server is ticked, click Next

  8. Click the database that the report will be based on and click Finish
     
  9. Check the required fields from the Pivot Table field list/design area
  10. To enable other users to access the report once the design is complete Save the report back to the Business Intelligence centre. Browse to the desired location and save the report

Common Issues

Issue 1: “XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value”.

This message occurs when the local computers regional format setting is set to a value other than English United States (gotta love these guys J)

Resolution – once the setting has been changed to English United States and the Data Connection opened, this setting can be returned to its original value without impacting future Data Connections.

See the following blog for more details:
http://www.dstewart.com.au/2012/08/xml-for-analysis-parser-localeidentifier-property/

To change the value click Region and Language from the Control panel, then set the format to English (United States)

Issue 2: “Errors in the OLE DB provider. An error occurred while loading the connection dialog box for prompting.”

Resolution: This issue occurs when the Microsoft® SQL Server® 2008 R2 SP2 Feature Pack component – SQLSERVER2008_ASOLEDB10_amd64 has not been installed as indicated in the pre-requisites. Install the feature pack component and then retry the report.

Conclusion

The report will then be accessible to all users who have access rights to the location where the report has been stored. The report will open by default in the Excel Services via the web enabling a user to open the report, apply filters and refresh the data or download a snapshot.

However changes to the report design can only be made in Excel by users the appropriate SQL privileges.

Happy Reporting!!


Blog Posted In Blog Posted In: Blog, How to, Reporting
Blog Posted In 

Leave a Reply

Your email address will not be published.