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.
WITH
MEMBER [Measures].[Count of Rows] AS
Axis(1).Count
MEMBER [Measures].[Count of Hierarchies on Rows] AS
Axis(1).Item(0).Count
MEMBER [Measures].[Row Number] AS
Rank(
StrToTuple(
"( " +
Generate( Head(Axis(1),
[Measures].[Count of Hierarchies on Rows] ) AS L,
"Axis(1).Item(0).Item(" +
CStr(L.CurrentOrdinal – 1) +
").Hierarchy.CurrentMember",
" , " ) +
" )"
),
Axis(1)
)
, FORMAT_STRING = "#,#"
SELECT
{
–[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
FROM
[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.