Hello gurus,
I'm having difficulty substituting MUN value, coming from a prompt, as the MUN has spaces and names repeating in both levels and hieararchy of the MUN.
My aim is to convert a mun coming from a prompt, so that I can use a single prompt affecting two different facts by two different dimensions
Example converting
[DC Kunde eiendom og årsverk VC (analyse)].[Dim Kostnadsted].[Avdeling til kostnadsted via enhet].[Avdeling]->:[RO].[Dim Kostnadsted].[Avdeling til kostnadsted via enhet].[All].[S2]
to
[DC Kunde eiendom og årsverk VC (analyse)].[Dim Ressurs].[Avdeling til ressurs via ksted].[Ressurs avdeling]->:[RO].[Dim Ressurs].[Avdeling til ressurs via ksted].[All].[S2]
I tried substituting, but as "Avdeling" is both used in the hierarchy and level-name, the conversion gets wrong. The result using
set (
#join(';',
substitute ( 'Dim Kostnadsted', 'Dim Ressurs',
substitute ( 'Avdeling til kostnadsted via enhet', 'Avdeling til ressurs via ksted',
substitute ( 'Kostnadsted', 'Ressurs kostnadsted',
substitute ( 'Enhet', 'Ressurs enhet',
substitute ( 'Avdeling', 'Ressurs avdeling',
substitute ( 'Dim Kostnadsted', 'Dim Ressurs',
substitute ( 'Avdeling til kostnadsted via enhet', 'Avdeling til ressurs via ksted',
split(';',
promptmany( 'P_Kostnadssted', 'memberuniquename',
'[DC Kunde eiendom og årsverk VC (analyse)].[Dim Kostnadsted].[Avdeling til kostnadsted via enhet].[(All)]->:[RO].[Dim Kostnadsted].[Avdeling til kostnadsted via enhet].[All]'
)
)
)
)
)
)
)
)
)
)
#
)
is
[DC Kunde eiendom og årsverk VC (analyse)].[Dim Ressurs].[Ressurs avdeling til ressurs via ksted].[Avdeling]->:[RO].[Dim Ressurs kostnadsted].[Avdeling til ressurs via ksted].[All].[S2]
Any suggestion on how to solve this?
Im on 10.2.1, with Dynamic Cubes as source
(I might just be on the wrong track here since I've done next to nothing with dimensional reporting)
From what I gather, the issue comes from you trying to substitute a string A1 with B1, but A1 is also completely contained within A2. Can't you include the brackets in your substitutions, so that all your strings (A1, A2, ... An) are unique?
Thanks for the suggestion hespora.
You have understood the issue correctly. The suggestion. however, won't work, as it isn't possible to search for brackets, as they are system-specific characters.
Can definitely search for brackets in substitute macro. However, you have to preface the brackets in the search pattern (and *only* there) with an escape character \.
So
#substitute (
'\[FieldA\]',
'[FieldB]',
'[Source].[Path].[FieldA]'
)#
will correctly turn [Source].[Path].[FieldA] into [Source].[Path].[FieldB]
Thank you!
Using the escape character, worked solving the issue.
Thank you for taking out time to answer me!
Wonderful; glad I could help! :)