none
Working with the top 5 rows RRS feed

  • Question

  • Hi,

     

    in my SSAS data set I want to display only the first 5 rows based on a metric value.

     

    let's say we have the following dataset :

     

    sales by region :

    Region               Sales Amount

    ---------------------------------------------------

    Region 1             5,006,128.00

    Region 2             1,056,108.00

    Region 3             3,116,198.00

    Region 4             9,236,138.00

    Region 5             1,006,178.00

    Region 6             7,006,138.00

    Region 7             9,006,118.00

    Region 8             3,006,108.00

    Region 9             4,306,198.00

    Region 10           3,236,128.00

    Region 11           1,116,178.00

    Region 12           2,106,118.00

    Region 13           8,606,108.00

    Region 14           9,706,128.00

    Region 15           8,126,188.00

    Region 16           3,006,168.00

    Region 17           8,786,108.00

    Region 18           3,340,138.00

     

    then the result I wanna get is the following, ordered by sales amount :

     

    Region               Sales Amount

    ---------------------------------------------------

    Region 14           9,706,128.00

    Region 4             9,236,138.00

    Region 7             9,006,118.00

    Region 17           8,786,108.00

    Region 13           8,606,108.00

    Region 15           8,126,188.00

     

     

    Thanks for you help.

    Friday, July 25, 2008 5:25 PM

Answers

  •  

    Hi,

     

    try this:

     

    Code Snippet

    WITH

    SET TopN AS

    { TopCount(Region.Region.Region.MEMBERS, 5, Measures.[Sales Amount]) }

    SELECT

    { Measures.[Sales Amount] } on 0,

    { TopN } on 1

    FROM CubeName

     

     

    Regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

     

     

    Friday, July 25, 2008 5:58 PM
    Answerer
  •  

    Hi,

     

    it should be ok like this:

     

    Code Snippet

    WITH

    SET TopN AS

    { TopCount(

     Region.Region.Region.MEMBERS *

     SalesType.SalesType.SalesTypeDesc.MEMBERS,

     5,

     Measures.[Sales Amount]

         )

    }

    SELECT

    { Measures.[Sales Amount] } on 0,

    { TopN } on 1

    FROM CubeName

     

     

    When using TopCount, your results should be sorted DESC. If not, it's an undoubtful mark that something is wrong. If so, post what you have in order for me to do some guessing.

     

    Btw, I see no changes in your result when adding this other attribute. How come? Each region has only one salestype?

     

    Best regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Friday, July 25, 2008 8:56 PM
    Answerer

All replies

  •  

    Hi,

     

    try this:

     

    Code Snippet

    WITH

    SET TopN AS

    { TopCount(Region.Region.Region.MEMBERS, 5, Measures.[Sales Amount]) }

    SELECT

    { Measures.[Sales Amount] } on 0,

    { TopN } on 1

    FROM CubeName

     

     

    Regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

     

     

    Friday, July 25, 2008 5:58 PM
    Answerer
  • Thanks for your answer, I tried it and it works fine!!!

     

     

    now, what if I had another attribute Sales Type (Internet, Store, Representative, Others) and my original dataset is the following :

     

    Sales Type          Region               Sales Amount

    ------------------------------------------------------------------------

    Internet               Region 1             5,006,128.00

    Store                  Region 2             1,056,108.00

    Store                  Region 3             3,116,198.00

    Store                  Region 4             9,236,138.00

    Store                  Region 5             1,006,178.00

    Store                  Region 6             7,006,138.00

    Internet               Region 7             9,006,118.00

    Internet               Region 8             3,006,108.00

    Representative     Region 9             4,306,198.00

    Representative     Region 10           3,236,128.00

    Representative     Region 11           1,116,178.00

    Store                  Region 12           2,106,118.00

    Store                  Region 13           8,606,108.00

    Others                Region 14           9,706,128.00

    Store                  Region 15           8,126,188.00

    Store                  Region 16           3,006,168.00

    Internet               Region 17           8,786,108.00

    Internet               Region 18           3,340,138.00

     

     

    then the result I wanna get is the following, ordered by sales amount :

     

    Sales Type          Region               Sales Amount

    ------------------------------------------------------------------------

    Others                Region 14           9,706,128.00

    Store                  Region 4             9,236,138.00

    Internet               Region 7             9,006,118.00

    Internet               Region 17           8,786,108.00

    Store                  Region 13           8,606,108.00

    Store                  Region 15           8,126,188.00

     

     

    Thanks for you help.

     

    Friday, July 25, 2008 7:27 PM
  •  

    I tried the following and it seems to be OK except the fact that I'm having null values for some rows:

     

     

    Code Snippet

    WITH

    SET TopN AS

    { TopCount(Region.Region.Region.MEMBERS * SalesType.SalesTypeDesc.MEMBERS, 5, Measures.[Sales Amount]) }

    SELECT

    NON EMPTY { Measures.[Sales Amount] } on 0,

    NON EMPTY { TopN } on 1

    FROM CubeName

     

     

    do you have any idea?

     

    Thanks for your precious help.

    Friday, July 25, 2008 7:41 PM
  •  

    Hi,

     

    it should be ok like this:

     

    Code Snippet

    WITH

    SET TopN AS

    { TopCount(

     Region.Region.Region.MEMBERS *

     SalesType.SalesType.SalesTypeDesc.MEMBERS,

     5,

     Measures.[Sales Amount]

         )

    }

    SELECT

    { Measures.[Sales Amount] } on 0,

    { TopN } on 1

    FROM CubeName

     

     

    When using TopCount, your results should be sorted DESC. If not, it's an undoubtful mark that something is wrong. If so, post what you have in order for me to do some guessing.

     

    Btw, I see no changes in your result when adding this other attribute. How come? Each region has only one salestype?

     

    Best regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Friday, July 25, 2008 8:56 PM
    Answerer
  • In fact, something was wrong (and it's my cube design ), after I fixed it, everything went alright.

     

    For your remark regarding the changes that I should have when adding a new attribute, I have nothing to say but RESPECT .... it's not really obvious to 'smell' that something is wrong.......

    in fact, the dataset I mentioned is only an example that wrote manualy, just to avoid posting real source code for confidentiality purpose.....

    again, I really thank you for your precious help.

     

    Friday, July 25, 2008 9:13 PM
  •  

    Your welcome!

     

    Regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Friday, July 25, 2008 9:34 PM
    Answerer