Report Builder 3.0 - MDX - Moving Sum

Question

I have poured over quite a few websites, including this one, and so far in vain. I need to create a graph depicting the employee turnover rate, which has to be calculated over 1 year. (As stated in my title I have the option of either going with 13 periods or 12 months: both are an equivalent of 1 year.) Please see the following "sample version" of my dataset:

Year  |  Trimester  |  Period  | Date End of Period | Departures  |  Avg total Employees
2015         1          1        yyyy-mm-dd 00:00:00      2           100
2015         1          2        yyyy-mm-dd 00:00:00      3           99
2015         1          3        yyyy-mm-dd 00:00:00      4           98
2015         2          4        yyyy-mm-dd 00:00:00      0           102
2015         2          5        yyyy-mm-dd 00:00:00      1           100
2015         2          6        yyyy-mm-dd 00:00:00      0           98
2015         3          7        yyyy-mm-dd 00:00:00      4           99
2015         3          8        yyyy-mm-dd 00:00:00      3           96
2015         3          9        yyyy-mm-dd 00:00:00      4           100
2015         3          10       yyyy-mm-dd 00:00:00      0           98
2015         4          11       yyyy-mm-dd 00:00:00      0           97
2015         4          12       yyyy-mm-dd 00:00:00      2           99
2015         4          13       yyyy-mm-dd 00:00:00      3           98
2016         1          1        yyyy-mm-dd 00:00:00      2           100
2016         1          2        yyyy-mm-dd 00:00:00      4           97
2016       [...]

FYI Avg total employees= ([Total employees].[End of Period] + [Total employees].[Start of Period])/2

Here's what my graph currently looks like (apologies, the labels are in French):

Value: Departures / Avg total employees By the way, as you can surely notice, the orange was an attempt to obtain the desired result by going to Add Calculated Series and Calculated Series Properties choosing moving average: the attempt was a fail.

So here is my problem: all I want is 1 serie that displays a 12 months cumulated value for each period. That is, please see following example based on my Dataset above:

Current for Year 2016, Period 1 = 2/100 = 2%

Wanted result for Year 2016, Period 1 = (2+3+2+0+0+4+3+4+0+1+0+4+3)/((100+100)/2) = 26%, i.e. to add the departures of the last 13 periods and making a ratio over the avg number of employees: (2016P01+2015P13+2015P12+[...]+2015P03+2015P02)

So, in other words, I need to obtain a graph where the turnover value displayed at each period would be a percentage that surely would range around 20-30% and not the current 0-4% (based on my dataset values.) Any ideas?

Thanks to all!

EDIT1 I found out that by using the RunningValue function: =RunningValue(Fields!Turnover.Value, Sum, Nothing) it gives something close to what I want but not quite it still. I would basically need a "Runningtotal" with a scope of last 13 periods... if that exists =/

p.s. Fields!Turnover.Value = Fields!Departure.Value / Fields!Avg_total_Employees.Value

EDIT2 Firstly, thank you Alan. Going fwd, here's a screenshot sample of what my actual DS:

Note: The slight difference between the one I typed up and this one is that I am required to provide a turnover Tx1 (all employees included) and a turnover Tx2 (the retirements excluded). Therefore, I created the following calculated members (bottom left corner of the screenshot) and removed from my DS the [Avg total employees] and [Departures] since my Tx1 and Tx2 accounted for both:

Number of retirees: Départs_ret = ([Measures].[Nb Départs],[Départ].[Départ].&[112337])

Turnover1: Tx1 = ([Measures].[Nb Départs])/([Measures].[Nb Matricules Actifs (Moyen)])

Turnover2: Tx2 = (([Measures].[Nb Départs])-([Measures].[Départs_ret]))/([Measures].[Nb Matricules Actifs (Moyen)]))])

My MDX query in text form is the following:

WITH MEMBER [Measures].[Départs_ret] AS ([Measures].[Nb Départs],[Départ].[Départ].&[112337]) 
MEMBER [Measures].[Tx1] AS ([Measures].[Nb Départs])/([Measures].[Nb Matricules Actifs (Moyen)]) 
MEMBER [Measures].[Tx2] AS (([Measures].[Nb Départs])-([Measures].[Départs_ret]))/([Measures].[Nb Matricules Actifs (Moyen)]) 
SELECT NON EMPTY { [Measures].[Tx1], [Measures].[Tx2] } 
ON COLUMNS, NON EMPTY { ([Période Financière].[PF - Année Financière].[PF - Année Financière].ALLMEMBERS * 
[Période Financière].[PF - Trimestre No].[PF - Trimestre No].ALLMEMBERS * 
[Période Financière].[PF - Période Financière No].[PF - Période Financière No].ALLMEMBERS * 
[Période Financière].[PF - Date Fin Période].[PF - Date Fin Période].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2014], [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2013], [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2012] } ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@[PériodeFinancièrePFAnnéeFinancière], CONSTRAINED) ) 
ON COLUMNS FROM [Historique Employés PerFin])) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

My apologies if my splitting of the code isn't the right way, I tried making it somehow logical being that the SSRS simply gives it as one big blurp.

Thanks!

score 0 · Answer 1 · answered Mar 23 '17 at 22:38

Report Builder isn't a great tool to do relative running aggregates in. My solution for this would be SQL based. I don't know your data structure so I can't write you a query, but I'll give a high level explanation. What you would do is join the table to itself with the 13 period offset as a condition. Then you would be able to get the total Departures leading up to each period from that subquery. This way you would only have to do the relatively simple calculations in the report.

Alan Schofield · Answer 2 · 2017-03-24T08:51:47.257

I agree with @StevenWhite that it's sometime simpler to do this kind of thing in your dataset. I've setup a sample in SQL Fiddle here

http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/9722

The code is below: For 2016 periods it's giving results around 26-27% . The basic calculation is SUM of last 12 periods 'Departures' divided by the average of the 'Avg Total Employees'. It's including the current period too in this example but you can easily adjust the range by editing the BETWEEN clause near the end of the query.

DECLARE @data TABLE ([Year] int, [Period] int, Departures float, [Avg Total Employees] float)

INSERT INTO @data
VALUES
(2015, 1, 2, 100),
(2015, 2, 3, 99),
(2015, 3, 4, 98),
(2015, 4, 0, 102),
(2015, 5, 1, 100),
(2015, 6, 0, 98),
(2015, 7, 4, 99),
(2015, 8, 3, 96),
(2015, 9, 4, 100),
(2015, 10, 0, 98),
(2015, 11, 0, 97),
(2015, 12, 2, 99),
(2015, 13, 3, 98),
(2016, 1, 2, 100),
(2016, 2, 4, 97)

-- add a CTE with a row number to help with joining ranges of records
;WITH tmp AS
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY [Year], [Period]) as rn FROM @data
)

SELECT DISTINCT -- DISTINCT because we are not grouping, instead using windowed functions
        a.[Year], a.[Period]
        , CAST(SUM(b.Departures) OVER(PARTITION BY a.[Year], a.[Period]) / AVG(b.[Avg Total Employees]) OVER(PARTITION BY a.[Year], a.[Period]) as float) AS TurnOverRate
    FROM tmp a
        JOIN tmp b on a.rn - b.rn between 0 and 12 -- raneg of periods to be included
order by a.[Year], a.[Period]

Thanks guys, unfortunately, although I have have made great progress with the SSRS tool, this solution is an overkill for my current SQL knowledge and all I'm getting is error pop-ups (and gray hair) as I'm trying to implement this in my Query designer lol But I'm stubborn and persistent so I'll swinging at this one. — Bob, Mar 24 '17 at 15:27
The designer won;t handle things like this, you'll have to do it in Text mode. Post the query and some sample data if it's not clear what the tables contain and i'll take a look later this evening. — Alan Schofield, Mar 24 '17 at 15:58
Srry Alan for the delay, I couldn't come to work yesterday, but thanks a lot for your offer and I hope Edit2 provides all the info you suggested. — Bob, Mar 25 '17 at 18:00
HI Max, unfortunately I didn't realise that your query was written in MDX, not T-SQL. However, as far as I am aware, MDX has built in functions to handle these range based expressions. I won't be able to help but I would change the tags on you question to include SSAS and MDX and it will be seen by people more suitable. Sorry to get your hopes up! — Alan Schofield, Mar 25 '17 at 23:14

score 0 · Accepted Answer · answered Mar 27 '17 at 17:47

After pouring over MDX stuff, I found the solution. Indeed, it has to be done in the Text Mode in the Query Designer and you have to use the Lag() function. So, here is what my DS looks like now:

WITH 
MEMBER [Measures].[Total_déb] 
AS ([PF - Date Fin Période].CurrentMember.Lag(12), [Measures].[Nb Matricules Actifs (DtDeb)])
MEMBER [Measures].[Départ_YTD]
AS 'SUM(
{[PF - Date Fin Période].CurrentMember.Lag(12):[PF - Date Fin Période].CurrentMember}, [Measures].[Nb Départs])'
MEMBER [Measures].[Départ_ret_YTD]
AS 'SUM(
{[PF - Date Fin Période].CurrentMember.Lag(12):[PF - Date Fin Période].CurrentMember}, ([Measures].[Nb Départs],[Départ].[Départ].&[112337]))'
SELECT NON EMPTY { [Measures].[Total_déb], [Measures].[Départ_YTD], [Measures].[Départ_ret_YTD], [Measures].[Nb Total Employés Actifs (DtFin)] } ON COLUMNS, NON EMPTY 
{ ([Période Financière].[PF - Année Financière].[PF - Année Financière].ALLMEMBERS * [Période Financière].[PF - Trimestre No].[PF - Trimestre No].ALLMEMBERS * [Période Financière].[PF - Période Financière No].[PF - Période Financière No].ALLMEMBERS * [Période Financière].[PF - Date Fin Période].[PF - Date Fin Période].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM ( SELECT ( -{ [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2014], [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2013], [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2012] } ) ON COLUMNS 
FROM ( SELECT ( STRTOSET(@[PériodeFinancièrePFAnnéeFinancière], CONSTRAINED) ) ON COLUMNS FROM [Historique Employés PerFin])) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

And here's a screenshot of my DS now:

Report Builder 3.0 - MDX - Moving Sum

3 Answers3