How do i find out, what dimension elements are shown ...

[Roger_Lewin]

...in a view?


Hi there,

how can i find out, what elements are show in a dimension column in a view? What i've done so far, was to create a view via TI with Zero Suppression turned on. I now want to know what members are show in the dimension column (after zero suppression) to build a new subset of those shown.

How can i do this?

Thanks in advance
Roger

[Roger_Lewin]

Okay...i found a workaround, BUT this crashes the tm1sd.exe - any idea why? Nothing special in the logs...
--------------------------
iSubKostenstellen = SubsetGetSize('Kostenstellen', 'N-Elemente');
iSubKostenarten = SubsetGetSize('Kostenarten', 'Abgrenzungskonten');

iSubKosten_Zaehler =1;
iSubKonten_Zaehler =1;
sSubKosten_Name = '';
sSubKonten_Name = '';

DIMENSIONELEMENTINSERT('Kostenstellen', '', 'Abgrenzungen', 'C');
DimensionElementComponentAdd ('Kostenstellen', 'Nach Sparten', 'Abgrenzungen', 1);

while( iSubKosten_Zaehler <= iSubKostenstellen);

sSubKosten_Name = SUBNM('Kostenstellen', 'N-Elemente', iSubKosten_Zaehler);

while( iSubKonten_Zaehler <= iSubKostenarten);
sSubKonten_Name = SUBNM('Kostenarten', 'Abgrenzungskonten', iSubKonten_Zaehler);
#ASCIIOutput('C:\dbg.txt', NumberToString(CellGetN('Kosten', 'Ist', 'Alle Jahre', 'Jahr gesamt', sSubKosten_Name, sSubKonten_Name)));
if( CellGetN('Kosten', 'Ist', 'Alle Jahre', 'Jahr gesamt', sSubKosten_Name, sSubKonten_Name)<>0);
DimensionElementComponentAdd ('Kostenstellen', 'Abgrenzungen', sSubKosten_Name, 1);
endif;

iSubKonten_Zaehler = iSubKonten_Zaehler +1;
end;

iSubKosten_Zaehler = iSubKosten_Zaehler +1;
end;
--------------------------

[David Usherwood]

You're kind of doing what I would have thought of (though my German is pretty rusty ). Why not just read the view, cell by cell, as a data source, than add to a (pre cleared) subset all elements you find in the dimension you care about? That should work.

[Roger_Lewin]

Oh, no need to speak german to me, i hope, that my english is not too bad... my solution from above did crash the tm1sd.exe - any ideas why? I'm using 9.4.0.568

What i've done now, is:

a) create a second process, which uses the created view as a data source
b) included a call to this second process in the process, which builds the view in the epilog
c) checked, if 'Value <>0' in the metadata tab and if yes, add the found element ('Kostenstelle' aka Cost Center) below the the element "Abgrenzungen" (aka accruals and deferrals) in the Cost Center dimension
d) BUT: This seems not to work. Below the Node "Abgrenzungen" no element is shown. Any hints?

Metadata tab:
DIMENSIONELEMENTINSERT('Kostenstellen', '', 'Abgrenzungen', 'C');
DimensionElementComponentAdd ('Kostenstellen', 'Nach Sparten', 'Abgrenzungen', 1);

If (Wert <>0);
DimensionElementComponentAdd ('Kostenstellen', 'Abgrenzungen', Kostenstellen, 1);
endif;

[Roger_Lewin]

Well, i reconsidered completely and found a different solution thanks to a hint given by a colleague

why build a view and sift through it, when i can filter the dimension directly via MDX?

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Kostenstellen] )}, 0)},[Kosten].([Jahre].[Alle Jahre],[Monate].[Jahr gesamt],[Datenarten].[Ist],[Kostenarten].[Abgrenzungskonten]) <> 0 )}

this MDX worked fine