Search for answers here or ask a question

"Classic Pivot table" design


I want to create a table that is similar in style to the classic pivot table in excel. In that sense I also wish to add multiple attributes from the same "dimension" from my data source/cube. In short, I want to create a table of orders with a selected number of rows (attributes).  In this example I want to include only what we have defined as "large orders".  In my data source, I have a dimension named "Larger Order". I then want my table to include both "Order Type",  "Delivery Month" and "Large Order No", as seen below.

However, when I have added "Large order" to the row's section to my table in Discovery, I cannot get any more rows in of the same dimension. Is there anyway I can work around this? 

Many thanks in advance!



5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Niclas Lovgren ,

    Do you want something like this? In this case we have a product hierarchy that drills down from product categroy to product. I did not put Product (the lowest member of the hierarchy) into the Rows, but what you need to do here is drag and drop the Hierarchy, in your case "Large Number" into the drop zones. Then you are able via the plusses in the grid to  drill down and see all the data.

    I hope that helps! 

    If you have any further questions, do not hesitate to ask them.


    Like 1
    • Ian MacdonaldAdmin
    • Team Lead - Product Management
    • Ian_Macdonald
    • 1 mth ago
    • Reported - view

    Hi Niclas,

    Just drop your additional hierarchies / attributes into your Rows DropZone:

    Hope that helps.


  • Thank you both for your replies and sorry for my late reply. 

    I am more looking for a way to structure the table differently, similar to a "classic pivot table". In the example below. First part is the way Pyramid allows me to show the data but I am looking for a way to structure it as the second table shows and where the item labels are shown on each row.

    The error also persists. Pyramid does not allow me to drag each member of the hierarchy to the rows section. I want each column to show "Order type", "Delivery Month" and "Large Order No". When I start with "Order Type" and drop it to the Rows section. In the rows section it then only says "Large Order" and having that in the rows section, Pyramid does not allow me to drag and drop any more of the other three members in the hierarchy. Nothing happens when I try.  

    In the end I want a table looking something like below

    Do you have any idea of how I can make this happen?


      • Ian MacdonaldAdmin
      • Team Lead - Product Management
      • Ian_Macdonald
      • 4 wk ago
      • Reported - view

      Hi Niclas,

      You cannot use the different levels in the hierarchy as individual attributes in this way.

      In most well designed Cubes, if there is a hierarchy of attributes, then those individual attributes are normally also provided (for this reason), often collected together in a "Folder" within that dimension.

      If your cube does not have them, you can ask your Cube developer to add them. It would only take a few minutes as they are available, just not displayed to you.

      May I ask why you want to display in a fashion that is much less elegant than the expand / collapse hierarchy?


    • Many thanks Ian, I will consult with the cube developer. 
      I want the table to be part of a sheet supporting a graph showing large order development per month and the table will provide a quick overview of say the top 5-10 orders for the latest month rather than having to drill down. I also think the classic view looks more neat for that purpose.


      Thanks again! 

Like Follow
  • Status Answered
  • 4 wk agoLast active
  • 5Replies
  • 46Views
  • 3 Following