Search code examples
ssasmdx

How to get former year using MDX expression


I've a working MDX query in a SSRS dataset, not done by me, that has the following lines inside it:

ON COLUMNS FROM ( SELECT ( STRTOSET(@pYear, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pMonth, CONSTRAINED) ) 
...

Everything works fine. However, I need to duplicate this dataset to have another one with the same data but from the former year (subtract one).

I've tried to modify it, got an error and then learned about MDX which I didn't know until now. However I've not been able to understand exactly how it works and how could I get the previous year. I've been reading a lot of docs, but examples are not being quite clear to me.

I have tried with the Sum function, but I wasn't able to make it work because all the examples that I found do start with a with clause that can't work in the middle of a query, plus these examples seem to use members but my query is using sets (if I don't misunderstand it).

I've tried using at the begining:

MEMBER [Measures].[Ano Prev] AS SUM(STRTOMEMBER(@pYear) , -1)

But then after that, I'm not able to use it in the lines like the previous ones.

How can I achieve it? (Insights and "how to" explanations would be greatly appreciated!)

Update

Note that the @pYear from the question is translated as @pEjercicio in the query.

Complete original query:

WITH MEMBER [Measures].[Comision] AS [Measures].[Comision Once]*[Measures].[Importe] 

SELECT NON EMPTY { [Measures].[Importe], [Measures].[Unidades], [Measures].[N Tiendas], [Measures].[Comision Once] } ON COLUMNS, 


NON EMPTY { ([Tiempo].[Periodos].[Ano]*[Tiempo].[Meses].[Mes].ALLMEMBERS * [Productos].[Once Modalidad].[Once Modalidad].ALLMEMBERS ) } 


ON ROWS FROM ( SELECT (-{[Productos].[Cod-Producto].[Cod Producto].&[139972]} ) 


ON COLUMNS FROM ( SELECT ( STRTOSET(@pEjercicio, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pMes, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pSemana, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( -{ [Productos].[Once Modalidad].&[-] } ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pOrganizacion, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pDirectorRegional, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pTecnicoComercial, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pCoordinadorComercial, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pTecnicoFranquicia, CONSTRAINED) ) 
ON COLUMNS FROM [Transacciones]))))))))))

Modified query based on answer:

WITH MEMBER [Measures].[Comision] AS [Measures].[Comision Once]*[Measures].[Importe] 
MEMBER [Measures].[Ano Prev] AS SUM((STRTOMEMBER(@pEjercicio).PrevMember) , -1)

SELECT NON EMPTY { [Measures].[Importe], [Measures].[Unidades], [Measures].[N Tiendas], [Measures].[Comision Once] } ON COLUMNS, 


NON EMPTY { ([Tiempo].[Periodos].[Ano]*[Tiempo].[Meses].[Mes].ALLMEMBERS * [Productos].[Once Modalidad].[Once Modalidad].ALLMEMBERS ) } 


ON ROWS FROM ( SELECT (-{[Productos].[Cod-Producto].[Cod Producto].&[139972]} ) 

ON COLUMNS FROM ( select ( 
STRTOSET(@pEjercicio, CONSTRAINED).item(0).prevmember,
STRTOSET(@pEjercicio, CONSTRAINED).item(1)  ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pMes, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pSemana, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( -{ [Productos].[Once Modalidad].&[-] } ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pOrganizacion, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pDirectorRegional, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pTecnicoComercial, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pCoordinadorComercial, CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@pTecnicoFranquicia, CONSTRAINED) ) 
ON COLUMNS FROM [Transacciones]))))))))))

Some profiler data:

<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">
        <Parameter>
          <Name>pEjercicio</Name>
          <Value xsi:type="xsd:string">-1</Value>
        </Parameter>
        <Parameter>
          <Name>pMes</Name>
          <Value xsi:type="xsd:string">[Tiempo].[Periodos].[Mes].&amp;[1]&amp;[2019]&amp;[TR-1]</Value>
        </Parameter>

Solution

  • This is the way I solved it (with some colleague guidance) with a bit of workaround, since I first aimed to have a complete dataset of the previous year. However, this solution targets only the desired values instead of a complete new dataset.

    The idea is to get the value as a member and then add it to columns.

    Since I need to get it for the previous year (title of the question here), I have to use parallelperiod to get this year. The argument for this function is a positive int because it means a lag.

    MEMBER [Measures].[Your_Parameter_Previous_Year] AS ( PARALLELPERIOD( [Time].[Years].[Year] , 1 , [Time].[Years].CurrentMember ),[Measures].[Your_Parameter] )

    If you need more than one value, you would need to prepare as many members as you need.

    Then, you have to include it in the select like:

    SELECT NON EMPTY { [ ...
    [Measures].[Your_Parameter],
    [Measures].[Your_Parameter_Previous_Year],
    ... }
    

    And that's it. Now you have the values from the previous year in your dataset.