Configure Excel Services in SharePoint 2013

http://prabathf.blogspot.com/2012/12/configure-excel-services-in-sharepoint.html

Configure Excel Services in SharePoint 2013

Finally I got some time to configure my SharePoint 2013 farm. So I thought of using the chance to update my blog too. "Two birds with one stone".

Central Administration > System Settings > Manage services on server

Start "Excel Calculations Services"

Once it's started the next step is to create a new instance of "Excel Service Application" (in one doesnt exist already).

Central Administration > Application Management > Manage service applications and create  a new "Excel Service Application"

You are done with configuring Excel Servcies. As the next step you need to configure the tusted locations where Excel Service will only load Excel Workbooks from.

Central Administration > Application Management > Manage service applications > Excel Servce Application (This will change based on how you named the servcie instance on the previous step) > Trusted File Locations

Fill the Address with a Document Library location. I have created a document library called "Excel Documents" in a site called "Team".

In the External Data section you have "Allow External Data", select the Trusted data connection libraries and embedded option.

Set both the Automatic refresh and Manual refresh values to 0 (But this is not recommend as it will consume more resources by refreshing workbook results immediately).

Now as we have a trusted file location, we can now publish data-connected workbooks with embedded data connections. But for Excel Services to correctly render data-connected workbooks with external data connections, we must configure a trusted data connection library.

Central Administration > Application Management > Manage service applications > Excel Servcie Application > Trusted Data Connection Libraris

Make sure you have a "Data Connection Library" type library. Name of my data connection library is "Data Connections" which is created in a site called "Team".

Now you have successfully completed with Excel service configuration.

----------------------------------------------------------------------------------------------

http://sharepointknowledgebase.blogspot.com/2011/12/this-workbook-cannot-be-opened-because.html#.VLg7CivF-4I

This workbook cannot be opened because it is not stored in an Excel Services Application trusted location

One of our user has opened a ticket with us by mentioning that he is getting an error while editing the date in excel. the error message is mentioned below:

This workbook cannot be opened because it is not stored in an Excel Services Application trusted location.
To create an Excel Services Application trusted location, contact your system administrator
Now its quite obvious that you need to add the document library path inside excel trusted file location.

Please refer following article and your issue will be resolved J
http://sharepointknowledgebase.blogspot.com/2011/12/excel-services-sharepoint-2010-trusted.html

If you have any queries/questions/doubts regarding the above mentioned information then please let me know. I would be more than happy to help you as well as resolves your issues, Thank youJ

- See more at: http://sharepointknowledgebase.blogspot.com/2011/12/this-workbook-cannot-be-opened-because.html#.VLg7CivF-4I

-----------------------------------------------------------------------------------------

http://blogs.technet.com/b/chrad/archive/2010/07/21/tfs-dashboards-in-moss-2010-return-access-denied-for-tfsolapreport-to-end-users.aspx

TechNet Blogs » Chris's Blog - Just weedin around Microsoft » TFS Dashboards in MOSS 2010 Return Access Denied for TfsOlapReport to End-Users

TFS Dashboards in MOSS 2010 Return Access Denied for TfsOlapReport to End-Users

ChrAd 

21 Jul 2010 2:50 PM 

• 2

If you’ve decided to integrate your TFS 2010 infrastructure with Microsoft’s enterprise-ready SharePoint Server 2010 (MOSS) then you are in for quite a store, both good and potentially bad.  MOSS is extremely powerful platform that many customers of ours are investing in and we certainly understand why.  However, the frustration often comes when you have competing heads – the TFS infrastructure “guys” and the SharePoint “gals – trying to get all the pieces working and playing together well.

In today’s post, I will share some insight that had our TFS consultants scratching their head and our MOSS folks (me – and can I say I’m limited) spinning around and saying “Just make it work…”  The beauty of working at Microsoft is the ability to find the right people and get the assistance needed to make it work.  This is the reward we get and that reward I’m happy to share with those of you that run into the same thing.

Goal

The goal here is to get TFS integrated fully into MOSS 2010 and, in particular, using Excel Web Services to display critical data in the TFS Dashboards.

Understanding Excel Web Services & TFS Integration

John Socha, a colleague and friend of mine, helped me quickly understand how the TFS Excel reports work with respect to TFS.  Excel workbooks have “cached” data embedded within the XLSX files themselves that is accessible via the Excel or via Excel Web Services.  By default, Excel Web Services (EWS) utilizes a SharePoint feature called Secure Store Service (or abbreviated often as SSS) to allow individuals who do not have direct access to the data source of the workbook to see the data within EWS.

Access Denied by External Data – TfsOlapReport

The reports start coming in as soon as you roll out TFS 2010 that you are having issues with “reports” showing up on the dashboard for each of your team projects.  TFS will create the dashboard anytime you point it towards the MOSS server which is helpful though it doesn’t mean that everything is good to go.

As you can see, the user will see the above error report every time they re-attempt to connect to the site and view the data.  If they click OK, you (and the user) are shocked when data is displayed to the end-user like below.

Why is it saying I’ve been denied access?

Excel Workbook Caching versus Excel Web Services

The reason data is shown after clicking ok is the user has been granted access to the SharePoint site and hence can access the physical Excel workbook.  As mentioned earlier, Excel stores “cached” data from the last time that the workbook is refreshed and this is shown to the user.

You get Access Denied because the end-user has been denied access to the data source, not the entire workbook.  EWS attempts to access the data source directly each time the site is opened and an existing connection isn’t open.  To validate whether you have an issue with your SSS, you can do the following-

1. Open your MOSS site hosting TFS content
2. Click the Excel Reports in the land-hand navigation
3. Click on the name of the Excel report such as Burndown
4. For Data, click Refresh All Connections

This will verify if your SSS is configured to work as expected, and that within the Members for SSS you’ve granted that user permissions.  If you get an access denied, let’s walk through fixing this problem.

Determining EWS Configuration – One Workbook at a Time

The first step is to verify that your Excel workbook is using the authentication type.  TFS 2010, out-of-the-box, uses SSS and setups a Target Application ID called TFS.  This, of course, is configurable though you need to think about two things -

1. SSS doesn’t require end-users to have direct access to the TFS cube (TFS_ANALYSIS)
2. Windows Authentication requires direct access permissions to the TFS cube

SSS is optimal long-term (as put by someone who helped me figure this out) though it requires a bit more configuration than Windows authentication.

To determine which authentication a workbook is using (if necessary), do the following -

1. Open the workbook in Excel (from SharePoint, not Visual Studio)
2. Highlight the chart in the report by clicking on it
3. Under the Data menu item, Connections
4. Highlight TfsOlapReport, click Properties
5. Click Definition tab
6. For Excel Services, click Authentication Settings…

As you can see, you can select SSS or you can choose Windows Authentication.  In this case, it should be set to the default of SSS – SSS ID:  TFS.  If not, change it to look directly like the above.

Correctly Configuring Secure Store Service (SSS)

If your workbook is configured correctly, and you are still getting access denied, then you will need to use the SharePoint Central Administration Site to correct the problem.  In this section of the post, I will share specifically how I corrected the problem that was occurring throughout our dashboard.

1. Open the MOSS Central Administration Site
2. Click Application Management
3. Under Service Applications, click Manage service applications
4. In this list of service names, click Secure Store Application
5. You should see a Target Application Id of TFS (if not, you need to create one which isn’t the purpose of this article). click on TFS
6. On the Target Application Settings page, click Next
7. On the Field Name page, click Next
8. On the Administrators & Members page, you will need to enter all potential dashboard viewers into the Members box.
9. Click OK

After you’ve clicked OK and saved, you can re-test and the users should not get the Access Denied if they are listed in a domain group that they are a member of or you’ve granted them direct access via their user account.

Summary

Doing software development is hard enough, let’s not make it any harder than it has to be.  Enter the handy dandy capabilities of SharePoint to make flashy, useful dashboards to give insight in how your team is executing and most importantly struggling.  Then it doesn’t work.  This is a challenge set of applications coming together as a “mash up” that one simple step missed can cause it to come tumbling down.  There is documentation out there that can help “lead” you to the problem but unless you know a)  TFS  b)  Excel Web Services  c)  SharePoint then your life is probably going to be a bit complicated.  In today’s post, I shared some insight into one problem that many of you might very well have and hopefully will make your life much better.

Enjoy!

-Chris

text area trick

http://www.impressivewebs.com/textarea-auto-resize/

Textarea Auto Resize

By Louis Lazaris on January 9th, 2012 | 101 Comments

On a current project, I was trying to find a way to auto-resize a textarea according to some content that would be loaded in dynamically via Ajax. I didn’t know the height of the content and the textarea element doesn’t resize naturally like other HTML elements, so I needed to update the height of the element with JavaScript each time the content changed.
It seemed like a simple task. After doing a search to see what types of plugins and scripts were floating around to do this, the examples I found seemed a little overly complex. While most solutions seemed to incorporate some complex math calculations, I thought of a better way.

Using a Hidden Clone Element

A <div> element will naturally stretch to fit the height of its content (assuming no floats or absolutely positioned elements are involved). So to get the height of the textarea, I just need to do the following:

• Grab the content loaded into the textarea
• Create an invisible clone div
• Give the clone the same width and typographical properties as the textarea
• Place the content into the clone
• Get the height of the clone
• Apply the height of the clone to the height of the textarea

The Code

One of the keys to this solution is the CSS. As mentioned, the invisible clone needs to have the same typographical properties as the textarea. Not only does this include stuff like font-size and font-family, but also the white-space and word-wrap properties of the clone need to be set to mimic what happens inside the textarea.
First here’s the CSS for the textarea:

textarea {
    width: 500px;
    min-height: 50px;
    font-family: Arial, sans-serif;
    font-size: 13px;
    color: #444;
    padding: 5px;
}

.noscroll {
    overflow: hidden;
}

Take note that I’ve added a separate class with overflow: hidden, to prevent scrollbars from appearing. Normally this would not be a good thing to add to a textarea element, but because I’ll be resizing it with JavaScript, it’s fine. This class will be added to the textarea with JavaScript, to ensure that if JavaScript is turned off, the textarea will scroll normally.
Here is the CSS I’ll be applying to the hidden clone element:

.hiddendiv {
    display: none;
    white-space: pre-wrap;
    width: 500px;
    min-height: 50px;
    font-family: Arial, sans-serif;
    font-size: 13px;
    padding: 5px;
    word-wrap: break-word;
}

A quick break-down: First, I set it to display: none because I don’t want it visible to the user. I believe this should be fine for screen readers, because I don’t want it read out to them either. If anyone has a better solution for hiding it for assistive devices, let me know.
I’ve also set the white-space property to a value of “pre-wrap”. This ensures that lines will wrap correctly, but everything else gets pre-formatted. I’ve also set the width to be equal to the textarea, and duplicated a few typographical properties. In both examples, I’m giving the clone and the textarea a min-height so it will always start out at a standard, usable height.
Now for the JavaScript (which is using jQuery, sorry):

$(function() {
    var txt = $('#comments'),
        hiddenDiv = $(document.createElement('div')),
        content = null;

    txt.addClass('txtstuff');
    hiddenDiv.addClass('hiddendiv common');

    $('body').append(hiddenDiv);

    txt.on('keyup', function () {

        content = $(this).val();

        content = content.replace(/\n/g, '<br>');
        hiddenDiv.html(content + '<br class="lbr">');

        $(this).css('height', hiddenDiv.height());

    });​
});

This code assumes we’re targeting a single textarea element on the page. If you need this to affect more than one element, then just change the first line inside the function that defines the element we’re working with.
I’m dynamically changing the height based on jQuery’s keyup event. You could easily change this to respond to an Ajax request instead, if you happen to be loading the content that way.
Using keyup, however, is a good solution because it’s the most likely reason that you’ll want to auto-resize a textarea — user-entered data.

What About IE6-8?

I almost didn’t write this article, because the code wasn’t working at all in IE6-8. The reason for this had to do with the poor way IE handles grabbing content using innerHTML. So after I had written this simple solution that seemed to work in all the newer browsers, I came across this jQuery plugin. That solution uses the exact same method that I’m using (the cloned element), and it worked (mostly) in IE.
So the line that I borrowed from that example that (mostly) fixed mine in IE was:

// fixes the IE innerHTML problem
content = content.replace(/\n/g, '<br>');

But even after adding this line, there was still an issue: Long, unbroken strings of text wouldn’t affect the height of the textarea (which isn’t a big problem, really). A simple fix was adding word-wrap: break-word to the CSS for the clone element.

Bugs? Problems?

Like the other solutions floating around, this could easily be turned into a plugin. In that case, I’d have to add a little more jQuery so that the characteristics of the clone element aren’t dependent on the CSS. Also, if the width of the textarea is fluid, then you’d have to use jQuery to grab that, and then apply it to the clone.
The solution I linked to in the previous section is just about perfect — you just need to add the word-wrap fix that I mentioned.
For a demo, you can view my JSFiddle using the link below. Just be sure to hit the “run” button before you test it. Let me know if you find any problems with it.

• View Demo

Update (Aug. 14/2012) The demo link above now points to an updated version with some of the fixes suggested in the comments along with some CSS improvements.