Written by

STAFF

Question Jenna Makin · Jan 10, 2023

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

#InterSystems IRIS BI (DeepSee) #Analytics

I have a pvot table defined. that generates a set of data. I want. to. execute the pivot table programatically which I believe involves using %DeepSee.ResultSet. Does someone have an example showing how to apply filters to the results?

For. example

[DateTime of Filing].[Date].[Date Filed Year] = &[2023]

Thanks

Product version: IRIS 2022.2

Discussion (2)3

Add reply

Comments

José Pereira · Jan 13, 2023

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 20234
------
Test 2
Parameters: 
pParams("pSelectedDim")="MesAno"
pParams("pSelectedYear")=2022
                           Qtd Atendimento
1 Ago-2022152Set-2022303 Out-2022254 Nov-202295 Dez-20225

Some resources that may be useful:

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

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

HTH,

José

0 0

Sam Duncan · Jan 17, 2023

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

0 0