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!)
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].&[1]&[2019]&[TR-1]</Value>
</Parameter>
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.