Topic
  • 4 replies
  • Latest Post - ‏2013-05-01T20:35:10Z by Glen.S
UAVT_David_Aspegren
UAVT_David_Aspegren
1 Post

Pinned topic Topcount products irrespective of nested levels

‏2013-04-30T10:00:19Z |

Hi all,

My requirements are:

I want a set of the top 10 products on the rows. They should be nested below product type and product line levels.

So rows look like:

Product line - Product type - top10 products-set

I am playing around with the topcount()-function but when I nest the set under the product type level, I get the top 10 products for each of the product types.

I want to keep the set of top10 products irrespective of the product types and then show their product type and product line above, Is that possible? Or do I need to do it in some other way?

Thanks in advance.

regards,

David

  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Topcount products irrespective of nested levels

    ‏2013-05-01T14:36:09Z  

    So you would like to slice on the Top 10 Product Names and hide all the empty cells?  Hint Hint

  • Glen.S
    Glen.S
    20 Posts

    Re: Topcount products irrespective of nested levels

    ‏2013-05-01T16:19:22Z  

    It depends on what studio you're using. If this is Report Studio Pro, then what you have to do is include the "all products" (root) member in the topcount tuple, something like this:

    topcount([Product], 10, tuple([Revenue], [Products (All)]) )

    Tip: Be careful about summaries when using topcount. Whether you want the top products overall, or the top products for each product line/type, if you have product group summaries on your report, for them to be correct you also have to include the ALL member for any hierarchies you use on the opposite edge. So for example, if you have products on the row edge, and years on the column edge, to have to do something like this for the former:

    topcount([Product], 10, tuple([Revenue], [Products (All)], [Time (All)]) )

    or this for the latter:

    topcount([Product], 10, tuple([Revenue], [Time (All)]) )

      /glen

  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Topcount products irrespective of nested levels

    ‏2013-05-01T17:45:37Z  
    • Glen.S
    • ‏2013-05-01T16:19:22Z

    It depends on what studio you're using. If this is Report Studio Pro, then what you have to do is include the "all products" (root) member in the topcount tuple, something like this:

    topcount([Product], 10, tuple([Revenue], [Products (All)]) )

    Tip: Be careful about summaries when using topcount. Whether you want the top products overall, or the top products for each product line/type, if you have product group summaries on your report, for them to be correct you also have to include the ALL member for any hierarchies you use on the opposite edge. So for example, if you have products on the row edge, and years on the column edge, to have to do something like this for the former:

    topcount([Product], 10, tuple([Revenue], [Products (All)], [Time (All)]) )

    or this for the latter:

    topcount([Product], 10, tuple([Revenue], [Time (All)]) )

      /glen

    Using the released 10.2 product against the Great Outdoors Sample Cube the expression:

    topcount([great_outdoors_sales_en].[Products].[Products].[Product], 10, tuple([Revenue], [Products]))

    does not produce the desired posters results.

     

    If you create a crosstab with PL, PT, and the calc above as rows, with Revenue as the columns it will give you the top 10 productNames for each nesting.  Which results in way more than 10 rows.  The desired result is only to display  10 rows in total.  To do this you can create a crosstab with PL, PT, PN on the rows, revenue on the columns and the TopCount expression as the slicer.  You can then use a filter expression or the zero row suppression to get rid of the sliced out/blank values.

    I have tested this and it works... attached is the spec.

    Attachments

  • Glen.S
    Glen.S
    20 Posts

    Re: Topcount products irrespective of nested levels

    ‏2013-05-01T20:35:10Z  

    Using the released 10.2 product against the Great Outdoors Sample Cube the expression:

    topcount([great_outdoors_sales_en].[Products].[Products].[Product], 10, tuple([Revenue], [Products]))

    does not produce the desired posters results.

     

    If you create a crosstab with PL, PT, and the calc above as rows, with Revenue as the columns it will give you the top 10 productNames for each nesting.  Which results in way more than 10 rows.  The desired result is only to display  10 rows in total.  To do this you can create a crosstab with PL, PT, PN on the rows, revenue on the columns and the TopCount expression as the slicer.  You can then use a filter expression or the zero row suppression to get rid of the sliced out/blank values.

    I have tested this and it works... attached is the spec.

    Thanks, Dan. You're quite right - the expression I quoted does not work for this case - it works only for nesting in different hierarchies.