Web API Reference PivotData Microservice Documentation


<BaseUrl> = http://localhost:5000/ (by default)
Query = required query-string parameter
Query = optional query-string parameter


GET <BaseUrl>/api/cube
Description
Get metadata for all available cubes.
Result
[ {CubeMetadata}, ... ]

GET <BaseUrl>/api/cube/{cubeId}
Description
Get metadata for specified cube identifier.
Result
{CubeMetadata}

GET POST <BaseUrl>/api/cube/{cubeId}/pivot/render
Description
Calculate pivot table and render it to HTML with metadata for web pivot builder UI (jquery.pivottable.js plugin).
Parameters (GET)
Query pvtReportJson serialized JSON string
Query jsonData = (NoData|OnlyKeys|KeysAndValues) default: OnlyKeys
Parameters (POST)
Request Header Content-Type = application/json
Request Body = pvtReportJson serialized JSON string
Query jsonData = (NoData|OnlyKeys|KeysAndValues) default: OnlyKeys
Result
{
 "Configuration" : { /* Pivot table config metadata */ },
 "HtmlContent" : "<table> ...",
 "JsonData" : { /* JSON export without values */ }
}

GET POST <BaseUrl>/api/cube/{cubeId}/pivot/export/{format}
Description
Calculate pivot table for specified report configuration and export it to specified format.
Parameters
Query format = (csv|excel|pdf|json|html)
Query pvtReportJson serialized JSON string
Query fileName = download file name. Extension is appended automatically.
POST method: parameters may be passed with form-urlencoded [Request Body]
Result
response Content-Type and contents depend on the specified format.

PivotReport model API parameter

Property Value Description
Rows array of PivotReport.Dimension models list of dimensions for pivot table rows; empty by default
Columns array of PivotReport.Dimension models list of dimensions for pivot table columns; empty by default
Measures array of PivotReport.Measure models list of measures for pivot table values; "Count" is used by default (or first measure if count is not present in the cube)
SubtotalColumns true|false render sub-totals for grouped column labels; false by default
SubtotalRows true|false render sub-totals for grouped row labels; false by default
GrandTotal true|false render grand total value; true by default
TotalsRow true|false render row with totals; true by default
TotalsColumn true|false render column with totals; true by default
LimitRows integer (N) render only first N rows; when limit is specified, pagination options for rows are ignored
LimitColumns integer (N) render only first N columns. When limit is specified, pagination options for columns are ignored
RowPage
{
  "Limit" : 100,
  "Offset" : 0
}
specifies pagination options for rows; "Limit" = max number of rows, "Offset" = start riw; if not specified all riws are shown
ColumnPage
{
  "Limit" : 100,
  "Offset" : 0
}
specifies pagination options for columns; "Limit" = max number of columns, "Offset" = start column; if not specified all columns are shown
Filter string comma or space separated list of keywords to match table row or column labels (this filter is applied after data aggregation); it is possible to specify dimension hints (like "year:2015") or starts-with/ends-with match type ("start*" or "*end")
Parameters
{
  "paramName" : "val"
}
custom parameters (name-value) for the report; specified parameters should be defined in the cube configuration
OrderBy PivotReport.OrderOptions model table sorting options; by default rows & columns are sorted by labels (A-Z order)
Example
{
 "Columns" : [ {"Name":"Country"}, {"Name":"Category"} ],
 "Rows": [ {"Name": "Order Date (Year)"} ],
 "Measures": [  {"Name":"Count"} ],
 "SubtotalColumns" : true,
 "RowPage": { "Limit": 100, "Offset": 0 }
 "Filter": "2015,2016,USA",
 "Parameters": { "customer": "somevalue" }
}

PivotReport.Dimension model API parameter

Property Value Description
Name string dimension name from cube's config
LabelText string custom label for this dimension; overrides LabelText from the cube's config
Format string custom format string for dimension keys; overrides Format from the cube's config
Example
{ 
  "Name", "order_date", 
  "LabelText" : "Order Date", 
  "Format" : "{0:d}"
}

PivotReport.Measure model API parameter

Property Value Description
Name string measure name from cube's config
LabelText string custom label for this measure; overrides LabelText from the cube's config
Format string custom format string for measure values; overrides Format from the cube's config
Heatmap Table | Row | Column highlight max values according to the specified scope
Percentage GrandTotal | RowTotal | ColumnTotal calculate percentage value for this measure
Difference PreviousRow | NextRow | PreviousColumn | NextColumn calculate difference value for this measure
DifferenceAsPercentage true|false if true difference value is presented as percentage
RunningTotal Column | Row calculate running total value for this measure
Example
{
 "Name", "SumOfAmount",
 "LabelText" : "Amount (Sum)",
 "Format" : "{0:#,#.##}",
 "Percentage": "RowTotal"
}

PivotReport.OrderOptions model API parameter

Property Value Description
Dimensions array of objects:
[
 {
  "Axis" : axis,
  "Index" : dimIndex,
  "Direction" : direction
 }
]
Force table order by labels for specified axis/dimension index.
axis
"Rows"|"Columns"
direction
"Ascending"|"Descending"
dimIndex
index of dimension in the specified axis ("Rows" or "Columns" array of PivotReport)
Note: order by values rule can override order by dimension keys rules.
Values object:
{
 "Axis": axis,
 "Direction": direction,
 "Index": colOrRowIdx,
 "Measure": measureIdx
}
axis
"Rows"|"Columns"
direction
"Ascending"|"Descending"
colOrRowIdx
row or column index to order by values
measureIdx
measure index to order by values (0 by default if not specified)
PreserveGroupOrder true | false preserves labels grouping order when sort by value is specified; false by default
Example
{
  "Dimensions" : [
    {
      "Axis" : "Columns",
      "Direction" : "Descending",
      "Index" : 0
    }
  ],
  "Values" : {
    "Axis", "Rows",
    "Direction" : "Descending",
    "Index" : 0,
    "Measure": 0
  },

CubeMetadata model API result

Property Value Description
Id string unique cube identifier
Name string User-friendly cube name
Description string Extended cube description (if specified)
Dimensions array of CubeMetadata.Member models list of dimensions that may be used in the reports
Measures array of CubeMetadata.Member models list of measures that may be used in the reports
Parameters array of CubeMetadata.Parameter models list of cube parameters that may be specified in the reports

CubeMetadata.Member model API result

Property Value Description
Name string unique member identifier
LabelText string user-friendly title
Format string default value format string
Type string determines member type:
  • for dimensions: empty or "Expression"
  • for measures: type of the aggregator ("Count", "Sum", "Average", "Min", "Max" or "Expression" for custom formula)
Params array of strings Additional parameters:
  • for dimension with Type="Expression": first param is an expression, next ones are names of dimensions used in the expression
  • for measure with Type="Expression": first param is a formula, next ones are names of measures used in the formula
  • for measure with Type!="Expression": name of the column for the aggregate function

Cube.Parameter model

Property Value Description
Name string unique parameter name
LabelText string user-friendly title
DataType string Type of the parameter value: "String", "Int32", "Int64", "Decimal", "Double", "DateTime", "Boolean"
Multivalue true|false If true parameter can accept several values (array)