Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
4 replies Latest Post - ‏2013-05-01T20:35:10Z by Glen.S
UAVT_David_Aspegren
UAVT_David_Aspegren
1 Post
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Topcount products irrespective of nested levels

    ‏2013-05-01T14:36:09Z  in response to UAVT_David_Aspegren

    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
    ACCEPTED ANSWER

    Re: Topcount products irrespective of nested levels

    ‏2013-05-01T16:19:22Z  in response to UAVT_David_Aspegren

    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
      ACCEPTED ANSWER

      Re: Topcount products irrespective of nested levels

      ‏2013-05-01T17:45:37Z  in response to Glen.S

      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
        ACCEPTED ANSWER

        Re: Topcount products irrespective of nested levels

        ‏2013-05-01T20:35:10Z  in response to DanielWagemann

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