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.
Great help !!!
thank you very much
Hey, Tomislav
I was just trying to figure out how to add ranking values for a report parameter query for a client project. I was delighted when your name appeared at the top of the Bing search. Thanks for saving me time. Hope to see you soon.
Thanks, Paul
Still a very good solution for my problem.
hm, when i copy&paste this MDX to SSMS (SQL2008R2) i get following error:
Parser: The following syntax error occurred during parsing: Invalid token, Line 16, Offset 54, –
Hi Jochen,
see what’s in that line 16. Maybe one of the quotes is not valid in my MDX. It can happen since this is RTF text and I might have put the wrong quote inside it while writing the post or maybe WordPress did that to me. I don’t see what else could be wrong with this MDX.
Hope it helps.
A problem is in “-” sign in “CStr(L.CurrentOrdinal – 1)” line. Just erase it and type again and it’ll work.
[…] Quelle: https://tomislav.piasevoli.com/2009/01/19/row-number/ […]
[…] Quelle: https://tomislav.piasevoli.com/2009/01/19/row-number/ […]
I would like to thank you from the bottom of my heart. I was struggling with getting it done for such a long time and you resolved the issue. Thnaks a lot
Cheers!!
Excellent.
This is working for me but I have to covert the whole query in dynamic query and these “” are so difficult to handle . Can you convert the above query in dynamic query and post here, I am really struggling on how to make it work as dynamic query. Please I will appreciate your help.
Thanks