MDX Statement with IIF in Apliqo

Apliqo UX
,
1

Hi,

I want to create a dynamic subset for my Sicil(personal list) dimension. I have a personal info cube which contains department info for each personal. In my cube there is no department dimension. But, i add it in apliqo view. And basically, i just want to list my Sicil(personal list) dimension according to my department selection.

I tried this MDX first;

{TM1SubsetToSet( [Sicil], 
    IIF(
        "$<<IK_Planlama.Departman.Departman>>" = "Tum Birimler",
        "Toplam Altı N Seviye",
        "UX Personel"
    )
)}

However it doesn’t work. Because i used “$<<>>” phrases in “UX Personel” subset.

Then i tried this MDX;

{
    IIF(
        "$<<IK_Planlama.Departman.Departman>>"= "Tum Birimler",
        {TM1SubsetToSet( [Sicil], "Toplam Altı N Seviye")},
        {FILTER( 
            {TM1SUBSETALL( [Sicil] )}, 
            [IK Personel Bilgileri].([Yil].[$<<IK_Planlama.Yil.Yil>>],[Ay].[$<<IK_Planlama.Ay.Ay>>],[Versiyon].[$<<IK_Planlama.Versiyon.Versiyon>>],[Personel Bilgileri m].[Departman Final Kod],[Sicil].CurrentMember) = "$<<IK_Planlama.Departman.Departman>>" 
        )}
    )
}

But it doesn’t work too. I’m not sure, if i used IIF function properly.

Am i missing something or is there any work around solution for this case?

Thanks in advance.

2

Hi @acandan

No, there’s nothing wrong with doing this at all. This is what the setting service substitution is designed to do. It will substitute the evaluated context value into the MDX as a literal string. So if you are using it as a value to test then it should be in single or double quotes, and if using as part of a dimension, hierarchy or member name then in suqare brackets without quotes.

I hadn’t uded IIF like that before to alternate between different sets based on the context of another menber but I tried out your first example with some standard dimensions and as expected it works just fine.

I suspect that the problem is the value being substituted isn’t what you think it is becasue you have a display attribute selected on the Departman dimension. When UX is determining the value of a substitution variable it tries to be a little bit “smart”. If the field is a title or info text, then the display name from the filter is used without neeing to reference an attribute in the lookup. However, if used in MDX then UX assumes that you want the principal name not the display name. So I suspect in the Departman dimension the principal name of the element isn’t “Tum Birimler” and this is an alias??

If this guess is correct then you just need to use the
$<<instance.dimension.hierarchy::attribute>>
syntax in the MDX to reference the display name. E.g. your first MDX should look like this …

{TM1SubsetToSet( [Sicil], 
    IIF(
        "$<<IK_Planlama.Departman.Departman::Caption>>" = "Tum Birimler",
        "Toplam Altı N Seviye",
        "UX Personel"
    )
)}
3

Thanks for quick reply.

I checked “Tum Birimler” and it is the principal name. Also in my mdx I use principal names for each element only. I think the source of the problem is someting else.

This is the final MDX that UX sends to TM1.

SELECT NON EMPTY {{TM1SubsetToSet( [Sicil], IIF( "ID" = "Tum Birimler", "Toplam Altı N Seviye", "UX Personel" ) )}} ON ROWS, 
{{TM1SubsetToSet( [Personel Bilgileri m].[Personel Bilgileri m], "Veri Girişi")}} ON COLUMNS 
FROM [IK Personel Bilgileri] 
WHERE ([Versiyon].[Versiyon].[Butce],[Yil].[Yil].[2020],[Ay].[Ay].[01])

The “UX Personel” subset has the mdx like this;

{ FILTER( {TM1SUBSETALL( [Sicil] )}, [IK Personel Bilgileri].([Yil].[$<<IK_Planlama.Yil.Yil>>],[Ay].[$<<IK_Planlama.Ay.Ay>>],[Versiyon].[$<<IK_Planlama.Versiyon.Versiyon>>],[Personel Bilgileri m].[Departman Final Kod],[Sicil].CurrentMember)= "$<<IK_Planlama.Departman.Departman>>" )}

UX only sends subset name to the TM1 and TM1 tries to build subset with the MDX above. This might be the source of problem i think. Because mdx phrases with “$<<>>” doesn’t work on TM1 as far as i know. Are there any work around solution for this case?

4

There’s nothing at all wrong with the first MDX.

SELECT NON EMPTY 
    {{TM1SubsetToSet( [Sicil], IIF( "ID" = "Tum Birimler", "Toplam Altı N Seviye", "UX Personel" ) )}} ON ROWS, 
    {{TM1SubsetToSet( [Personel Bilgileri m].[Personel Bilgileri m], "Veri Girişi")}} ON COLUMNS 
FROM 
    [IK Personel Bilgileri] 
WHERE 
    ([Versiyon].[Versiyon].[Butce],[Yil].[Yil].[2020],[Ay].[Ay].[01])

This will just give you a grid with “UX Personel” subset from Sicil dimension on rows and “Veri Girişi” subset from Personel Bilgileri m on columns.

Now I would have assumed that the “UX Personel” subset was static. If it’s dynamic that’s still going to work just fine. But are you saying you have set the “UX Personel” subset MDX EXACTLY as above??

{ FILTER( 
    {TM1SUBSETALL( [Sicil] )}, 
    [IK Personel Bilgileri].([Yil].[$<<IK_Planlama.Yil.Yil>>],[Ay].[$<<IK_Planlama.Ay.Ay>>],[Versiyon].[$<<IK_Planlama.Versiyon.Versiyon>>],[Personel Bilgileri m].[Departman Final Kod],[Sicil].CurrentMember)= "$<<IK_Planlama.Departman.Departman>>" 
)}

If so then of course this isn’t going to work! The TM1 server itself has no concept of the Apliqo UX setting service values. To the TM1 Server the MDX of a dynamic subset is just a literal string. The MDX actually needs to work.

So to make this work you need to do the variable substitution in the MDX field in Apliqo UX, and not use TM1SubsetToSet. Your 2nd example from your original post should work. The only thing you are doing wrong is testing a member against a string. You just need to test the Name property instead then I am pretty sure it will work just like you want.

e.g.

{FILTER( 
    {TM1SUBSETALL( [Sicil] )}, 
    [IK Personel Bilgileri].([Yil].[$<<IK_Planlama.Yil.Yil>>],[Ay].[$<<IK_Planlama.Ay.Ay>>],[Versiyon].[$<<IK_Planlama.Versiyon.Versiyon>>],[Personel Bilgileri m].[Departman Final Kod],[Sicil].CurrentMember.Name) = "$<<IK_Planlama.Departman.Departman>>" 
)}

Or
[Sicil].CurrentMember.Properties(“NAME”)
[Sicil].CurrentMember.Properties(“MEMBER_NAME”)

1 Like