MDX Query, how to select first and last year of an event's period

Question

I have a publications datamart from teachers of my university, I would like to select the list of teachers with how much books they wrote and in period while them publicated the books.

Example Teacher - Last Year of pubblication - Number of Books

Teacher A - 2014 - 200 books

I tried to do something like:

WITH MEMBER [Measures].[LastYear] AS 
   '(ClosingPeriod([Anno].[Anno])
         ,[Autore].[Nome].CurrentMember)'
SELECT 
  {[Measures].[Unita (Libri)],[Measures].[LastYear]} ON COLUMNS,
NON EMPTY 
   [Autore].[Nome].Members ON ROWS
FROM [Pubblicazioni]

but the field last year is alway void, if i try to change the second part of ClosingPeriod with [Measures].[Unita (libri)] it is empty too... How should i implement that query? I'm a bit confused about

EDIT

select [Measures].[Libri] ON COLUMNS,
crossjoin([Autore.default].[Nome].[Fazzinga, Bettina], tail(NonEmptyCrossJoin([Autore.default].[Nome].[Fazzinga, Bettina], [Anno.default].[Anno].Members), 1).Item(0).Item(1)) ON ROWS
from [Pubblicazioni]

This one is working great for the teacher [Fazzinga, Bettina], but the query stops to work while i doing

select [Measures].[Libri] ON COLUMNS,
crossjoin([Autore.default].[Nome].Members, tail(NonEmptyCrossJoin([Autore.default].[Nome].CurrentMember, [Anno.default].[Anno].Members), 1).Item(0).Item(1)) ON ROWS
from [Pubblicazioni]

What about using a MAX() aggregation to find the maximum year (i.e. the most recent year for that teacher) instead of a SUM() aggregation? — Magnus Smith, Nov 05 '14 at 14:33
I tried to use something like WITH MEMBER [Measures].[TopYear] AS 'max([Anno].[Anno].Members)' and it gives me values like 3, 4, 5... I don't know why — Neo87, Nov 05 '14 at 14:45
You may wish to read about the AGGREGATE() function, which is slightly different to what you did there. See http://msdn.microsoft.com/en-us/library/ms145524.aspx — Magnus Smith, Nov 06 '14 at 14:02
I was thinking that the list of all years when a teacher published a book could be turned into the 'last date published' by looking for the maximum. Sorry for confusing you with AGGREGATE(), I see that would only be useful if the cube itself had the aggregation option set to be MAX (the default is SUM). — Magnus Smith, Nov 07 '14 at 11:56
@MagnusSmith, i think i understand what you are saying now. I should set a new Measure in the cube with aggregation max() and Column Year :) — Neo87, Nov 12 '14 at 13:51

whytheq · Answer 1 · 2014-11-06T12:58:57.973

Measures needs some form of value or string to return, I think that is why your measure is returning void as ClosingPeriod is returning a member rather than value.

The following is not tested:

WITH MEMBER [Measures].[LastYear] AS 
   '(Tail(
       nonempty(
         [Anno].[Anno]
         ,([Autore].[Nome].CurrentMember, [Measures].[Unita (Libri)])
       )
    ).item(0).item(0).MEMBERVALUE)'
SELECT 
  {[Measures].[Unita (Libri)],[Measures].[LastYear]} ON COLUMNS,
NON EMPTY 
   [Autore].[Nome].Members ON ROWS
FROM [Pubblicazioni]

Try this one instead:

SELECT 
  [Measures].[Libri] ON COLUMNS

 ,Generate
  (
    [Autore.default].[Nome].MEMBERS
   ,Tail
    (
      //NonEmptyCrossJoin <<hopefully nonempty is enough
      NonEmpty
      (
        [Autore.default].[Nome].CurrentMember * [Anno.default].[Anno].MEMBERS
      )
     ,1
    ) //.Item(0).Item(1) //<<don't believe this is required
  ) ON ROWS
FROM [Pubblicazioni];

Mondrian Error:No function matches signature 'nonempty(, )' anyway i edited the question with a new query (thought it by watching your query) but .currentmember is not working :(. And if i use the result as [Measures].[LastYear] on COLUMNS it gives me the count of books — Neo87, Nov 06 '14 at 11:33

Neo87 · Accepted Answer · 2014-11-13T14:14:28.490

I finally found a way to get it... Natively, every calculated member in the clause "WITH MEMBER" are treated as information of cube. In my case, if i do max("xpression of years when author worked") it will give the number of books he wrote in the relative max year. To avoid this, there is a construct called SetToStr() that will print the year as a String (something like [Hyerarchie].[Level].[Value]). The string can simple cutted with left() and right() condition. Here is the result:

WITH MEMBER [Measures].[Anno prima pubblicazione] AS 'left(right(settostr(head(extract(nonemptycrossjoin([Anno].[Anno].Members, nonemptycrossjoin([Libro].[Titolo].Members,[Autore].[Nome].CurrentMember)),[Anno]),1)),6),4)'
MEMBER [Measures].[Anno ultima pubblicazione] AS 'left(right(settostr(tail(extract(nonemptycrossjoin([Anno].[Anno].Members, nonemptycrossjoin([Libro].[Titolo].Members,[Autore].[Nome].CurrentMember)),[Anno]),1)),6),4)'
SELECT {[Measures].[Anno prima pubblicazione],[Measures].[Anno ultima pubblicazione]} ON COLUMNS,
[Autore].[Nome].Members ON ROWS
FROM [Pubblicazioni]

This query work in this way:

1) extract books for the current author with nonemptycrossjoin([Libro].[Titolo].Members,[Autore].[Nome].CurrentMember) so i get the tuple ;

2) I let the year join the previous tuple with nonemptycrossjoin([Anno].[Anno].Members,);

3) In my case the DM is ordered yet for year, in other case we should use even the function order;

4) we don'n need all that things, so we trash what we doesn't need using extract(,[Anno]), this mean i will maintain only the values of hierarchy [Anno];

5) we just need the first entry of the ordered list of years and we use head() function;

6) we can't use the value yet or MDX will count entries in the fact table for the value of [Anno] we selected, so we transform it as string with setToStr();

7) the value is still dirty, cause the string has hiearchy too, so we clean it using left() and right() function (a year is always composed by 4 chars).

It is a little big function, but this is the only one worked for me :)

MDX Query, how to select first and last year of an event's period

2 Answers2