Incorta

In Version 10 XLCubed supports connections to Incorta data sources.

In the connections screen select the Incorta type and enter the url you would normally use to access the Incorta front end as the server name. If this includes the tenant name then that will be detected, otherwise you'll also need to enter that information.

e.g. http://myServer/incorta/!tenantName/

You can then authenticate log in and access the data model.

Hierarchies

You can add custom metadata to the Incorta instance that defines hierarchies which will be visible in XLCubed. These allow drilldown and custom calculations to be added

Definition

The hierarchy definitions must be held in a schema called XLCubedMetadata. This should contain a table called Hierarchies and has three columns Schema, Table and Json.

The Schema and Table defines where the hierarchy will appear in the XLCubed UI, and the Json defines the hierarchy structure.

There are two types of hierarchy, multi-level and self-join.

This example shows a product hierarchy with three levels, each is based on a column in the table. This works well if your levels are already defined in the existing schema.

The "name" attributes define what XLCubed calls the hierarchy and levels and must not be changed once the hierarchy is in use in reports. The labels can be freely changed.

{

   "name": "PROD_HIER",
   "label": "Products",
   "isDefault": true,
   "levels": [{
           "name": "PROD_HIER_CAT",
           "label": "Product Category",
           "field": "Online_Store.OrderDetails.CategoryName"
       }, {
           "name": "PROD_HIER_SUBCAT",
           "label": "Product Subcategory",
           "field": "Online_Store.OrderDetails.SubcategoryName"
       }, {
           "name": "PROD_HIER_PROD",
           "label": "Product Name",
           "field": "Online_Store.OrderDetails.ProductName"
       }
   ]

}

This example shows using formulae to define the levels of the hierarchy. In this case we have a date field and want to create a multi-level hierarchy from that without adding extra calculations to our model.

We have simple a simple formula to define the content of each level, and for some levels add a sortFormula to control the order. (This prevents the months being ordered April, August, ...)

{

   "name": "ORDERDATE_YQM",
   "label": "Order Date YQM",
   "type": "time",
   "levels": [{
           "name": "DATE_Y",
           "label": "Year",
           "formula": "Year(Online_Store.OrderDetails.OrderDate)",
           "type": "timeyears"
       }, {
           "name": "DATE_YQ",
           "label": "Quarter",
           "formula": "\"Q\" + quarter(Online_Store.OrderDetails.OrderDate) + \" \" + year(Online_Store.OrderDetails.OrderDate)",
           "sortFormula": "(year(Online_Store.OrderDetails.OrderDate) * 100) + quarter(Online_Store.OrderDetails.OrderDate)",
           "type": "timequarters"
       }, {
           "name": "DATE_YQM",
           "label": "Month",
           "formula": "monthName(Online_Store.OrderDetails.OrderDate) + \" \" + year(Online_Store.OrderDetails.OrderDate)",
           "sortFormula": "(year(Online_Store.OrderDetails.OrderDate) * 10000) + (quarter(Online_Store.OrderDetails.OrderDate) * 100) + month(Online_Store.OrderDetails.OrderDate)",
           "type": "timemonths"
       }
   ]

}

Self-join hierarchy

Self-joins can be defined in Incorta and used in XLCubed. In this case we defined the fields in the self-joined table that we want to use as not all will be relevant. e.g. in an Employees table the FullName would make a good hierarchy, but Title would not.

{

   "selfJoinFields": ["EMAIL", "JOB_TITLE"]

}

Example

This Excel workbook contains sample hierarchies that you can use as a template for your own upload.