Sam is an Avid Commentator
1 published comment for 2022
User bio
404 bio not found
Member since Sep 2, 2016
Replies:

Hi Colin,

It's been a while since I used them, but I think POST /Info/Filters returns some information on dimensions/hierarchies/levels, and POST /Info/FilterMembers returns information on the members of a level. If you need metadata on properties specifically, I'm not sure if that is included. I don't think we have information on namespaces as part of the REST API for IRIS BI, but maybe someone has a suggestion of other ways to get that kind of information about an IRIS instance over REST. Anyway, I'd recommend taking a look at those two /Info services, and possibly opening a WRC issue if you find that they don't provide what you need.

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

Hi Jenna,

Is something like this what you have in mind?

SAMPLES>set relatedFilters("[DateOfSale].[Actual].[YearSold]") = "&[2021]"
 
SAMPLES>set sc = ##class(%DeepSee.Utils).%GetDimensionMembers("HOLEFOODS","[DateOfSale].[Actual].[MonthSold]","filter",.members,,.memberClass,.relatedFilters)
 
SAMPLES>zwrite members                                                         
members(1)=$lb("[NOW]","NOW","NOW","NOW")
members(2)=$lb("[Jan-2021]","Jan-2021","202101","202101","")
members(3)=$lb("[Feb-2021]","Feb-2021","202102","202102","")
members(4)=$lb("[Mar-2021]","Mar-2021","202103","202103","")
members(5)=$lb("[Apr-2021]","Apr-2021","202104","202104","")
members(6)=$lb("[May-2021]","May-2021","202105","202105","")
members(7)=$lb("[Jun-2021]","Jun-2021","202106","202106","")
members(8)=$lb("[Jul-2021]","Jul-2021","202107","202107","")
members(9)=$lb("[Aug-2021]","Aug-2021","202108","202108","")
members(10)=$lb("[Sep-2021]","Sep-2021","202109","202109","")
members(11)=$lb("[Oct-2021]","Oct-2021","202110","202110","")
members(12)=$lb("[Nov-2021]","Nov-2021","202111","202111","")
members(13)=$lb("[Dec-2021]","Dec-2021","202112","202112","")

I'll admit I'm not completely sure why the NOW member is coming back in this case, since there shouldn't be any data with a year of 2021 and a month of NOW, but other than that I think this may be what you were describing.

Open Exchange applications:
Followers:
Following:
Sam has not followed anybody yet.
Global Masters badges: