Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

Kyvos supports the SCOPE function and Default_Member MDX functions.  

• SCOPE MDX function: The SCOPE MDX function writes the scripts on the semantic model design without rebuilding a semantic model.
  For more information about the SCOPE function and its syntax, refer to Microsoft Documentation.   

• DEFAULT_MEMBER MDX function: This function is used to define a default member on a hierarchy. The default member evaluates reports based on the given member of the level instead of all members of each level in the respective hierarchy.

SCOPE MDX function

Before using the SCOPE function, consider the following:

• The SCOPE script does not apply to the SQL interface.

• Nested SCOPE statements are not supported.

• Scope context is not applicable for parent level, subtotals, and total.
  For example,
  SCOPE( [Geo].[Hierarchy].[City].&[Indore] , [Measures].[Profit] );

  This = [Measures].[Profit] * 1.1;

  END SCOPE;

To create a script using the SCOPE function, perform the following steps.

1. Create or edit an existing semantic model.

2. On the Design tab, click the Actions menu (…), and then select Scripts. The Script dialog box is displayed.

3. In the right pane, click the Functions option and type SCOPE in the Search textbox. The SCOPE function is displayed.

4. To use the SCOPE template, double-click the SCOPE. The template is displayed in the left pane. 

5. Start typing an expression in the editor section of the dialog box.

6. As you enter text, suggested subcube expressions, semantic models, dimensions, members, and so on are displayed. You can also see a description of the selected function.
   To view a list of functions along with the detailed description, navigate to the Functions tab and select a function from the list.

7. You can also perform the following:

   1. Add, update, get, or delete the script using the REST APIs on the semantic model design. See Kyvos REST APIs Guide for more details. 

   2. Modify or remove the script. 

      1. In the editor section, modify the script as needed.

      2. To delete the script, you need to first select the script and press the Delete or Backspace key. 

   3. Enable or disable the script.
      By default, the SCOPE script is enabled. On disabling the script, the SCOPE calculations do not apply while querying. 

      1. To disable the script, click the Properties option and then select the Disable checkbox. The script is disabled for that semantic model. Click the i icon to learn more. 

8. Click Validate to validate your criteria. 
   If you have made changes and don’t want to keep them, click Cancel.

9. Click Save.

DEFAULT_MEMBER MDX function 

Kyvos supports the Default_Member MDX function, which enables you to evaluate reports by considering the specified member instead of all members of the hierarchy.

For example, if you have dragged the [Measure].[Sum Sales] measure from the Order Date semantic model onto the visualization; you will see the aggregated value. This value considers all members of each hierarchy from every dimension related to this measure. However, when the default member is set for a hierarchy, instead of considering every member of that hierarchy, it will only consider the value of the Default Member.

To create a script using the Default_Members function, perform the following steps.

1. Create or edit an existing semantic model.

2. On the Design tab, click the Actions menu (…), and then select Scripts. The Default_Member dialog box is displayed.

3. In the right pane, click the Functions option and type Default_Memeber in the Search textbox. The Default_Memeber function is displayed.
   
   

4. To use the Default_Memeber  template, double-click the Default_Memeber. The template is displayed in the left pane. 

5. Start typing an expression in the editor section of the dialog box.

6. As you enter text, suggested subcube expressions, semantic model, dimensions, members, and so on are displayed. You can also see a description of the selected function.
   To view a list of functions along with the detailed description, navigate to the Functions tab and select a function from the list.
   You can also define Default member in the script using supported member functions. See the supported member function in the Supported MDX Functions.
   

7. You can also perform the following:

   1. Add, update, get, or delete the script using the REST APIs on the semantic model design. See Kyvos REST APIs Guide for more details. 

   2. Modify or remove the script. 

      1. In the editor section, modify the script as needed.

      2. To delete the script, you need to first select the script and press the Delete or Backspace key. 

   3. Enable or disable the script.
      By default, the Default_Member script is enabled. On disabling the script, the Default_Memeber calculations do not apply while querying. 

      1. To disable the script, click the Properties option, and then select the Disable checkbox. The script is disabled for that semantic model. Click the i icon to learn more.  
         

8. Click Validate to validate your criteria. 
   If you have made changes and don’t want to keep them, click Cancel.

9. Click Save.

Connecting to Excel 

When you connect to Excel and drag the default member onto the filter shelf, the data corresponding to the default member is automatically filtered. 

For example, 

Alter semantic model [AdventureWorksMF]
Update Dimension [Customer].[Geography],
default_Member = [Customer].[Geography].[Country].&[Canada].firstchild;

If you connect to Excel and drag 'Country' onto the filter shelf, 'Alberta' will be automatically selected as the first child of Canada, as defined in the default member script.