Crossjoined sets on rows

Apliqo UX
1

Apologies in advance if this is a stupid question…

QUESTION IN BRIEF
Any documentation or advice on how to realign columns when you use Crossjoined MDX in your row definition?

CONTEXT OF QUESTION
I have a requirement from users to create a Trial Balance report. As such, users want to crossjoin measures and accounts on rows.

I first wrote some full cube MDX in Arc that returns values perfectly and got that approved by the user. (Appendix A below).

I then took the row set from my cube MDX and pasted it into the MDX textbox of the FIN OCoA dimension in ApliqoUX (Appendix B below).

When I updated Apliqo with this new CrossJoined MDX set on the account dimension, I get a grid returned with the values I am after. I can change the value of GL Measure in the Global Filters and data does not change. I can move the GL Measure box to Global Settings and hide it and my numbers are perfect.

HOWEVER… Apliqo rendering now has issues that I don’t know how to resolve. I have effectively updated my row MDX to be two columns wide instead of one. (As it’s now a crossjoin of two dimensions). Apliqo will display only elements from whichever dimension I list first in my crossjoin (in this case accounts). It will then start displaying column data perfectly lined up with column headers from column 2 onwards. At the end however, it will repeat the last column’s data without formatting or a column header. If we do an extract of this view, we see that second dimension in the row definition is actually in column two contents. And all the column data is offset one column to the right from the column heading.

Is there something in the JSON I can change to let Apliqo know I have row definition that is two columns wide and not just one, so that I don’t have these column misalignments? Or is there a better method for using crossjoins in your row MDX?

Thanks in advance for any help!

APPENDIX A - CUBE MDX

SELECT 
  { [T Year-Month].[Total Year 2021 Months].Children } ON COLUMNS,
  { 
    {
      {TM1SubsetToSet( [FIN OCoA].[FIN OCoA], "Trial Balance - Balance Sheet")} *
      {[FIN General Ledger Measure].[CB]}
    },
    {
      {TM1SubsetToSet( [FIN OCoA].[FIN OCoA], "Trial Balance - P&L")} *
      {[FIN General Ledger Measure].[MOV]}
    }    
  } ON ROWS
FROM [FIN General Ledger]
WHERE (
  [FIN Version].[Actual],
  [FIN Currency].[LOCAL],
  [FIN BSEG1].[ALL BU],
  [FIN BSEG2].[All Accounts],
  [FIN BSEG3].[Total Location],
  [FIN BSEG4].[Total FIN BSEG4],
  [Scale].[Base Units],
  [Time Analysis].[Base Period],
  [FIN Consolidation Value Measure].[TB Load],
  [FIN Company].[Total FIN Company]
)

APPENDIX B - DIMENSION MDX POSTED INTO APLIQO FIN OCoA DIMENSION DEFINITION

  { 
    {
      {TM1SubsetToSet( [FIN OCoA].[FIN OCoA], "Trial Balance - Balance Sheet")} *
      {[FIN General Ledger Measure].[CB]}
    },
    {
      {TM1SubsetToSet( [FIN OCoA].[FIN OCoA], "Trial Balance - P&L")} *
      {[FIN General Ledger Measure].[MOV]}
    }    
  }
2

Hi @Epistemophile ,

If I understood correctly, it should be pretty simple, you’re just missing to add the GL measure to the rows as well in UX cube settings.

When I have to do crossjoin on rows and columns in UX, I normally add all the referring dimensions I’m using in the MDX on the rows/columns. In the First dim you add your MDX code, in the second onwards, you change to type MDX, but leave the code blank.

In your example:

  • Drag and drop 1st the FIN OCoA dim and 2nd the FIN GL measure dimension to the rows.
  • On the FIN OCoA you add your MDX code (Appendix B), in the FIN GL Measure dim you change the type to MDX and leave it blank, it should then work.
  • Looks like you want to see the Measure column, but you can then potentially hide it, with “right-click > hide column”, as it will repeat the measure name on each row…

Hope this helps!

2 Likes
3

Awesome!!. Works beautifully!

Thanks!!!

1 Like