SSAS custom group by query

My fact table looks like this

yesterday a
yesterday a
yesterday a
yesterday b
yesterday b
yesterday c
today     a
today     a
today     b
today     b
tommorow  a
tommorow  a
tommorow  c
tommorow  d

In the end I need an Excel report like this

               repetition count
               1     2     3
yesterday      1     1     1
today          0     2     0
tomorow        2     1     0

How to create a "repetion count" dimension in SSAS 2k5 ? Please keep in mind that my fact table is a liitle bit more complicated and I have more other dimension there.

My idea is to create a named query in DSV but I have some doubts if filtering will work correctly.

From stackoverflow Michal Sznajder

• I think you would need to do a view with a GROUP BY and a count so that you load data like the following into your cube

  yesterday a 3
  yesterday b 2
  yesterday c 1
  today     a 2
  today     b 2
  tomorrow  a 2
  tomorrow  c 1
  tomorrow  d 1
  

  Then you could use the count column as the key for a repetition count dimension and you would create a measure based on the row count aggregate type.

  From Darren Gosbell