Order set in prolog tab in a view created with MDX is no the order running in datatab

Post Reply
jjramirez
Posts: 31
Joined: Fri Jan 13, 2017 12:33 pm
OLAP Product: IBM Planning Analytics/TM1
Version: 2.0.9.15
Excel Version: Excel 2019

Order set in prolog tab in a view created with MDX is no the order running in datatab

Post by jjramirez »

Hi everybody,

I created a view in the prolog tab using a MDX to filter and order a dimension from the source cube
and when I test the MDX in subset editor the elements are shown in the expected order even in the view created by the TI Process elements appears in the expected order but when process go to data tab elements are processed in ASC order...and not as the MDX.
sMDXGrua= '{ORDER( {Filter( { TM1FilterByLevel ( { TM1DrillDownMember ( { [Grua].[Gruas Muelle] },ALL,Recursive) } ,0 ) }
, [R.Operacion.Grua].([R_Version].[ ' | pVersion | ' ], [Periodo.E].[ ' | pPeriodo | ' ], [GPC.Empresa].[ ' | pEmpresa | ' ] ,[Acuerdo].[ ' | pAcuerdo | ' ] , [Grua].CurrentMember, [UVI].[ Total UVIs ],[Medida.Grua].[% Utilizacion Equipo] ) >0 )}
, [R.Operacion.Grua].([R_Version].[ ' | pVersion | ' ], [Periodo.E].[ ' | pPeriodo | ' ], [GPC.Empresa].[ ' | pEmpresa | ' ] ,[Acuerdo].[ ' | pAcuerdo | ' ] , [Grua].CurrentMember, [UVI].[ Total UVIs ],[Medida.Grua].[% Utilizacion Equipo] ),BDESC )}' ;
In pictures attached you can see
1) the order that subset editor return and the order: (26,28,29,27,25...)
2) the order that view created by TI return: (26,28,29,27,25...)
3) the order processed in datatab: (23,24,25,26)

Thank you veru much for any help!
Attachments
results in subset editor.png
results in subset editor.png (56.81 KiB) Viewed 873 times
view created by TI Process.png
view created by TI Process.png (40.1 KiB) Viewed 873 times
final view with TI Process results.png
final view with TI Process results.png (27.43 KiB) Viewed 873 times
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Order set in prolog tab in a view created with MDX is no the order running in datatab

Post by gtonkin »

Check out this thread
jjramirez
Posts: 31
Joined: Fri Jan 13, 2017 12:33 pm
OLAP Product: IBM Planning Analytics/TM1
Version: 2.0.9.15
Excel Version: Excel 2019

Re: Order set in prolog tab in a view created with MDX is no the order running in datatab

Post by jjramirez »

Hi gtonkin,

thanks for your reference. I found a solution creating the subset and looping in the prolog tab instead of using the source view.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Order set in prolog tab in a view created with MDX is no the order running in datatab

Post by Wim Gielis »

Indeed looping the alternative, unless you need to go through a lot of sparsity or largish volumes or rules-calculated cells.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Order set in prolog tab in a view created with MDX is no the order running in datatab

Post by Steve Rowe »

Is this the same thing as view dimension order though?

OP seems to be talking about element order within the a set being different depending on how the view is accessed?

Since all the other dimensions are pick of single members I can't see why dimension order of view would be impact the set order?
Technical Director
www.infocat.co.uk
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Order set in prolog tab in a view created with MDX is no the order running in datatab

Post by gtonkin »

Looks like I jumped the gun here and assumed the current MDX view discussion.

Does look curious - What is set in prolog for the DataSourceNameForServer etc? Need to see what is happening in prolog to build and reference the set.
Post Reply