How to execute a. pivot table and get results into %DeepSee.ResultSet passing. in filters

Hi!

May be this example could help you:

ClassMethod ExecTestQuery(pParams)
{
	Set mdx = 
		"WITH "_
		"	%PARM pSelectedDim as 'value:Trimestre' "_
		"	%PARM pSelectedYear as 'value:NOW' "_
		"SELECT "_
		"	[Measures].[QtdAtendimento] ON 0, "_
		"	NON EMPTY [DataD].[H1].@pSelectedDim.Members ON 1 "_
		"FROM [ARQORDEMSERVICO] "_
		"%FILTER [DATAD].[H1].[ANO].&[@pSelectedYear]"
	Set rs = ##class(%DeepSee.ResultSet).%New()
	Try {
		$$$TOE(st, rs.%PrepareMDX(mdx))
		Write "Parameters: "
		Write:($D(pParams) = 0) "(default)"
		Write !
		ZW pParams
		$$$TOE(st, rs.%Execute(.pParams))
		Do rs.%Print()
	} Catch(e) {
		Write e.DisplaytString(),!
	}
}

ClassMethod TestDeepSeeResultSet()
{
	Write "Test 1", !
	Do ..ExecTestQuery()
	
	Write "------",!
	Write "Test 2", !
	Set params("pSelectedDim") = "MesAno"
	Set params("pSelectedYear") = "2022"
	Do ..ExecTestQuery(.params)
}

Do ##class(teste.NewClass1).TestDeepSeeResultSet()
Test 1
Parameters: (default)
                           Qtd Atendimento
Q1 2023                                          4
------
Test 2
Parameters: 
pParams("pSelectedDim")="MesAno"
pParams("pSelectedYear")=2022
                           Qtd Atendimento
1 Ago-2022                                         15
2 Set-2022                                         30
3 Out-2022                                         25
4 Nov-2022                                          9
5 Dez-2022                                          5

Some resources that may be useful:

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

https://docs.intersystems.com/ens201815/csp/docbook/Doc.View.cls?KEY=D2R...

HTH,

José

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