Alexander Vanwynsberghe

"There is nothing impossible to him who will try"

Using Project Online OData with Excel Web App

Project online has some great reporting features. One of the features that I really like is the OData service. This OData service is not only available for Project online, but also for on-premise deployments. To access this feed, all you have to do is browse to the following url: “/pwa/_api/ProjectData”.

One of the tools to “explore” this Odata feed is Excel. You can open one of the existing Project Online reports with Excel. Go to “Reports – English – Project Overview Dashboard” and click on the 3 dots, select “Edit“.

01

The report will open in Excel, and you can now select the ‘DATA‘ tab, and press ‘Refresh All‘. This should load all your data and show you a list of all projects with some additional information. This is pretty cool, but can we also use this within the Excel Web App? Well, let’s try it straight. Go back to the “Reports” folder, and instead of clicking on the 3 dots, just click on the name of the file. Excel Web App should open, and you will see an empty list of projects. Now click on the Data tab and select “Refresh All Connections“.

02

So, that’s not what we were expecting. It looks like Excel Web App cannot access the Odata feed. The explanation is quite simple:

When Excel workbooks are refreshed in Office 365, the BI Azure Service retrieves updated data from Project Online and recalculates the internal workbook model. If the workbook has data connections pointing to Project Online OData feeds, the BI Azure Service must have permission to the SharePoint Online tenant to retrieve that data.

What we have to do is giving the BI Azure service the required permissions to access our Project Online tenant. Browse to your PWA instance, and add “/_layouts/15/appinv.aspx” to the URL. For example “http://yourtenant.sharepoint.com/sites/pwa/_layouts/15/appinv.aspx“. You will now see the application permission screen. In the App ID field, add 00000009-0000-0000-c000-000000000000 and click Lookup. The title and App domain should tell you we’re working with the Azure AnalyisisServices. Copy and paste the following in the Permission Request XML Field

03

Click create and Trust the App.

04

To be sure that the App has been granted with the correct permissions, go to “Admin (on top) – SharePoint – Apps – Permissions“. There you should see the App “Microsoft.Azure.AnalysisServices

05

That’s it. Now we can open the report using Excel Web App, and click on “Data – Refresh All Connections“. Have fun!

06

Note: Be sure that you first refresh and save your report using the Excel (desktop) application. This will update the internal model of the workbook so it is supported by Excel Web App.

Windows Azure auto-provisioned apps for SharePoint 2013

I suppose you all heard about the “App” concept in SharePoint 2013? If not, you have a nice overview about this new concept on msdn. An app for SharePoint is a great way to surface a remote web application in SharePoint. In this post, I want to tell you a little bit more about Windows Azure auto-provisioned apps for SharePoint 2013.

Imagine this: Wouldn’t it be cool if you can deploy whatever custom web application with whatever server-code behind it, directly to your Office 365 instance? Well, using the Windows Azure auto-provisioned apps you can. First question you can ask yourself, what’s Windows Azure doing in this story and how does it link to your Office 365? Well, to get this working internally each Office 365 gets automatically an Azure account associated. That’s nice, and really powerful. Because you now have both Azure and Office 365 together, you don’t have any reasons or whatever to stick with your on-premise installation.

An auto-provisioned app contains at least one Windows Azure Web Site that can be launched from a SharePoint 2013 Preview host web, and may also include SharePoint components on an app web and a SQL Server database (based on SSDT and .dacpac packages).

Let’s take a look what the development and deployment process looks like:

  1. The developer builds a SharePoint application into in .APP file
  2. Deploy to SharePoint
  3. SharePoint installs the application for you
  4. SharePoint provisions all Azure components
  5. SharePoint provisions the SQL Azure database and initializes everything included in the .dacpac (Data-tier Application Component Package)

So in general, you create an application, publish it to SharePoint, and SharePoint will do everything for you. So creating an Azure Website, manage your database on SQL Azure and initialize everything.

Let’s try it out. First you have to create a new SharePoint project in Visual Studio 2012. Open Visual Studio and click ‘File –  New Project‘. In the ‘new project‘ window, select ‘Visual C# – Office/SharePoint – Apps‘ and select the ‘App for SharePoint 2013‘ template. Give your project a name and click ‘Ok

Note: If you have never created a SharePoint/Office 2013 app before, you will not find the Project Template in Visual Studio 2012. You need to install the Microsoft Office Developer Tools for Visual Studio 2012. You can download this here. It will start WebMatrix and download all the required components you need.

On the next screen, enter the name of the application, and the URL of your development site. I used my SharePoint Online subscription for this. Be sure to use a ‘Development Site‘, otherwise you will get an error when you try to test your application. (been there, done that!). If you don’t have a development site, create a new one and use the ‘Development’ template when creating. That will do the trick. Ok, in the current window, also be sure to select  and choose “Autohosted”.

When you click ‘Finish’, your project will be created. Basically, it will create a solution with 2 projects. The first project contains an AppManifest file, and the second project is a simple ASP.NET application.

The AppManiFest file is the definition of your SharePoint application. It contains a title, a name, which (tile) icon it uses, what the start page is, which permission requests it need to run (for example accessing  User Profile information),… The ASP.NET application itself is really basic, but one thing to notice is the file ‘TokenHelper.cs‘. This is a file who is responsible for the OAuth 2.0 authentication of your application with SharePoint. This helper class is used in Default.aspx. It includes sample code about how to handle OAuth 2.0 authentication and how to perform callbacks to SharePoint using CSOM.

In this example, we won’t change anything and just continue and get our application up and running. All you have to do is click on ‘Debug‘, or just press ‘F5‘.

In the output console of Visual Studio, you will see that the SharePoint application will be uploaded to SharePoint, and that SharePoint will do the necessary things to install it. Note that your App is NOT provisioned on Azure yet. If the installation was successful, your browser will be opened and will ask you to do a login and it will now ask you: “Do you trust your application”? Click ‘Trust it

You will now have your application listed as an item in the ‘Site Contents‘.  Click on your application and you will get a certificate error. If you look good to the URL of your application you will see ‘http://localhost:xxxx’. What SharePoint did was an installation, and during the installation, the application URL was set to your localhost environment. Indeed, if you look on that taskbar of your machine, Visual Studio started an IISExpress with the same port as the one SharePoint redirects you to. Just ignore the certification error and continue.

What you see now is not quite much. What this example basically does is using the ‘SPHostUrl‘ parameter that is added to the URL when SharePoint opens your application. Using this parameter, the application will get an access token (using OAuth 2.0) and use this token to get a client context object. This object can load the title of the SharePoint site you’re using. Because your app is hosted locally using IISExpress, you can easily debug it.

Ok, let’s assume that our application is ready to be deployed. We can use Visual Studio to deploy our application. Right-click on your project containing the AppManifest file and select ‘Deploy‘. It will now execute the same process as before (as you can see in the Visual Studio Output window). When your application is installed, trust it, and click on the Application icon to open it.

As you can see now, the URL is not on localhost anymore, but it got a unique o365apps.net URL. So our application has been deployed to Windows Azure, awesome!

Of course, we’re still in development. But let’s say that everything is good, tests are written and the application is ready to go live, how can you do this? Well, we first need to create an ‘APP’ package of our application. In Visual Studio, right-click on your project containing the AppManifest file and select ‘Publish‘. You will get this dialog:

Click finish, and the output folder of your .APP file should open. In fact, an APP file is nothing more that a zip file (you can open it with 7Zip for example), containing all your artifacts. A bit like the WSP files.

Ok, so we have our APP file. Now we should ‘upload‘ it to our SharePoint farm, and make it possible for users to select this application on their site. The place where App’s can be managed is called the “App Catalog“. This app catalog is a separate Site Collection, and can be created using the SharePoint Administration Center. There you can click on ‘Apps – App Catalog‘. If you haven’t created one yet, it will create one for your, and otherwise, you will be redirected to the App Catalog itself.

Adding an App to the catalog is really simple. On your App Catalog site, click on ‘Apps for SharePoint‘ (on the left side). You will see an overview of all installed apps, including an indication of the licenses you still have available. To add your App, click ‘New Item

It will open a new dialog where you can select your .App file and give it a version comment. Click ‘OK‘ to continue. The next dialog gives you an overview of everything related to your application. Complete this as you want, and click ‘Save

Now you have your new Application available in the App Catalog. To use it, just go to a site where you want to use the application. On this site, go to ‘Site Contents‘ on the left side, and click on ‘Add an App‘. Look what you see there, the application we’ve just uploaded:

Click the App, trust it, and your application will be provisioned by SharePoint. When this process is ready, your application is installed on your site, and you’re ready to use it. Click on the App, and you should see:

The text displayed is the name of my site, which is ‘Ideation’, and you also see a unique ID in the URL = a unique Windows Azure website. (Note the ‘Website’ part of the name).

So that’s it. We have our SharePoint app up and running on Windows Azure. Great! One thing that’s really important to know: each ‘installation’ of our App will provision a new Windows Azure Website. So this will be a part of your billing/account, don’t forget that.

To finish this post, I’ll give you 2 reasons to use this approach:

  1. When you deploy an autohosted app for SharePoint, all Windows Azure and SQL Azure components are provisioned for you when the app for SharePoint is installed.
  2. The Windows Azure Web Sites infrastructure handles load balancing, multi-tenancy, and other important maintenance tasks for you.

Thanks for reading!

Getting started with Team Foundation Service

Logo

Introduction

At the Build conference this week, a new service for the upcoming version of TFS was announced. They call it Team Foundation Service. This is a Windows Azure hosted solution of a Team Foundation Server, which is pretty cool! The TFS team definitely did a great job to bring the Application Lifecycle Management features of Visual Studio and Team Foundation Server to a higher level. And from what I can say, it looks promising. In this post, I’ll describe some setup steps and a general overview of some new features. 

 

Setup

First of all you need an account for the Team Foundation Service on tfspreview.com. For now, you need an invitation code to create an account. (If you don’t have an invitation code, I have some left. Just post a comment..) Brian Harry already made a nice blogpost about the registration process and how to add and invite users to your Team Foundation Service environment. When you finish your registration (using your Windows Live Id), you’re in the adminstration mode of the Team Foundation Service.

One tip I can give is that the gray border on top indicates that you’re in administration mode. This is one thing I didn’t notice in the beginning. I was a little bit confused when I tried to open my team project when I was still in administration mode. But after a while I got whole point.

The first thing you can do is create your first team project. You can do this by clicking on ‘create team project’. There you get a dialog where you can enter a name and a project description. You can also select which process template you want to use. The Microsoft Visual Studio Scrum 2.0 Preview 1 template is selected by default. (More about this specific template will follow in a later post) Currently it is not yet possible to add extra process templates. But I think most of us will use the Scrum template or the Agile Software Development 6.0 template.

Creating the team project only takes about one minute or less. Actually it will queue a job in the background, and the service does some jobstatus polling to show the process status to the user.

 

Overview

After the team project is created, you can click on the ‘My Team home page’ and you are redirected. That’s the place where all the fun starts. On top of the page you see:

  • Home: where you have some shortcuts to your activites, and a link to some administration
  • Backlog: where you can add your userstories
  • Board: where you have a graphical overview of the running/sheduled/.. work items. There you can also change the status of the workitems.
  • Source: where you have the possibility to search in changesets, shelvesets. You can lookup code, compare code with previous versions.
  • Builds: where you get an overview of the build history. You can also manage the build qualities.

 

Conclusion

I spent some time on the Team Foundation Service now, and from what I experience it’s really good. It’s surprisingly easy to use and quite fast. I really like the ajaxified interface. The only things which I think will need some change are some usability issues. Apart from that, my first impressions are good.

In my next blog posts, I’ll cover some other things like the product backlog, work-items, the board, how to use Team Foundation Service with Visual Studio 2010,..   You can always share your personal experiences by adding a comment to this post.

Thanks for reading!