none
Using Excel based scorecards as a datasource in SharePoint 2013 online RRS feed

  • Question

  • I'm trying the following scenario in SharePoint 2013 online:

    I have a few excel based scorecards (or simply excel based site content types) based on which users create monthly scorecard sheets and save them in a document library in SharePoint 2013 online. I'm trying to use these monthly scorecards as data sources and generate some reports. How to get started on this or resolve this scenario?

    For example, in a given document library there will be 2 excel based content types - HR Scorecard and Marketing scorecard. And, the scorecards will be created by users on a monthly basis. These monthly scorecards will be created, edited or viewed by users with the aid of Excel Web App in SharePoint 2013 online. Now, the problem statement here is to use these monthly scorecards as data sources and pull data into another excel report 

    Note that I'm in SharePoint 2013 online environment. How to accomplish this scenario.

    Thanks Arut

    Friday, May 10, 2013 9:45 AM

Answers

All replies

  • Hi,

    Thank you for posting in the MSDN Forum.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day!

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 13, 2013 11:55 AM
    Moderator
  • Per my findings so far, this scenario may not be possible in SharePoint online due to the following reasons:

    (a) Excel Web Apps do not support Data models and hence, specifying data connections or sources may not be directly possible. Also, in my case the data source is not a single specified source that can be hard-coded, but rather dynamic ones.

    (b) Since macros don't work on Excel Web App, my only option is to go for Javascript based User Defined functions. But my excel templates are going to used as site content types and hence, not sure how to include the script in aspx file (i guess WOPI.aspx) which opens the template.

    However, I would like to see this solution possible and if not, at least a work around.


    Thanks Arut

    Monday, May 13, 2013 2:13 PM
  • Hi Arut,

     

    Thanks for posting.   From your reply, it appears that you have researched this a bit, and you are spot on with your findings.  I will attempt to clarify and answer your questions:

     

    (a) Excel Web Apps do not support Data models and hence, specifying data connections or sources may not be directly possible. Also, in my case the data source is not a single specified source that can be hard-coded, but rather dynamic ones.

     

    Answer:

     

    Office 365 only provides basic browser interactivity for PowerPivot workbooks up to 10 MB in size. Gallery, Scheduled Data Refresh, Management Dashboard, BISM link files, and other BI features such as Power View are not available in any way or form. To get a site with full PowerPivot and Power View functionality, we need to stay on-premises.   With this full functionality, you would be able to use another Excel workbook as a data source that is on a SharePoint site.   Please see the following for more information on using a workbook as a data source:

     

    Using PowerPivot Workbooks as a data source

    http://technet.microsoft.com/en-us/sqlserver/dn151361.aspx

     

    (b) Since macros don't work on Excel Web App, my only option is to go for Javascript based User Defined functions. But my excel templates are going to be used as site content types and hence, not sure how to include the script in aspx file (i guess WOPI.aspx) which opens the template.

     

    However, I would like to see this solution possible and if not, at least a work around.

     

    Answer:

    You may be able use Javascript based UDF's to get the information you need from SharePoint Online;  however, I am not quite sure what you mean by "template" file.  If you could provide a bit more information on what you mean by "template", maybe that will clarify my confusion.

     

    I can provide you some information on how to use UDF's in SharePoint, and maybe that will point you in the right direction.    If this looks like something that you would be able to use, then you could possibly use SharePoint 2013 Designer to create your aspx page and/or UDF's.

     

    SharePoint Online Service Description

    http://technet.microsoft.com/en-us/library/jj819267.aspx

     

    SharePoint Online for developers

    http://msdn.microsoft.com/en-us/sharepoint/gg153540.aspx

     

    SharePoint Online for Office 365: Developer Guide

    http://www.microsoft.com/en-us/download/details.aspx?id=17069

     

    User Defined Functions (UDF's) and Excel Services 2010

    http://www.srinisistla.com/blog/Lists/Posts/Post.aspx?ID=207

     

     

     

    Hope that helps!


    Best Regards,
    Dan F - Microsoft Online Community Support


    Wednesday, May 15, 2013 9:01 PM
  • Hi Dan,

    Thanks for the detailed clarification response. Responding to your follow-up question, given below is the detailed description of what I mean by Excel-based templates:

    Like I mentioned in my in my initial post that this solution is basically for creating monthly or quarterly scorecards for various departments (like HR, Sales etc.) within my organization. Each scorecard will have its own specific fields and formatting applied to the sheets. Hence, each scorecard will be created as a template using excel and hosted in SharePoint site as Site Content Type (which can be utilized in the lists as content types). I was referring to these content types as excel templates.

    I guess this content type (excel) opens up in 'wopiframe.aspx' which is located in the layouts folder and hence, there is no way I would be able to add script file or link (that has JavaScript UDF defined in them) because my site in provisioned on SharePoint online. Ultimately, I will not able to use the UDF in my excel content type if I cannot add these scripts to the host page.

    If I create a page OOB and host some excel content using excel web part, then I might be able to utilize the Javascript UDF on SharePoint online.

    Kindly let me know if you needs any further clarification.



    Thanks Arut

    Thursday, May 16, 2013 6:23 AM
  • Hi Arut,

     

    Thanks for replying back and the detail.   I have researched this a bit more and found that UDF's are not available in Excel Web App.

     

    User defined functions that are installed with add-ins (reference)

    http://office.microsoft.com/en-us/excel-help/user-defined-functions-that-are-installed-with-add-ins-reference-HP010342161.aspx

     

     

    I also have some other information for you that you may or may not have seen. 

     

    Comparing Excel Services in SharePoint to Excel Web App

    http://office.microsoft.com/en-us/sharepoint-server-help/comparing-excel-services-in-sharepoint-to-excel-web-app-HA102832426.aspx

     

    Business intelligence capabilities supported in Excel Services and Excel Web App

    http://office.microsoft.com/en-us/sharepoint-server-help/business-intelligence-in-excel-and-excel-services-sharepoint-server-HA102915300.aspx#_Toc354497755

     

    I also explored any other avenues for you that may work for you.  Unfortunately, since we are using SharePoint online, your interaction with Excel Services is going to be limited compared to an on premise installation of SharePoint. 

     

    The workbooks will need to be manually created or you may use an Excel Template to give the user a start on what type of information should be in the workbook initially. 

     

    Save a workbook as a template

    http://office.microsoft.com/en-us/excel-help/save-a-workbook-as-a-template-HA102809716.aspx?CTT=1

     

     

    Hope that gets you going in the right direction!


    Best Regards,
    Dan F - Microsoft Online Community Support

    • Marked as answer by arutjothi Monday, May 20, 2013 5:46 AM
    Friday, May 17, 2013 3:03 PM
  • Dan,

    I had gone thru this information earlier. However, thanks for compiling all the relevant information (specific to Excel based customization in SharePoint online) in this thread. Hope that helps others.


    Thanks Arut

    Monday, May 20, 2013 5:46 AM