MDX NON EMPTY multidimension axis

Question

I'm writting MDX code in VBA and I have to put the data in Excel.

SELECT 
{ [Measures].[VL PROD], [Measures].[Impostos], [Measures].[RecLiqProd], [Measures.[ValorMgProd], [Measures].[QTD ITENS] ,[Measures].[VL FRETE] } ON 0, 
NON EMPTY ( { Descendants( [Produto].[Produto].[Departamento], 5 ) } ) ON 1,
NON EMPTY ( { [Data Pedido].[Data].[Ano].&[2014].&[2].&[6].&[1]:[Data Pedido].[Data].[Ano].&[2014].&[2].&[6].&[26] } ON 2,
NON EMPTY ( { [Unidade Negócio].[Unidade Negócio].&[Unidade 1], [Unidade Negócio].[Unidade Negócio].&[Unidade 2], [Unidade Negócio].[Unidade Negócio].&[Unidade 3] } ) ON 3
FROM [Rentabilidade]
WHERE ( - Extract( { [Livre de Debito] }, [Meio Pagamento].[Meio Pagamento]) )


For i = 0 To cst.Axes(1).Positions.Count - 1
    For j = 0 To cst.Axes(2).Positions.Count - 1
        For k = 0 To cst.Axes(3).Positions.Count - 1

            'If cst(0, i, j, k) * cst(1, i, j, k) * cst(2, i, j, k) * cst(3, i, j, k) * cst(4, i, j, k) * cst(5, i, j, k) <> "" Then

                Cells(a, 1) = cst.Axes(1).Positions(i).Members(0).Caption
                Cells(a, 2) = cst.Axes(2).Positions(j).Members(0).Caption
                Cells(a, 3) = cst.Axes(3).Positions(k).Members(0).Caption
                Cells(a, 4) = cst(0, i, j, k)
                Cells(a, 5) = cst(1, i, j, k)
                Cells(a, 6) = cst(2, i, j, k)
                Cells(a, 7) = cst(3, i, j, k)
                Cells(a, 8) = cst(4, i, j, k)
                Cells(a, 9) = cst(5, i, j, k)


                a = a + 1
            'End If
        Next k
    Next j
Next i

The problem is that I get plenty of empty rows; I'd like to know how I can remove them.

For example, I'm getting the following:

Id | Data  | Bandeira  | impostos | recliq | ValorMrg | Qtd Item | Vl Frete
10 | 40230 | Unidade 1 |          |        |          |          |
10 | 40230 | Unidade 2 |          |        |          |          |
10 | 40230 | Unidade 3 | 0,2      |  2032  |  100     | 1000     |  323

32 | 40231 | Unidade 3 |          |        |          |          | 
32 | 40232 | Unidade 3 |          |        |          |          | 
32 | 40233 | Unidade 3 | 0,2      |    32  |  321     | 5045     |  323

I thought I had understood the difference between non empty and nonempty (from http://beyondrelational.com/modules/2/blogs/65/posts/11569/mdx-non-empty-vs-nonempty.aspx) but maybe I'm missing something.

Can anyone help me?

score 1 · Accepted Answer · answered Jul 01 '14 at 07:22

If you want to have a two dimensional report, why do you run a four dimensional query?

I would think that the following MDX

SELECT 
{ [Measures].[VL PROD], [Measures].[Impostos], [Measures].[RecLiqProd], [Measures.[ValorMgProd], [Measures].[QTD ITENS] ,[Measures].[VL FRETE] }
ON 0, 
NON EMPTY 
{ Descendants( [Produto].[Produto].[Departamento], 5 ) } )
*
{ [Data Pedido].[Data].[Ano].&[2014].&[2].&[6].&[1]:[Data Pedido].[Data].[Ano].&[2014].&[2].&[6].&[26] }
*
{ [Unidade Negócio].[Unidade Negócio].&[Unidade 1], [Unidade Negócio].[Unidade Negócio].&[Unidade 2], [Unidade Negócio].[Unidade Negócio].&[Unidade 3] } )
ON 1
FROM [Rentabilidade]
WHERE ( - Extract( { [Livre de Debito] }, [Meio Pagamento].[Meio Pagamento]) )

would deliver what you want. In this case, the NON EMPTY on Axis 1 would be evaluated for each tuple of the cross product of the three hierarchies against the columns axis.

Of course, then you would have to change your VBA code accordingly, as you now have only one axis for the rows, but it has three positions instead of one.

Hmmm probably because I'm dumb! Hehe sorry, I started with MDX last week so I didn't even know that this was possible! You solved my problem man! My query is wayyyyyy faster! Thank you very much! — Fabio Rebelo, Jul 01 '14 at 11:20

MDX NON EMPTY multidimension axis

1 Answers1