Hi Jenna,

I think the %DeepSee.Utils:%GetMDXFromPivot() method may be helpful here. (The class reference is here, though I think the first line of the description is confusing.)

If the pivot is saved with the filters you want in effect (e.g. with the &[2023] member already selected), you can get the MDX and execute it directly by passing a result set and pExecute = 1 to %GetMDXFromPivot():

SAMPLES>set rs = ##class(%DeepSee.ResultSet).%New()
SAMPLES>set pivot = "Filter Examples/One Member Filter"
SAMPLES>set mdx = ##class(%DeepSee.Utils).%GetMDXFromPivot(pivot,.sc,1,,.rs)
SAMPLES>write sc, !, mdx
1
SELECT NON EMPTY NONEMPTYCROSSJOIN([GenD].[H1].[Gender].Members,[Measures].[%Count]) ON 0,NON EMPTY {[DiagD].[H1].[Diagnoses].Members,[AgeD].[All Patients]} ON 1 FROM [PATIENTS] %FILTER [AllerD].[H1].[Allergies].&[wheat]
SAMPLES>do rs.%Print()
                       Patient Count        Patient Count
1 None                             23                   16
2 asthma                            3                    4
3 diabetes                          1                    2
4 All Patients                     27                   21
 
If the pivot table is not saved with all the filters that you want, you can use %GetMDXFromPivot() just to get the MDX query text, then append a %FILTER clause to it (make sure to include a space between the original query and the %FILTER) and pass the modified text to a %DeepSee.ResultSet object to be prepared and executed:

SAMPLES>set pivot = "Chart Demos/Bar Chart"
SAMPLES>set mdx = ##class(%DeepSee.Utils).%GetMDXFromPivot(pivot,.sc)
SAMPLES>write sc, !, mdx
1
SELECT NON EMPTY [Measures].[Amount Sold] ON 0,NON EMPTY [DateOfSale].[Actual].[YearSold].Members ON 1 FROM [HOLEFOODS]
SAMPLES>set mdx = mdx_" %FILTER [Product].[P1].[Product Category].&[Cereal]"
SAMPLES>write mdx
SELECT NON EMPTY [Measures].[Amount Sold] ON 0,NON EMPTY [DateOfSale].[Actual].[YearSold].Members ON 1 FROM [HOLEFOODS] %FILTER [Product].[P1].[Product Category].&[Cereal]
SAMPLES>set rs = ##class(%DeepSee.ResultSet).%New()
SAMPLES>write rs.%PrepareMDX(mdx)
1
SAMPLES>write rs.%Execute()
1
SAMPLES>do rs.%Print()
                                   Revenue
1 2017                               $40.69
2 2018                               $59.65
3 2019                              $103.13
4 2020                               $40.70
5 2021                               $99.96
6 2022                               $68.35

There are a couple of ways you might be able to do this, but the simplest one is probably to use the %NOT MDX function. You can write a filter that excludes one member by appending .%NOT to the member spec:

%FILTER [Demographics].[H10].[Citizenship].&[USA].%NOT

If you want to write a filter that excludes multiple individual members of one or more levels, you can filter by a tuple of the members that you want to exclude. Append .%NOT to each of the member specs, and do not use %OR:

%FILTER ([Demographics].[H10].[Citizenship].&[USA].%NOT,[Demographics].[H10].[Citizenship].&[U.S.A].%NOT,[Demographics].[H10].[Citizenship].&[US].%NOT)

I'd like some clarification on the prompt "Using %MDX and %KPI instead of Subject Area in IRIS Analytics". I don't typically think of %MDX and %KPI as alternatives to creating/using a subject area in the way that the prompt implies - is the intention just to have an article that explains how to use %MDX and %KPI, or is there a specific use case that you are hoping it will explain?
edit: Doesn't look like I'm eligible to participate, but maybe someone else wants to write this...

Hi Evgenii,

If the list of branches will not change over time (or will change very infrequently, so that you can manually update your cube model when it does), you might consider manually specifying the members for the Branch level, as documented here.

If you do this, keep in mind that the Analyzer adds the NON EMPTY keyword to many MDX queries by default, which will prevent members with no data from being shown in the resulting pivot table. You may need to remove this keyword from MDX queries where you want the members with no data to show up.

Hi Evgeny,

Please take a look at the documentation on custom actions. You will need to create a KPI class and define the actions you want to make available in it. You can then add widget controls that correspond to these actions. For a real-world example that includes an action to rebuild a cube, take a look at this class from the CubeEventMonitor.

I think you know this, but to clarify for other readers - this allows developers to write any methods they would like and end-users to execute the defined methods at any time. It doesn't allow end-users to define and execute arbitrary methods of their own.