Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • Web Development

    XLCubed Web Edition allows application developers to embed XLCubed reports in their own web pages.

    Several reports can be embedded in a single page and linked together using parameters as required, allowing you to build custom pages out of existing published workbooks.

    This page describes the web API, and the html and javascript required to embed reports.

    Direct report linking

    It is possible to link to a published report directly using the following URL syntax:

    http://Server/XLCubedWeb/WebForm/ShowReport.aspx?rep=folder/report.xml

    This can also include any Web Parameters to allow specific criteria to be passed.

    Embedding Workbooks

    Standards Mode

    XLCubed does not support "Quirks Mode" in Internet Explorer. To ensure that IE renders your page in standards mode you will need to put a suitable doctype at the top of your page.

    Sample doctypes can be found on wikipedia.

    Required Includes

    Embedded XLCubed reports reference various javascript libraries which must be included in your page.

    You should change the references to "MyServerName" to the name of the XLCubed web site, eg: "WebServer01/XLCubedWeb"

    Required Css:

    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/XLCubedWeb.css" />
    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/XLCubed.Workbook.css" />
    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery-ui-1.8.custom.css" />
    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery.treeview.css" />
    

    Required Javascript:

    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.4.2.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.8.custom.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.treeview.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.treeview.async.js"></script>
        
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.cookie.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.n-contextmenu.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.scrollTo-1.4.2-min.js"></script>
        
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.xlcubedextensions.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/microsoft/microsoftajax.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.Web.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.Workbook.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.Toolbars.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.Repository.js"></script>
    
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.DialogManager.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.Controls.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.Dialogs.js"></script>
            
    <script type="text/javascript" src="http://MyServerName/WebServices/WorkbookService.svc/js"></script>
    
    Updates for version 6.5

    As of version 6.5 many resources have been consolidated, and you can instead include the following.

    Required Css:

    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery-ui-1.8.custom.css" />
    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery.treeview.css" />
    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/XLCubed.min.css" />
    

    Required Javascript:

    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.4.2.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.8.custom.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/Microsoft/MicrosoftAjax.js"></script>        
    <script type="text/javascript" src="http://MyServerName/WebServices/WorkbookService.svc/js"></script>
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.min.js"></script>
    
    Updates for version 7.0

    As of version 7.0 the version of jQuery has been updated to 1.7.2, so the includes are as follows.

    Required Css:

    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery-ui-1.8.custom.css" />
    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery.treeview.css" />
    <link rel="stylesheet" type="text/css" href="http://MyServerName/Css/XLCubed.min.css" />
    

    Required Javascript:

    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.7.2.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.8.custom.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/Microsoft/MicrosoftAjax.js"></script>        
    <script type="text/javascript" src="http://MyServerName/WebServices/WorkbookService.svc/js"></script>
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.min.js"></script>
    


    Updates for version 8.0

    The jQuery version has been incremented, so now the following files should be included instead of the previous version of jQuery.

    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.11.0.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-migrate-1.2.1.min.js"></script>
    

    Page Markup

    XLCubed reports are loaded into a div element, which requires markup in the following style

    <div id="workbookdiv" style="position:relative;height:300px;width:600px;border:1px solid silver;"></div>
    

    The id attribute is mandatory, and must be unique in the page.

    The div must have the position of its style set, either inline as above or via CSS. It must be relative or absolute.

    Javascript

    Loading

    Initialisation of the workbook is done via javascript, the simplest example is as follows:

    <script>
    function loadXLCubed(){
        //Initialise XLCubed
        var xlcubedOptions = { BaseUrl:"http://MyServerName" };
        XLCubed.Settings.Init(xlcubedOptions);
    
        //create the XLCubed workbook
        var div = document.getElementById("workbookdiv");
        var book = new XLCubed.Workbook(div);
    
        //load a report into the workbook
        book.Load("MyFolder/MyReport.xml");
    }
    </script>
    

    Here MyServerName must again be replaced with the actual XLCubed web site.

    The string passed to the Load() function is the path in the repository to an existing report published from Excel.

    Optionally you can pass parameters to the new XLCubed.Workbook() call to customise the appearance of the report

    var opts = {	HorizontalScrollBar:false,
    		VerticalScrollBar:true,
    		SheetTabs:true,
    		ContextMenus:false };
    
    var book = new XLCubed.Workbook(div, opts);
    
    If you wish to load a different report, then simply call
    book.Load()
    again on the same book variable, do not create a second book in the same div.
    Impersonation

    XLCubed can impersonate a named user if required. To do this the following API can be called.

    //Set up the user we want to run queries as
    var logonOptions = {	Username:"MyUsername",
    			Domain:"MyDomain",
    			Password:"MyPassword"};
    		
    XLCubed.Settings.Logon(logonOptions);
    

    The Logon() function must be called after XLCubed.Settings.Init() and before book.Load()

    Parameters

    If a report is published with web parameters, these can be setup through the API.

    You can load a report with parameters set as follows:

    //create book as above...
    
    var params = {	Date:"January 2010",
    		Geography:"Europe" };
    		
    book.Load("MyFolder/MyReport.xml", params);
    

    After a book has been loaded you can update the parameters as follows:

    //book variable from elsewhere, the report is already loaded
    
    var params = {	Date:"March 2010",
    		Geography:"America" };
    		
    book.ApplyParameters(params);
    

    As of Version 7.6 you can also retrieve the current values of the parameters:

    //book variable from elsewhere, the report is already loaded
    
    var params = book.GetParameters();
    		
    for(var param in params){
    	alert(param + " = " + params[param]);
    }
    
    Toolbar

    You can create a toolbar for your report using the following

    Markup:

    <div id="toolbar"></div>
    

    Script:

    //book already created
    var toolDiv = document.getElementById("toolbar");
    var toolbar = new XLCubed.WorkbookToolbar(toolDiv, book);
    
    Events

    An XLCubed workbook will fire events which you can attach to to customise your page and link reports together.

    Binding to events uses the following syntax

    //book already created
    book.Bind(eventName, eventHandlerFunction)
    

    The eventHandlerFunction takes two arguments, an event object and a data object containing information about the event that fired.

    function loadXLCubed(){
        //book already created
        book.Bind("load", loadHandler);
    }
    
    
    function loadHandler(e, data){
        //we can get a reference to the book from here
        var b = data.workbook;
        alert("loaded");
    }
    

    The events fired are

    Event Name Fired When Data Properties
    load Workbook load completed workbook
    render Workbook is updated and rendered workbook
    exception An error occured workbook

    message //text of error

    cancel //set to true to stop display of message to user

    expire Users session timed out workbook
    dimensionslicerchange User updates a slicer workbook

    name //slicer name if setup

    items // array of unique names selected

    xl3linkclick User follows an XL3Link() formula workbook

    linkAddress

    linkTo

    linkType

    values // javascript object, mapping cells to values

    memberclick User clicks a member in a grid or formula workbook

    cell

    cellType ("gridmember", or "memberlookup")

    hierarchy (unique name)

    getMember (callback function to get member metadata, receives an object)

    data.getMember(function(metadata){ });
    
    cubecellclick (New in Version 9) User clicks data in a grid or formula workbook

    cell

    cellType ("gridcell", or "lookup")

    location cell position {x:number, y:number}

    gridlocation grid only, cell position in the grid data

    getCubeCell (callback function to get cell metadata, receives an object)

    data.getCubeCell(function(cellInfo){ });
    
    parameterschanged Cell parameter values changed workbook

    parameters

    Methods

    An XLCubed workbook has a public API which you can call once a book has been loaded.

    Method Description
    Load(path) Loads the requested report, closes any existing report.
    RecalculateAll() Causes all grids, tables and formulae to be refreshed.
    ApplyParameters(params) params is a JS literal object. Updates the values in the workbook and refreshes the display.
    GetParameters() Returns a JS literal object of the current parameter values.
    Bind(eventName, eventHandlerFunction) Attaches the handler to the specified event.
    SaveToExcel(keeplive) Saves the workbook and downloads it. The keeplive parameter controls whether the book is fixed or saved as a live XLCubed report. Possible values as "NOTHING" and "FORMULAANDGRID".
    GetCellValues(address, callback) Gets the values in the specified range, and returns them as an array. Callback is called with the array, whose first dimension is the row, and second is the column. (i.e. An array of arrays) New in Version 9
    SubmitChanges() Commits any changes made by the user and recalculates the workbook.
    Object Reference
    • cellInfo

    This object has the cell value, and information on the tuple coordinates. A tuple axis has the hierarchies on the axis, and the members in an array.

    For normal axes the name will be "axis" + idx, where idx is the axis position. E.g. 0 = Columns, 1 = Rows.

    {
        "value":object,
    
        "coordinates":[
           {"axis":"where",
            "hierarchies":[
                {"uniqueName":"hier1",
                 "members":["mem1"]},
                {"uniqeName":"hier2",
                 "members":["mem2a", "mem2b"]}
           ]},
           {"axis":"axis0",
            "hierarchies":[
                {"uniqeName":"hier3",
                 "members":["mem3"]}
           ]} ...
        ]
    }
    

    Embedding Repository

    Required Includes

    You can query the repository to get a list of the folders and files available on the server. To do this you must reference a couple of javascript files.

    You should change the references to "MyServerName" to the name of the XLCubed web site, eg: "WebServer01/XLCubedWeb"

    Required Css:

    None
    

    Required Javascript:

    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.7.2.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.8.custom.min.js"></script>
    <script type="text/javascript" src="http://MyServerName/js/Microsoft/MicrosoftAjax.js"></script>        
    <script type="text/javascript" src="http://MyServerName/WebServices/RepositoryService.svc/js"></script>
    <script type="text/javascript" src="http://MyServerName/js/XLCubed.min.js"></script>
    

    Javascript

    Loading

    Initialisation of the repository is done via javascript, the simplest example is as follows:

    <script>
    function loadXLCubed(){
        //Initialise XLCubed
        var xlcubedOptions = { BaseUrl:"http://MyServerName" };
        XLCubed.Settings.Init(xlcubedOptions);
    
        //create a repository object
        var oRep = new XLCubed.Repository();
        //and query the server.
        //This is asynchronous, so pass in a callback to handle the results
        oRep.GetRepository(
    	function(oTopFolder)
    	{
    		alert(oTopFolder.folders.length);
    	});
    }
    </script>
    

    Here MyServerName must again be replaced with the actual XLCubed web site.

    The oTopFolder object is an instance of the folder class, which itself contains file.

    You can then iterate over the contents of the top level folder to create your own UI, or do anything else you'd like.

    Object Reference
    Folder
    Property Content Type
    path String
    name String
    folders Folder[]
    files Files[]
    File
    Property Content Type
    path String
    name String
    description String
    publishedby String
    datepublished Date

    Parameters

    This was added in Version 9

    You can retrieve the names of the report parameters in javascript, for example

    <script>
    function loadXLCubed(){
        //Initialise XLCubed
        var xlcubedOptions = { BaseUrl:"http://MyServerName" };
        XLCubed.Settings.Init(xlcubedOptions);
        //fill this in with a real path of a report
        var reportPath = "???";
    
        //create a repository object
        var oRep = new XLCubed.Repository();
        //and query the server.
        //This is asynchronous, so pass in a callback to handle the results
        oRep.GetReportParameters(reportPath,
    	function(parameterNames)
    	{
    		alert(parameterNames.length);
    	});
    }
    </script>
    

    Customising HTML

    From Version 7.1 it is possible to configure the HTML output of XLCubed web reports. This is configured on the XLCubed format sheet in Excel.

    Column Content
    Object Type "Grid" or "Table"
    Name Name of the object to customise. If blank will be used as the default.
    Area Report area to customise, see below.
    CssClass Value for the class attribute of the cell.

    Area Names

    Grid

    Possible values are:

    • FiltMemLabel
    • FiltMem
    • ColMemLabel
    • ColPropLabel
    • ColMem
    • ColProp
    • RowMemLabel
    • RowPropLabel
    • RowMem
    • RowProp
    • Data
    Table

    Possible values are:

    • WhereLabel
    • WhereMem
    • Header
    • Data