Supported Excel Functionality
Prerequisite
Below functionality requires Excel 2010 and above
Data Connection
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| SSAS Entrance - Use username and password | Support | Data - Get Data - From Database - SSAS | You need to check Save password in file and Always attempt to use file to refresh during the connection process, otherwise you may need to enter authentication information frequently to maintain the connection |
| Existing Connection | Support | Data | There are two ways to connect to the existing connection. Selecting Connections in this workbook will make the newly created PivotTable use the same connection. Selecting Connection files in this computer will create a new copy of the connection, new PivotTable will use the new copy connection. In addition, when the data is refreshed, the same connected PivotTable will be updated |
| Insert PivotTable | Support | Insert - PivotTable -Choose Connection | You can choose a connection entry other than SSAS, such as XMLA Connect |
| Import Offline Cube File | Not Support | Drag and drop .cub file to Excel |
Excel support extracts data from multidimensional servers and generates .cub files for offline analysis |
Analyze in PivotTable
Sync Semantic Model
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| Measure group | Support | PivotTable | By default, each model corresponds to a group, and the calculated measure is a single measure group |
| Measure Folder | Support | PivotTable | |
| Dimension Table | Support | PivotTable | Only one public dimension table will be displayed |
| Named Set | Support | PivotTable | |
| Namedset Folder | Support | PivotTable | The Named Set is displayed under the Set folder by default |
| Hierarchy | Support | PivotTable | |
| Normal Dimension | Support | PivotTable | |
| Dimension Folder | Support | PivotTable |
Field Select & Search
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| Filters Area | Support | PivotTable | Support filtering normal dimension and hierarchy dimension |
| Rows Area | Support | PivotTable | Support choosing dimension, hierarchy and namedset |
| Columns Area | Support | PivotTable | Support choosing dimension, hierarchy and namedset. if user check the time dimension, it is placed on rows by default. |
| Values Area | Support | PivotTable | When there are more than two measures, values dimension will be generated by default on columns |
| Field Search | Support | PivotTable | Support searching measure, dimension and namedset |
Filter Function
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| Filter Area | Support | Support to filter dimension and hierarchy; support single and multiple selection; support fuzzy select | |
| Columns & Rows | Support | Support to filter dimension and hierarchy; support single and multiple selection; support fuzzy select | |
| Keep/Hide selected items | Support | PivotTable right-click menu - Filter | Belong to list filter and cannot coexist with value filter, label filter |
| TopN | Support | PivotTable right-click menu - Filter | Belong to value filter; multiple dimensions filter TopN cannot take effect; Hierarchy multiple Level filter TopN may lead to inaccurate data |
| Value Filters | Support | PivotTable right-click menu - Filter | Need to enable sub-query by configuring insight.mdx.mondrian.mdprop.mdx.subqueries, check Basic Configurations for details; value filter and label filter cannot be performed at the same time; for single character (not) equals, (not) greater than/less query results are incorrect; After filtering the hierarchy , it is not possible to expand; no total can be displayed after filtering |
| Lable Filters | Support | PivotTable right-click menu - Filter | Need to enable sub-query by configuring insight.mdx.mondrian.mdprop.mdx.subqueries, check Basic Configurations for details; value filter and label filter cannot take effect at the same time; for single character (not) equals, (not) greater than/less query results are incorrect; After filtering the hierarchy , it is not possible to expand; no total can be displayed after filtering |
| Slicer Filter | Support | Analyze | Belong to list filter |
| Timeline Filter | Not support | Analyze | Belong to list filter |
| Excel copied fiel d filter | Support | Copy Field - Filter | Copy a filter in PivotTable to a blank area and filter the field |
Sort Function
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| Datasource Order | Support | PivotTable right-click menu - Sort | Sort by sql result by default; Use Key Column and name column to change the sorting |
| Manual Order | Support | PivotTable right-click menu - Move | Custom sorting through move function |
| Field Order | Support | PivotTable right-click menu - Sort | Sort according to the selected field, other sorting methods cannot coexist |
Layout & Display
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| Format Cells | Support | PivotTable right-click menu | The string format such as currency sign can be set by editing the dataset, or it can be set in Excel |
| Show Totals | Support | Design - Subtotals | Columns with small cardinality placed on the left has better performance |
| Show Subtotals | Support | Design - Subtotals | Columns with small cardinality placed on the left has better performance |
| Include filtered items In Totals | Not support | Design - Subtotals | Support totals do not change according to the filter items |
Show +/- buttons |
Not support | Analyze - Show | +/- buttons will be generated in hierarchy by default; If there are multiple common dimensions, the +/- button will not be generated currently |
| Show items with no data on rows | Not support | PivotTable right-click menu - PivotTable Option - Display | The row will be hidden when all measures on are empty |
| Show items with no data on columns | Not support | PivotTable right-click menu - PivotTable Option - Display | The column will be hidden when all measures on are empty |
| Show field headers | Support | Analyze - Show | |
| Show field list | Support | Analyze - Show | |
| Show Properties in tooltips | Not support | PivotTable right-click menu | |
| Show Properties in reports | Not support | PivotTable right-click menu | |
| Insert blank line after each Item | Support | Design - Blank Rows | |
| Expand | Support | PivotTable right-click menu | Support expand one and expand all |
| Collapse | Support | PivotTable right-click menu | Support collapse one and collapse all |
| Drill down | Not support | PivotTable right-click menu | Currently, you can only drill down two levels at most, and you can drill down multiple levels after opening the subquery |
| Drill up | Not support | PivotTable right-click menu | Function is not perfect, see above |
| Repeat all Item lables | Support | Design - Report Layout | |
| Show in compact form | Support | Design - Report Layout | |
| Show in outline form | Support | Design - Report Layout | |
| Show in tabular form | Support | Design - Report Layout |
Refresh Data
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| Defer Layout Update | Support | PivotTable | Only update data, exclude the structure. If the dataset structure is changed (dimensions/measures are missing), an error will be reported |
| PivotTable right click refresh | Support | PivotTable right click | Both update the data and structure. If the structure changes, the report will be adaptively changed, related dimensions/measures may be removed, or the entire report may be cleared |
| Refresh | Support | Data | Same as above, will update all reports of the same connection |
| Refresh All | Support | Data | Will update all connected reports |
Cube Formulas
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| CubeMember | Support | Formula | Returns a member or tuple from the cube. Use to validate that the member or tuple exists in the cube |
| CubeValue | Support | Formula | Returns an aggregated value from the cube |
| CubeSet | Not support | Formula | Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Excel |
| CubeSetCount | Not support | Formula | Returns the number of items in a set |
| CubeKPIMember | Not support | Formula | Returns a key performance indicator (KPI) property and displays the KPI name in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, that is used to monitor an organization's performance |
| CubeMemberProperty | Not support | Formula | Returns the value of a member property from the cube. Use to validate that a member name exists within the cube and to return the specified property for this member |
| CubeRankedMember | Support | Formula | Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or the top 10 students |
Advanced Functions
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| ESC cancel query | Support | Type ESC during the query | There may be problems in a cluster env |
| Show Value As | Support | PivotTable right-click menu | Similar to table calculation in Tableau |
| Group/Ungroup | Support | PivotTable right-click menu | Common in head-long tail scenario analysis, long tail is classified as other |
| Drillthrough | Not support | PivotTable right-click menu | Double-click the measure cell to drill into details |
| Create Set Bases on Row Items | Not support | Analyze - Filed, Items & Sets | Can be defined in MDX for Kylin data set interface |
| Create Set Bases on MDX | Not support | Analyze - Filed, Items & Sets | Can be defined in MDX for Kylin data set interface |
| Create Calculate Measure | Not support | Analyze - OLAP Tools | Can be defined in MDX for Kylin data set interface |
| Create Calculate Member | Not support | Analyze - OLAP Tools | Can be defined in MDX for Kylin data set interface |
| What-If Analysis | Not support | Analyze - OLAP Tools | Need to have data write-back function |
| Offline OLAP | Not support | Analyze - OLAP Tools |
Report Making
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| Inset Chart | Support | Insert - Charts | Various types of charts can be selected for data visualization |
| Filter linkage | Support | Right click Slicer - Report Connections | After the filter setting is associated with multiple PivotTable, linkage analysis can be performed, which is often used in dashboards |
Report Sharing
| Function | Support Status | Operation Path | Comment |
|---|---|---|---|
| Change Data Source | Support | Analyze | This operation will update the data and structure. If the structure changes, the report will be adaptively changed, related dimensions/measures may be removed, or the entire report may be cleared |
| Sharing without change data source | Support | When creating the report, do not check Save password in file and Always try to refresh using file | To connect from SSAS Entrance - Use Windows Authentication, it needs to be used with MDX for Kylin Gateway |