Sunday, March 23, 2014

Getting Power View into Dynamics CRM

image

There it is, no Photoshop, this is real. Thank you to Microsoft’s CRM maven and top bloke Mark Rettig for inspiring me to revisit this and for giving me the ‘cross-scripting’ trick. This is how I do it.

Create Your Power View Spreadsheet

If you have Excel 2013, Power View comes as part of the package. Go to Insert-Power View to generate a pretty and powerful representation of your data. In my case, I browsed online and found some data showing industry sales by country.

image

After a bit of playing with the Power View I got the Map view you see in my dashboard.

Load the Spreadsheet into your SharePoint Site

Initially I tried using OneDrive but I could not get it to show the Power View sheet so SharePoint it is. As my long-term readers will know, I have an Office 365 subscription. Being cheap, this is a P1 plan which costs me US$6 per month and takes care of my email. It also comes with SharePoint which, until today, I had not done a lot with. Going to the SharePoint Team Site document store, I loaded up my spreadsheet.

image

As you can see, we get control of the permissions if we need to restrict access to the spreadsheet. Ticking it and selecting ‘View in Browser’ shows the Power View in my browser and gives me a URL to use.

Create a New Dynamics CRM Solution

This is where Mark’s trick comes in. If you try and add the URL directly to a dashboard in CRM it does not work; nothing shows up. This is because, cross-scripting is forbidden for dashboard iframes created against the base solution. To get around this, we create a solution and create the dashboard there. Now we can add an iframe to the dashboard and turn off cross-scripting

image

We are then good to go. We can add whatever else we like to our dashboard and it all just works. Have fun.

2 comments:

Simon Jackson said...

That's a great, but wouldn't it be even better if the data dynamically read?

Can you set this to read data from another spreadsheet. Have you tried this?



Cheers
Si

Leon Tribe said...

Hi Simon,

Dynamically read would be awesome and there has been some discussion on this on my twitter handle (@leontribe).

At this stage it mainly depends on your deployment mode and, thus, the technologies employed in linking Excel and Dynamics CRM.

I was using CRM Online for the blog article and, unfortunately, CRM Online requires the Outlook client for CRM (which I had not set up) to render Dynamics Excel sheets so it would not work.

It is certainly worth a try though.