NReco NReco{.NET REusable COmponents}

Web API Reference PivotData Microservice Documentation

Get PivotData Microservice

<BaseUrl> = http://localhost:5000/ (port 5000 is used by default, you can change it)
Query = required query-string parameter
Query = optional query-string parameter
HTTP error codes:
400
Bad request: incomplete or incorrect API call parameters.
401
Unauthorized: JWT authentication is configured but JWT token is not provided (incorrect, expired).
404
Not found: API resource (cube ID) is unknown.
429
Too many requests: occurs when API limits are configured and request waits in a queue too long (wait timeout is reached).
500
Application error: details are provided in the HTTP response (JSON).
501
Not supported: requested API or feature is not enabled (for example, table reports or search queries may be disabled in the appsettings.json.
GET <BaseUrl>/api/cube → JSFiddle example
Description
Get metadata for all available cubes.
Result
[ {CubeMetadata}, ... ]
GET <BaseUrl>/api/cube/{cubeId} → JSFiddle example
Description
Get metadata for specified cube identifier.
Result
{CubeMetadata}
GET POST <BaseUrl>/api/cube/{cubeId}/pivot/render → JSFiddle example
Description
Calculate pivot table and render it to HTML with metadata for web pivot builder UI (webpivotbuilder.js plugin) or interactive features (jquery.pivottable.js plugin).
Parameters (GET)
Query pvtReportJson serialized JSON string
Query jsonData = (NoData|OnlyKeys|KeysAndValues) default: OnlyKeys
Query refresh = (true|yes|1) force data load (execute DB query, ignore cached data if present)
Parameters (POST)
Request Header Content-Type = application/json
Request Body = pvtReportJson serialized JSON string
Query jsonData = (NoData|OnlyKeys|KeysAndValues) default: OnlyKeys
Query refresh = (true|yes|1) force data load (execute DB query, ignore cached data if present)
Result
{
 "Configuration" : { /* Pivot table config metadata */ },
 "HtmlContent" : "<table> ...",
 "JsonData" : { /* JSON export without values */ }
}
GET POST <BaseUrl>/api/cube/{cubeId}/pivot/export/{format} → JSFiddle example
Description
Calculate pivot table for specified report configuration and export it to specified format.
Parameters
Query format = ( csv |excel | excelpivottable | pdf | json | html | png )
Query pvtReportJson serialized JSON string
Query fileName = download file name. Extension is appended automatically if needed.
Query valueFormatting = (true|false) explicitly specifies if values should be formatted. By default formatting is applied for HTML/CSV/PDF exports and not applied for JSON/Excel exports.
Query tokens = JSON object with key-value pairs (these tokens maybe used in PDF export template).
Query enableJsonPivotSubtotals = (true|false) enable subtotals in JSON export (if they are enabled in the report JSON). False by default.
Query jsonData = (OnlyKeys|KeysAndValues) default: KeysAndValues, applicable only for JSON export format.
Query refresh = (true|yes|1) force data load (execute DB query, ignore cached data if present)
For POST: parameters may be passed with form-urlencoded [Request Body]
Result
response (Content-Type and contents) depends on the specified format.
GET POST <BaseUrl>/api/cube/{cubeId}/table/export/{format} → JSFiddle example
Description
Returns raw (non-aggregated) rows for specified 'flat table' report configuration. Note: this API is disabled by default. To enable it add to appsettings.json: 
"PivotDataService": {
  "EnableTableApi" : true
}
Parameters
Query format = ( csv | excel | pdf | json | html | png )
Query tableReportJson serialized JSON string
Query fileName = download file name. Extension is appended automatically if needed.
Query valueFormatting = (true|false) explicitly specifies whether values should be formatted. By default values are formatted.
Query rowMode = (array|object) only for JSON format: determines how data rows are serialized.
Query onlyRows = (true|false) only for JSON format: only data rows are returned (no JSON array start/end - techically an incorrect JSON output!).
Query tokens = JSON object with key-value pairs (these tokens maybe used in PDF export template).
POST method: parameters may be passed with form-urlencoded [Request Body]
Result
response (Content-Type and contents) depends on the specified format.
GET <BaseUrl>/api/cube/{cubeId}/search/suggestions
Description
Returns list of autocomplete suggestions for the specified prefix. Note: this API is disabled by default. To enable it add to appsettings.json: 
"PivotDataService": {
  "EnableSearchApi" : true
}
Parameters
Query prefix = keyword prefix to match
Query limit = max number of suggestions (10 by default).
Result
["suggestion1", "suggestion2", ... ]
GET <BaseUrl>/api/cube/{cubeId}/search/query
Description
Returns list of pivot table configs that are most relevant to the specified search query. Note: this API is disabled by default. To enable it add to appsettings.json: 
"PivotDataService": {
  "EnableSearchApi" : true
}
Parameters
Query q = free-form search query
Query limit = max number of reports to return (10 by default).
Result
[ {report1}, {report2}, ... ]

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
SubtotalInsideGroup true|false render sub-totals as a special group's item (vs subtotal item on the same level as its group); false by default
GrandTotal true|false render grand total value; true by default
TotalsRow true|false render row with totals; true by default
TotalsRowPosition Last|First where to place row(s) with totals; 'Last' by default
TotalsColumn true|false render column with totals; true by default
TotalsColumnPosition Last|First where to place columns(s) with totals; 'Last' 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
RepeatRowKeysInGroups true|false Repeat keys in groups of the same row-axis dimensions (false by default).
RepeatColumnKeysInGroups true|false Repeat keys in groups of the same column-axis dimensions (false by default).
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)
Labels PivotReport.LabelOptions model customization of various labels that are used in the pivot table
Styles PivotReport.Styles model customization of table's text styles
ExpandCollapse 
{
  "Enabled": true
}
 activates expand/collapse mode for label groups
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
Subtotals bool If set determines whether to show subtotals for this dimension. Subtotals are shown only if they are enabled for the dimension's axis (report's SubtotalColumns or SubtotalRows).
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
EmptyValue object value that should be used for 'empty' table cells (when number of aggregated records = 0). By default null is used (which is rendered as white space).
Heatmap Table | Row | RowTotal | Column | ColumnTotal highlight max values according to the specified scope
HeatmapColor string base RGB-hex color for heatmap for this measure, for example: "FF0000"
Percentage GrandTotal | RowTotal | ColumnTotal calculate percentage value for this measure
PercentageDataBar 
{
 "DisplayMode":"OriginalValue",
 "Color": "#A0A0E0",
 "ShowBarsForTotals":true,
 "ShowBarsForSubtotals":true
}
 Apply "Percentage" calculation as in-cell data bars (for HTML output and PDF/Excel exports).
DisplayMode can be: OriginalValue | PercentageValue | DataBarOnly
Difference
[deprecated] 		PreviousRow | NextRow | PreviousColumn | NextColumn calculate a difference value for this measure
DifferenceAsPercentage
[deprecated] 		true|false if true difference value is presented as a percentage
DifferenceSameGroup
[deprecated] 		true|false if true difference is calculated only between cells inside the same group.
Difference 
{
 "Mode":"PreviousRow",
 "AsPercentage": false,
 "SameGroup": false,
 "Colors": ["red","navy"],
 "AsSuffixTag": "sup",
 "SuffixFormat": null,
}
 calculate a difference value for this measure with the specified options:
Mode
PreviousRow | NextRow | PreviousColumn | NextColumn
AsPercentage
if true difference value is presented as a percentage.
SameGroup
if true difference is calculated only between cells inside the same group.
Colors
apply conditional text color for difference value depending on the sign (negative/positive).
AsSuffixTag
if set difference values are wrapped with the specified tag.
SuffixFormat
apply custom format for difference values (only if AsSuffixTag is set). For example (arrows instead of +/- sign): {0:↑0.##;↓0.##;0}
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 single object or array of: 
{
 "Axis": axis,
 "Direction": direction,
 "Index": colOrRowIdx,
 "Measure": measureIdx
}
Axis
"Rows"|"Columns"
Direction
"Ascending"|"Descending"
Index
row or column index to order by values (can be null to order by totals)
Measure
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
  },

PivotReport.LabelOptions model API parameter
Property Value Description
EmptyText "(empty)" text used for 'null' dimension keys.
OtherText "..." when top-N limit for rows and/or columns is set everything beyond the limit are included into special 'other' groups. By default it is displayed as "...". Set this option to 'null' if you want to exclude 'other' groups at all.
SubtotalSuffixText " Totals" this suffix is added to the group labels that correspond to row or column with sub-totals.
TotalsRowText "Totals" Label for row with totals.
TotalsColumnText "Totals" Label for column with totals.
ExcelReportWorksheetName "Report" A name used for a worksheet that contains a pivot table layout in Excel export of this report.
ExcelChartWorksheetName "Chart" A name used for a worksheet that contains a chart in Excel export of this report (if applicable).

PivotReport.StyleOptions model API parameter
Property Value Description
Columns 
{Bold:true, Italic:false, TextAlign:"Center"}
 Text styles for labels of columns.
TotalsColumn 
{Bold:true, Italic:true, TextAlign:"Right"}
 Text styles for labels of columns with subtotals.
Rows 
{Bold:true, Italic:false, TextAlign:"Left"}
 Text styles for labels of rows.
TotalsRow 
{Bold:true, Italic:true, TextAlign:"Right"}
 Text styles for labels of rows with subtotals.
TotalsValue 
{Bold:true, Italic:true}
 Text styles for values of rows with subtotals.
FontSize 
10
 A font size (number) in pt. Affects web view, PDF and Excel exports.
FontName 
"Arial"
 A font name to use for pivot table cells. Affects web view, PDF and Excel exports.
GridLabelsHighlight 
true
 Determines whether to hightlight table cells with labels. For a web view/PDF export this is grey background of these cells, in Excel exports also affects solid (black) borders.

TableReport model API parameter
Property Value Description
Columns array of PivotReport.Dimension models list of dimensions that are used as columns for 'flat table'. At least one entry should be specified.
LimitRows integer or null Number of rows to return. If not specified value 100 is used by default. Max number is 10,000.
OffsetRows integer Return rows starting from specified offset (0 by default).
Filter string comma-separated list of keywords or conditions like "year>2015" or "name:start*"
Parameters 
{
  "paramName" : "val"
}
 custom parameters (name-value) for the report; specified parameters should be defined in the cube configuration
OrderBy 
[
 {
  "Name":"dimName",
  "Direction":"Descending"
 }
]
 table sorting options; note that some data sources may not support sorting by multiple columns.
Example 
{
 "Columns" : [ {"Name":"Country"}, {"Name":"Category"} ],
 "LimitRows": 500,
 "RowPage": { "Limit": 100, "Offset": 0 }
 "Filter": "country=USA",
 "OrderBy": [
  {
    "Name":"Category",
    "Direction":"Ascending"
  }
 ]
}

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)

API Error JSON
Property Value Description
Message string Human-readable error description. It can be shown to the user.
ErrorType string One of the following OR empty (if error is something different): DbExecQuery, DbOpenConnection (specific to SQL-cubes), DimensionUnknown, DimensionEvalExpression, MeasureUnknown, MeasureEvalExpression, ParameterInvalidValue, ParameterEvalExpression.
Exception string .NET exception (including a stacktrace). Included only if ExposeException in the appsettings.json is enabled.
Query 
DB query string
 Included when ErrorType is DbExecQuery or DbOpenConnection AND if cube's config has "ExposeRawQuery":true entry.
Dimension 
{ "Name" : "dimName" }
 Included when ErrorType is DimensionUnknown or DimensionEvalExpression.
Measure 
{ "Name" : "measureName" }
 Included when ErrorType is MeasureUnknown or MeasureEvalExpression.
Parameter 
{ "Name" : "paramName" }
 Included when ErrorType is ParameterInvalidValue or ParameterEvalExpression.
Example 
{
 "Message": "Name or service not known",
 "ErrorType": "DbOpenConnection"
}

© nrecosite.com 2008-2024

Have a question?   info@nrecosite.com