Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • SharePoint Integration

    XLCubed have developed an option for tighter integration with SharePoint, allowing SharePoint to act as a repository for XLCubedWeb reports, so publication and report navigation is contained within SharePoint.


    Installing

    There are several steps to complete configuration, this requires XLCubedWeb to be already setup and configured. There are 2 ways to display the reports, as standalone pages redirecting the user to XLCubedWeb or by creating a "Container" page so the report is displayed within a standard SharePoint page.

    Option 1: SharepointOption1.png

    Option 2: SharepointOption2.png

    Custom Handler

    XLCubed Handler Assembly

    The first stage is to install the custom handler for XLCubed Reports on the SharePoint server. You need to download and extract following file:

    http://www.xlcubed.com/downloads/xlcubedhandler.zip

    Once installed this needs to be installed in the GAC, the easiest way is to use Windows Explorer to drag the file into the \windows\Assembly folder.

    SharePointAssembly.png

    Windows Server 2012

    To install an assembly to the GAC in 2012, you will need to run PowerShell as an Administrator and use the following commands (assuming the assembly has been extracted to "c:\xl3"):

    Set-location "c:\xl3"
    [System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
    $publish = New-Object System.EnterpriseServices.Internal.Publish
    $publish.GacInstall("c:\xl3\xlcubed.handlermapping.dll")
    iisreset
    

    Register Handler with IIS

    You now need to register the Assembly with IIS, this can be done by editing the SharePoint web.config, usually in:

    \inetpub\wwwroot\wss\VirtualDirectories\80

    in the

     <system.web>
        <compilation>
           <assemblies>
    

    collection add the line:

           <add assembly="XLCubed.HandlerMapping, Version=1.0.0.0, Culture=neutral, PublicKeyToken=add6446b63d88928" />
    


    in:

     <system.webServer>
        <modules>
    

    Add the following:

         <add name="XLCubedModule" type="XLCubed.HandlerMapping.XLCubedModule, XLCubed.HandlerMapping, Version=1.0.0.0, Culture=neutral, PublicKeyToken=add6446b63d88928" />
    

    Set icon file type

    Download the following file: http://www.xlcubed.com/downloads/xlcubed.gif and copy it to the "Template\Images" folder, for example:

    \Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Template\Images
    

    or for SharePoint 2013 (the 14 folder exists, but use the 15 instead):

    \Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\Template\Images
    


    Then edit "docicon.xml" in the "Template\Xml" folder (\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Template\Xml) and add the following line to:

     <DocIcons>
       <ByExtension>
    


       <Mapping Key="xl3wbz" Value="xlcubed.gif"/>
    


    And for Version 8 onwards the following additional extension:

       <Mapping Key="xl3abz" Value="xlcubed.gif"/>
    

    Custom Report Page (Optional)

    If you want to display the XLCubed Reports within the context of Sharepoint (Option 2 in the example screens), then you can do that by using creating a page containg a custom webpart.

    WebPart installation

    Follow the standard SharePoint guide.

    Page creation

    Create a page to display all XLCubed reports in, in this case we'll call it XLCubedReport.

    Insert a "XLCubed Page Viewer" web part and edit it:

    Set the height of the web part and change the "Chrome Type" to none:

    SharepointPageViewerAppearance.png

    Under "Custom Properties" set "Report URL" to the following (updating it to reflect the actual xlcubedweb location):

     http://servername/xlcubedweb/WebForm/ShowReport.aspx
    

    After setting the properties you should see an XLCubed error message, this is fine it's showing that the page is able to find the server but hasn't been supplied with a report to display yet.

    Configure redirect

    The next step is to add the configuration items required for the custom handler installed previously. Open the SharePoint web.config, usually in:

     \inetpub\wwwroot\wss\VirtualDirectories\80 
    

    To the section:

     <configuration>
        <appSettings>    
    

    Add a new key:

       <add key="XLCubedReportURL" value="http://hvxl3-sp/SitePages/XLCubedReport.aspx?rep=%rep%&amp;embedded=SharePoint&amp;toolbar=true" />
    

    If you are going for "Option 1" enter the XLCubedWeb server address:

     http://xlcubedservername/xlcubedweb/WebForm/ShowReport.aspx?rep=%rep%&amp;embedded=SharePoint
    

    If you have created a custom sharepoint page then use that instead:

     http://sharepointserver/SitePages/XLCubedReport.aspx?rep=%rep%&amp;embedded=SharePoint
    


    If you want to include a toolbar when display reports, you can add the following:

     &amp;toolbar=true
    

    Subsites

    You can also use a relative path to keep navigation within the context of any sub-sites:

     ./XLCubedReport.aspx?rep=%rep%&amp;embedded=SharePoint
    

    With this kind of configuration a SharePoint XLCubed Report page would need to be created in the every location to which reports can be published (i.e. \sites\subsite\sitepages\xlcubedreport.aspx, \SitePages\XLCubedReport.aspx etc.)

    Searching

    To allow published reports to be indexed by SharePoint you need to register the report extension type with SharePoint and then add the file type to the list of files to be indexed.

    The SharePoint crawler will index the contents of the Workbook as it was at publication time.


    Register file extension

    Add the following reg keys (updating the version of SharePoint if not 2010):

     [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Setup\ContentIndexCommon\Filters\Extension\.xl3wbz]
     @=hex(7):7b,00,32,00,30,00,45,00,38,00,32,00,33,00,43,00,32,00,2d,00,36,00,32,\
     00,46,00,33,00,2d,00,34,00,36,00,33,00,38,00,2d,00,39,00,36,00,42,00,44,00,\
     2d,00,39,00,30,00,46,00,34,00,46,00,36,00,37,00,38,00,34,00,45,00,42,00,43,\
     00,7d,00,00,00,00,00
    
     [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Search\Setup\ContentIndexCommon\Filters\Extension\.xl3wbz]
     @=hex(7):7b,00,32,00,30,00,45,00,38,00,32,00,33,00,43,00,32,00,2d,00,36,00,32,\
     00,46,00,33,00,2d,00,34,00,36,00,33,00,38,00,2d,00,39,00,36,00,42,00,44,00,\
     2d,00,39,00,30,00,46,00,34,00,46,00,36,00,37,00,38,00,34,00,45,00,42,00,43,\
     00,7d,00,00,00,00,00
    

    You can download an exported reg file from the following link:

    http://www.xlcubed.com/downloads/xlcubedsharepoint_regkey.zip

    Add file types to be indexed

    Go to the SharePoint Central Administration Website, under Application Managment => Manage Service Applications => Search Service Application:

    SharepointSearchService.png

    Under "Crawling", select "File Types" and click "New File Type":

    SharePointNewFileType.png

    enter "XL3WBZ" as the extension

    Sharepoint on different server

    If SharePoint is on a different server to XLCubedWeb and XLCubedWeb is using "Windows Authentication" then by default the User's credentials will not be passed from XLCubedWeb to Sharepoint, this will prevent the reports from displaying correctly (you'd get an invalid or no login credentials error when accessing the reports)

    You could configure Kerberos, this would allow the credentials to be passed correctly (XLCubedWeb uses WebDav to access the reports) or enable the following setting on the XLCubedWeb server:

       <add key="UseAppPoolUserForSharepointDownload" value="false" />
    

    With that setting enabled then any SharePoint access from XLCubedWeb will be done using the Application Pool identity, so set that to run as an account that has the required permissions in SharePoint

    Published Connections, Queries and Favourite

    To allow publishing of Connections, Sql Queries and Favourites you must create the following folder structure at the root of the SharePoint site, the users would need to connect at the root level to use this feature:


      /XLCubed
           /PublishedConnections
           /PublishedGrids
           /PublishedSQLQueries
    

    Saving to SharePoint

    With Version 8 you can add the ability to save any modified (and adhoc reports), displayed using the Page Viewer Web part, directly back to SharePoint. To do this ensure you are running the latest XLCubed Web part and add the following line to the XLCubedWeb Config:

      <add key="SharePointServerUrl" value="http://sharepointserver"/>
    

    The "Save" option on the toolbar will now show the SharePoint structure and the file can be saved as usual.

    This step is also needed if setting up scheduling for SharePoint based servers (Version 9 Requires)

    Adhoc Reports

    With Version 8 you can include links to the adhoc designers in SharePoint (both standard grids and Analytic Books). To do this create a new page containing the Page Viewer webpart and then link to:

     http://server/xlcubedweb/WebForm/Connect.aspx?toolbar=True&embedded=SharePoint
    

    for Adhoc grids or for Analytics books use the following:

     http://server/xlcubedweb/WebForm/Connect.aspx?Mode=AnalyticsBook&toolbar=True&embedded=SharePoint
    


    Mobile app access

    With Version 8 the mobile app can access reports published directly to sharepoint, to do this ensure the web.config has the following settings ("SitePages" is optional and can be replaced with any sub folders to limit the reports that can be viewed):

      <add key="SharePointServerUrl" value="http://SharePointServer/SitePages"/>
      <add key="SharePointUseRepository" value="true"/>
    

    For using this in the mobile app ensure the user registers the XLCubedWeb server, once connected they will see any reports published directly to SharePoint. Please note: Saving is limited to the "My Reports" folder and is saved directly to the XLCubedWeb server and not SharePoint

    Using

    Once setup the SharePoint integration allows users to publish XLCubed reports directly to SharePoint

    Publishing

    Enter the SharePoint server for the XLCubedWeb server address and check the "SharePoint" checkbox:

    SharepointPublish.png

    You can add any sub folders directly to the address you enter, for example:

     http://sharepoint/SitePages/Reports
    

    Viewing

    Any published reports will be displayed and viewed within SharePoint:

    SharepointSitePages.png

    SharepointOption2.png


    Customising

    Adding a "Load to Excel" option

    If you want to add an option to load a report directly into Excel you can define a Custom Action with a URL of:

     http://xlcubedserver/xlcubedweb/webform/showreport.aspx?rep={SiteUrl}{ItemUrl}&XL3ReportType=excelfixed
    
    

    SharepointCustomAction.png

    SharepointLoadToExcel.png