Thanks a lot for the code snippet! I solved the parsing in a similar way :-) I am just curious why the order changes but maybe Svetlanas link explains it.
Original Message:
Sent: Mon June 12, 2023 02:58 AM
From: David Pretorius
Subject: Drill to SQL Server with hierarchies
Hey Mario,
Long time no see.
While I didnt have your issue of having different order here is some parsing code that may be of use. This one checks to make sure you drilled on a leaf element and I suppose adding a dimix() check could make it indifferent to the order in your string (?)
sMember = dimensionElementPrincipalName('Counterparty', counterparty);
sDim = 'Counterparty';
if(scan('^', sMember )>0);
z =sMember | '^';
While (SCAN('^', z)>0 );
d = subst(z,1,scan('^', z)-1);
zz = d | ':';
hier = subst(d,1, scan(':',d));
member = subst(d, scan(':',d)+1, 1000);
if(ellev(sDim, member) =0);
sMember = member;
endif;
z = subst(z, scan('^', z)+1, long(z) );
end;
endif;
------------------------------
David Pretorius
Original Message:
Sent: Mon June 05, 2023 03:51 PM
From: Mario Hasler
Subject: Drill to SQL Server with hierarchies
Hi Community!
We have a cube containing an organisation dimension which has a hierarchy called "TechnologyArea". Meaning the organisation dimension has the default hierarchy and a hierarchy called "TechnologyArea".
From a cube view in PAW which contains both the default hierarchy and the hierarchy "TechnologyArea", we want to drill to a SQL server view. We have a drill rule and a drill process in place.
It seems like the string which is returned for the organisation dimension changes. The order of the default hierarchy and hierarchy "TechnologyArea" switches places sometimes.
Sometimes the string looks like this:
^ELTEK_R01^TechnologyArea:Service
-> Default hierarchy first, then hierarchy "TechnologyArea".
In other cases it is the other way round:
^TechnologyArea:Service^ELTEK_D05
-> Hierarchy "TechnologyArea" first, then default hierarchy.
In order to make the drill work we need to parse this string in the drill process. The different orders in the string can of course be handled in the drill process but it would be nice to know what determines the order. Does someone know?
Thanks!
Mario
------------------------------
Mario Hasler
------------------------------