The Baretta

Today we will be learning how to create a excel report connected to a SharePoint library using Power Query.  I will also address all issues found along the way. The reason we are connecting to our library via Power Query instead of via the 'Export to Excel' option is because our library contains InfoPath forms that have repeating table data in the columns that we need to report on. Power Query   Is a free excel add-in available from Microsoft for both Excel 2010 Professional Plus & Excel 2013.  It is used when you need a way to transform data that isn't quite in the format you need. Step 1: Connecting to your SharePoint Library via Power Query. Open up Excel.  Click Power Query Tab > From Other Sources > From SharePoint List > Insert SharePoint Site URL Here's our first issue.  Only Lists are appearing in the Navigator.  Not to worry, Right-Click any of the Lists showing and click Edit to open up the Query Editor.   You should ...

Just a quick one after some deep thought. Thoughts on the Future: If you have to create an InfoPath Form, you should be creating it as a List Form (if possible), not a Form Library.  The reason for this is ease of upgrade now that InfoPath will not be upgraded/supported by Microsoft. If you have to create a Form Library (because you form requires repeating tables/sections): I’ve decided that InfoPath forms should NOT be published as Content Types.  The only time it would be considered OK is if the form is very generic and is being used on multiple sites.  E.g. a Help Form which could be used by multiple departments without any modification. The main issue when publishing as a Content Type is that every field you promote to be visible in SharePoint has to be created as a Site Column. This means that even for a small form, for instance, a Personnel Access Form, you’d have to create Site Columns for fields like: Room Number or Access Hours, which would only ever be ...

Wow that's a long heading...  Let me explain how this is done, and why this path was chosen out of all the options. THE OPTIONS Still to come... SETTING UP There's quite a few large steps to get this done, so I'll split it into the following sections, ensuring we test along the way to make sure we haven't missed a step: Install & Configure Oracle Client on Web Server Create custom Web Service in Visual Studio Deploy Web Service to IIS Build InfoPath Form Configure InfoPath Services Deploy InfoPath Form to SharePoint Environment Install & Configure Oracle Client on Web Server Ask your Oracle DBA what version of Oracle the database is running on. Navigate here and download the corresponding Client (for this excercise get the 32-bit version if its an option):  http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html Copy to the IIS Server (Web Front End) Extract and install, here's the settings I used for the i...