SeekTable can be connected to OLAP cube (like SQL Server Analysis Services) via XMLA-over-HTTP(S) endpoint. MDX queries are composed and executed on-the-fly; you can specify your own MDX SELECT template and define custom named sets using WITH keyword. XMLA connector features/limitations:
[Country].&[Canada]
.
To avoid this you may change AS cube configuration or exclude this dimension from the translation to MDX slicers.Data Source=http://localhost/olap/msmdpump.dll;Initial Catalog=Adventure Works DW Standard Edition;Connect Timeout=30;Note: only HTTP or HTTPS URL may be used as a Data Source. For SSAS HTTP access can be configured with IIS + msmdpump.dll. Important: only Anonymous or Basic authentication are supported by SeekTable XMLA OLAP connector.
@token_name
syntax.
For example:
SELECT @AXES FROM @CUBEwhere
@AXES
is a special pre-defined token, and @CUBE
is defined with an expression (see below).
@CUBE
may be defined as:
SelectFromCube("Adventure Works").Where("[Geography].[Geography].[Country]", Parameter["country"] )Expressions syntax is the same as for calculated fields but evaluation context is a different:
Function | Description |
---|---|
Parameter["parameter_name"] |
returns value of the report parameter with name="parameter_name".
If parameter name contains only letter/digits/'_' it can be referenced simply as parameter_name .
|
DIMENSIONS |
list of dimension names used in the MDX query. For example, you may check if concrete dimension is used with
DIMENSIONS.Contains("[Geography].[Geography].[Country]") .
|
SelectFromCube("olap_cube_name") |
helper function that composes sub-cube SELECT with slicers and returns special <MdxSelect> object.
Usage of this function is required if you want to translate pivot table filter into MDX slicers or
apply report parameters to filter by concrete dimension members.
|
<MdxSelect>.Where("dimension", value) |
adds a slicer to <MdxSelect> object for the specified dimension.
If value is null (parameter is not defined) slicer is not applied.
Function returns <MdxSelect> so you can call it in a chain like Where().Where() .
|
<MdxSelect>.Range("dimension", fromVal, toVal) |
adds a range slicer to <MdxSelect> object for the specified dimension.
Either fromVal or toVal may be null (if parameter is not defined).
Function returns <MdxSelect> so you can call it in a chain.
|
If everything is fine you should see a new cube dashboard with the list of available dimensions and measures.
In case of connection error you'll see an orange box with an error message; you may click on "Edit Configuration" and apply necessary changes to solve the issue.
Field
this is a dimension attribute specifier like:
[Product].[Category]Last element can be a dimension property to load like
[MEMBER_CAPTION]
, [MEMBER_NAME]
,
[MEMBER_VALUE]
, [MEMBER_UNIQUE_NAME]
; if property is not specified MEMBER_CAPTION
is loaded by default.
{0}
can be used).
Examples:
prefix {0} suffix
→ append custom prefix and/or suffix{0:yyyy-MM-dd}
→ format date (or timestamp) as 2017-05-25{0:MMM}
→ format month number (1-12) as a short month name (Jan, Feb etc){0:MMMM}
→ format month number (1-12) as a full month name (January, February etc){0:ddd}
→ format day-of-week number (0-6) as a short day-of-week name (Mon, Tue etc){0:dddd}
→ format day-of-week number (0-6) as a full day-of-week name (Monday, Tuesday etc).ALLMEMBERS
doesn't include (All)
(which is needed for totals) you may include it explicitely by
specifying {[Product].[Category].members, [Product].[Category].[All Products]}
.
Type=Expression: you can specify formula expression (1-st parameter) and dimension names for the arguments (2-nd, 3-rd etc parameter).
Cube Measure
: OLAP cube measure.Expression
: measure is defined with an expression-based formula field. This kind of calculation is performed on SeekTable side.Cube Measure
: first parameter is an OLAP cube measure specifier like [Measures].[Sales Amount]
.
For Type=Expression
: first parameter is a formula expression, and next parameters are names of measures used as arguments in the expression.
{0}
can be used).
Examples:
{0:$#.##}
→ format number as $10.25 (or empty if no value){0:0,.0#}k
→ show number in thousands with "k" suffix{0:0.#|k}
→ if number>1000 shorten it with "k" suffix{0:0,,.0#}M
→ show number in millions with "M" suffix{0:0.#|M}
→ if number>1000000 shorten it with "M" suffix{0:0.#|kMB}
→ shorten large number with appropriate "k"/"M"/"B" suffixReport parameter allows you to specify some filtering condition in MDX query by user-entered value. Also you can use parameters to affect MDX query generation.
String
: text-based value.Int32
: 32-bit integer (max value is 2,147,483,647).Int64
: 64-bit integer (max value is 9,223,372,036,854,775,807).Decimal
: Fixed-point number with max 28 significant digits. Decimal point is '.' character.DateTime
: datetime or date value (in this case datetime value has 0:00:00 time). Date value should be specified as string in YYYY-MM-DD format.Boolean
: accepts only 'True' or 'False' value.Parameter["param_name"]
.