locked
Order a measure and display only top 10 RRS feed

  • Question

  • Hello,

    I have a measure called visits ( visit to a webpage) and a dimension attribute  'page title'. Now I want the user to see  the top 10 most popular pages, popularity being measured with the number of visits. In short, I want to select page title, visits, then sort by visits desc and show top 10. 

    How is this possible in ssas?

    My users view this in excel ( with a pivot connection to the cubes). I can sort this pivot table based on dimension attributes, but not on measures. Also, I cannot restrict the views to top 10.

    Please help.

     

    Thanks.

    Friday, December 30, 2011 6:04 AM

Answers

  • Here you go

     

     

    CURRENT MEMBER CURRENTCUBE.[Measures].[Top10Cnt]  AS

     

    TOPCOUNT({[Website Page Title].[Website Page Title].[Website Page Title].members},10,[Measures].[Visits]),

    VISIBLE =1;

     

    HTH,

    Ram


    Ram
    • Proposed as answer by Asim Bagwan Friday, December 30, 2011 6:20 PM
    • Marked as answer by Challen Fu Thursday, January 5, 2012 10:05 AM
    Friday, December 30, 2011 6:00 PM

All replies

  • Hi,

    Try with following MXD:

    SELECT
        {([Measures].[visits])} ON COLUMNS,
    TOPCOUNT (
        {[Your Dimention Name].[page title].Members}, 10,
        ([Measures].[visits]))
         ON ROWS
    FROM [Your Cube Name]
    



    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Proposed as answer by Sandip Shinde Friday, December 30, 2011 9:33 AM
    Friday, December 30, 2011 6:59 AM
  • Hi Asim,

    how can these be done within excel?


    Andrew
    BI, Data Mining, Analytical CRM
    Friday, December 30, 2011 3:33 PM
  • Hi Asim,

     

    This is what I tried :

     

     

    CREATE MEMBER CURRENTCUBE.[Measures].[Orders visits - page title]as 

    TOPCOUNT (

        [Website Page Title].[Website Page Title].[Website Page Title].members, 10,

        ([Measures].[visits])),

    VISIBLE =1;

     

     

    I got a #VALUE error with message " a string or numeric expression was expected. instead a tuple was provided"

     

    Thanks

    Friday, December 30, 2011 4:54 PM
  • Here you go

     

     

    CURRENT MEMBER CURRENTCUBE.[Measures].[Top10Cnt]  AS

     

    TOPCOUNT({[Website Page Title].[Website Page Title].[Website Page Title].members},10,[Measures].[Visits]),

    VISIBLE =1;

     

    HTH,

    Ram


    Ram
    • Proposed as answer by Asim Bagwan Friday, December 30, 2011 6:20 PM
    • Marked as answer by Challen Fu Thursday, January 5, 2012 10:05 AM
    Friday, December 30, 2011 6:00 PM