Jan 192009


While doing some calculations for a client, I encountered a not so strange wish – to show an incremental number for each row of data. Although it seems pretty easy, I ended playing with it for several hours, because it had to be generic solution, the one that doesn’t depend on hierarchies placed on rows.

Finally I got it! Here it is.



MEMBER [Measures].[Count of Rows] AS

MEMBER [Measures].[Count of Hierarchies on Rows] AS

MEMBER [Measures].[Row Number] AS
                    "( " +
                     Generate( Head(Axis(1),
                                    [Measures].[Count of Hierarchies on Rows] ) AS L,
                              "Axis(1).Item(0).Item(" +
                               CStr(L.CurrentOrdinal – 1) +
                              " , " ) +
                    " )"
    , FORMAT_STRING = "#,#"

        –[Measures].[Count of Rows],
        –[Measures].[Count of Hierarchies on Rows],
        [Measures].[Row Number]
    } ON 0,
        [Date].[Calendar].[Calendar Year].MEMBERS *
        [Product].[Product Categories].[Category].MEMBERS
    } ON 1
    [Adventure Works]


It needs a bit more tweaking to become bulletproof, but I’ll leave that for some other occasion.

A remark for Excel users. Beware that Excel sometimes tricks you and keeps hierarchies on columns although they look like they are on rows. If you’re using Excel as an OLAP client, make sure you put 2 measures on columns, that will force hierarchies placed on rows to actually appear there.

Jan 182009


2008 was a very good year for me. I’ve been on maternity leave with my son, engaged in SSAS forum, went to London and Seattle and met some very nice people from all around the globe, virtually and in person.

I fulfilled all the goals I set for myself:

1) Become recognized as one of the people who are good in SSAS
2) Achieve and retain a top half position in SSAS forum
2) Spread the word about our product, CubePlayer
3) Pass my first MS exam (70-445)

Although there were some losses (my pension fund i.e., as everyone’s else I guess), the overall gain is much greater. And despite the current crisis, I believe 2009 will bring even more good things for me and the company I work for. It’s only up to us to reach for it!