IIF statement in column dimension based on row dimension

Apliqo UX
1

Hi All,

I want to create a report where the number will change based on row member.
For example, if row member display region A, “Revenue & Other Income” GL will be used ==> display Revenue & Other Income number otherwise display “Revenue” number in column

I have written the code below, however it doesn’t seem to work. Region A still display “revenue” number instead of “Revenue & Other Income”:

SELECT NON EMPTY { [Region].[Region].[Consolidated Region].Children
} ON ROWS,

{
{[Period].[Peiod].[Jun2024]}
*

{ [GL Name].[ GL Name],[Gross Margin],
STRTOMEMBER(“[GL Name].[ GL Name].[” +
IIF( [Region].[Region].Currentmember.Name
<> “Region A”, “Revenue”, “Revenue & Other Income”)

  • “]”
    )}
    } ON COLUMNS

FROM [Cube 1]

WHERE ( [Currency].[Currency].[AUD])

However, if I put the Region dimension in Global Filters, it will work.

Great if you can help to shed lights on this issue!
Thank you

2

Hi @New ,

If I understood correctly, you are trying to mix the 2 Accounts into 1 column, this is only possible if you create a new Calculated member using the WITH MEMBER before the Select statement.
it would be something like this:

WITH MEMBER [GL Name].[GL Name].[Revenue Calc] AS
 IIF(  [Region].[Region].CurrentMember.Name = "Region A"
    , [GL Name].[GL Name].[Revenue & Other Income]
    , [GL Name].[GL Name].[Revenue])

SELECT NON EMPTY { [Region].[Region].[Consolidated Region].Children
} ON ROWS,

{
{[Period].[Peiod].[Jun2024]}
*
{ [GL Name].[ GL Name].[Gross Margin], [GL Name].[GL Name].[Revenue Calc]}
} ON COLUMNS
FROM [Cube 1]

WHERE ( [Currency].[Currency].[AUD])

Hope this helps!

3

Thank you very much!

1 Like