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.

 

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.

  11 Responses to “Row number”

  1. Great help !!!
    thank you very much

  2. 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

  3. Still a very good solution for my problem.

  4. 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.

  5. 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!!

  6. Excellent.

  7. 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

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)