THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

PowerPivot and Parent/Child hierarchies

Does PowerPivot handle Parent/Child hierarchies? The common answer is “no”, since it does not handle them natively. During last PowerPivot course in London, I have been asked the same question once more and had an interesting discussion about this severe limitation of the PowerPivot data modeling and visualization capabilities. On my way back in Italy, I started thinking at a possible solution and, after some work, I managed to make PowerPivot handle Parent/Child hierarchies in a very nice way, which is indistinguishable from SSAS. This post explains the technique.

Before starting the real topic of the post, I need to say a couple things:

  1. This is definitely NOT self service BI. I am going to adopt advanced data modeling techniques and, to fully understand how the formulas work, you need to have a good understanding of the DAX behavior in terms of filter contexts. From the SQL point of view, on the other hand, only a basic understanding of CTE is required.
  2. After one day of hard work on this topic, my desktop was loaded with sheets of papers full of doodles and the test workbook contained an insane number of non-working formulas. It has been a phone call with Marco Russo which really turned on the light into the solution, we definitely work fine as a team! Thus, I share the merit of the post with Marco, and I feel the need to make it clear.

And now, let’s get back to business, and start the topic.

Let us suppose that we have a hierarchy of sales persons, like this:


This hierarchy has two root nodes and then some children, with a maximum depth of three levels. Each individual in this chart produces sales, stored in an invoice table that contains a reference to the individual. Data is stored in the classical way inside two tables: Tree contains the hierarchy and Invoices contains the invoices. The table structure is the following:

    NodeID INT,
    Node VARCHAR (100),
    ParentID INT)

    NodeID INT, 
    InvoiceID INT, 
    Amount INT, 
    City VARCHAR (100)

Once you have created the tables, you can populate them with sample data using this script, that creates the hierarchy and puts some values in the invoices:

VALUES  ( 1, 'Bill',       NULL),
        ( 2, 'Brad',       1),
        ( 3, 'Julie',      1),
        ( 4, 'Chris',      2),
        ( 5, 'Vincent',    2),
        ( 6, 'Annabel',    NULL),
        ( 7, 'Catherine',  6),
        ( 8, 'Harry',      6),
        ( 9, 'Michael',    6);

VALUES (2,  3, 200, 'Chicago'),
       (2,  4, 200, 'Seattle'),
       (3,  5, 300, 'Chicago'),
       (4,  6, 400, 'Seattle'),
       (5,  7, 500, 'Chicago'),
       (6,  8, 600, 'Seattle'),
       (7,  9, 600, 'Seattle'),
       (7, 10, 600, 'Chicago'),
       (8, 11, 400, 'Chicago'),
       (8, 12, 400, 'Seattle'),
       (9, 13, 300, 'Chicago'),
       (9, 14, 300, 'Seattle');

Before explaining the technique, let us take a look at the final result. We want to obtain this PivotTable:


The notable aspects to look at are only two:

  • There are three levels in the hierarchy but, since the first tree, starting with Annabel, has a maximum depth of two, we don’t want to see the third level, which does not contain useful information.
  • The total of each node is the sum of all of its children plus the value produced by the node itself. If you look, for example, at the value of Brad in Chicago, it is 700.00, even if Vincent has produced 500,00. This means that 200,00 has been produced by Brad.

We are going to reach the final result step by step, starting from a very basic visualization and then solving the various issues, as they will become evident.

If we load the two tables inside PowerPivot, setup the relationships and create a PivotTable, we will end up with this:


The totals are correct, but don’t take into account the hierarchy, they represent only the total sales of each individual. Said in other words, PowerPivot has no knowledge at all of the existence of the self-relationship and behaves in a classical way, aggregating values by node. In order to create a hierarchy, we will need to add columns to the tree table that let us slice data following the Parent/Child hierarchy. There is a well-known technique to use, which is called "Naturalized Parent/Child” and that requires to:

  • Define the maximum depth of the hierarchy. Since in our case we have three levels at maximum depth, we can start with a hierarchy with some free space, let us say that five levels are good.
  • Create a column for each level which contains the parent of the node at that level.

Since this is a classical technique, I am not going to explain it in full detail. The result can be obtained with a SQL query that implements recursive CTE like this:

DECLARE @KeyLength INT = 3;

    FindNodePaths AS (
            NodeId    = NodeID, 
            NodePath  = CAST (' ' 
                              + RIGHT (REPLICATE ('0', @KeyLength) 
                              + CAST (NodeID AS VARCHAR (100)), @KeyLength) AS VARCHAR (100))
        FROM Tree WHERE ParentID IS NULL
        UNION ALL
            NodeId    = Tree.NodeID, 
            NodePath  = CAST (NodePath + ' ' 
                              + RIGHT (REPLICATE ('0', @KeyLength) 
                              + CAST (Tree.NodeID AS VARCHAR), @KeyLength) AS VARCHAR (100))
        FROM Tree
            INNER JOIN FindNodePaths AS Parent ON Parent.NodeID = Tree.ParentID
    ComputeParentNodeIdAtLevels AS (
            NodeID                = NodeID,
            NodePath              = NodePath,
            ParentNodeIdAtLevel1  = SUBSTRING (NodePath, 2, @KeyLength),
            ParentNodeIdAtLevel2  = SUBSTRING (NodePath, (@KeyLength + 1) * 1 + 2, @KeyLength),
            ParentNodeIdAtLevel3  = SUBSTRING (NodePath, (@KeyLength + 1) * 2 + 2, @KeyLength),
            ParentNodeIdAtLevel4  = SUBSTRING (NodePath, (@KeyLength + 1) * 3 + 2, @KeyLength),
            ParentNodeIdAtLevel5  = SUBSTRING (NodePath, (@KeyLength + 1) * 4 + 2, @KeyLength)
        FROM FindNodePaths
    NodeId   = C.NodeId,
    Node     = T.Node,
    Level1   = T1.Node,
    Level2   = T2.Node,
    Level3   = T3.Node,
    Level4   = T4.Node,
    Level5   = T5.Node
    ComputeParentNodeIdAtLevels C
        LEFT OUTER JOIN Tree T  ON T.NodeID = C.NodeID
        LEFT OUTER JOIN Tree T1 ON T1.NodeID = C.ParentNodeIdAtLevel1
        LEFT OUTER JOIN Tree T2 ON T2.NodeID = C.ParentNodeIdAtLevel2
        LEFT OUTER JOIN Tree T3 ON T3.NodeID = C.ParentNodeIdAtLevel3
        LEFT OUTER JOIN Tree T4 ON T4.NodeID = C.ParentNodeIdAtLevel4
        LEFT OUTER JOIN Tree T5 ON T5.NodeID = C.ParentNodeIdAtLevel5

This code, even if it seems complex, is indeed pretty simple. Moreover, I did not even try to make it optimal, the intent is educational, so I preferred to use suboptimal code to better show the technique:

  1. The first CTE function (FindNodePaths) will compute the node paths for each node, recursively traversing the tree thanks to the great feature of CTE in SQL Server. It is a standard tree traversal code.
  2. The second CTE (ComputeParentNodeIdAtLevels) computes, for each node, the keys of the nodes that are to be used for the Level from 1 to 5. In case you need more than five levels, it is pretty easy to adapt the code just by adding some columns.
  3. The last SELECT simply translates codes into names, since they are much better to show in reports than codes.

This SQL code can be used straight as the source for a PowerPivot table and will return this table:


Now, we can use the various Level columns to browse the PivotTable and… well the result is not very sexy. Please note that I removed the city of the invoice from the following reports, to make it simpler to look at the PivotTables.


Even if the values are correct, and this time they take into account the hierarchy, there are a few issues:

  • Annabel has an empty child (the first one), which is the value of Annabel herself. This happens for all the nodes, up to the level selected. In the figure, I used three levels. Moreover, there is no evidence of the fact that the empty nodes are, indeed, the same value as their parent.
  • All three levels are shown, even when they are not useful.

Let us start with solving the first issue. Who are those empty nodes? In the levels, we used NULL for Level2 of Annabel since the node of Annabel has a father at level 1, which is Annabel herself, but no father at levels greater than one. We can assume, with no loss in information, that Annabel is the father of herself for all levels. The same applies for any node: once a node reaches its last level, all the following ones should repeat the node name as its father. Doing so, we will remove all the NULL from the levels, removing empty nodes.

To obtain this result, it is enough to replace the last SELECT of our query with this:

    NodeId   = C.NodeId,
    Node     = T.Node,
    Level1   = COALESCE (T1.Node, T.Node),
    Level2   = COALESCE (T2.Node, T.Node),
    Level3   = COALESCE (T3.Node, T.Node),
    Level4   = COALESCE (T4.Node, T.Node),
    Level5   = COALESCE (T5.Node, T.Node)
    ComputeParentNodeIdAtLevels C
        LEFT OUTER JOIN Tree T  ON T.NodeID = C.NodeID
        LEFT OUTER JOIN Tree T1 ON T1.NodeID = C.ParentNodeIdAtLevel1
        LEFT OUTER JOIN Tree T2 ON T2.NodeID = C.ParentNodeIdAtLevel2
        LEFT OUTER JOIN Tree T3 ON T3.NodeID = C.ParentNodeIdAtLevel3
        LEFT OUTER JOIN Tree T4 ON T4.NodeID = C.ParentNodeIdAtLevel4
        LEFT OUTER JOIN Tree T5 ON T5.NodeID = C.ParentNodeIdAtLevel5

By simply adding the COALESCE, the final table becomes this:


Using this new format for the table, the result is much nicer:


Now all the empty nodes disappeared from the PivotTable, when a node is a leaf it is repeated in all subsequent levels with its name. Still not what we really wanted to show, but definitely better than before.

All what I said up to now, is pretty well known in the BI world. Thus, the interesting part of the post starts here. Sorriso

Our second goal is to hide all the useless levels. For example, the two “Annabel” under the first one, should be hidden, since Annabel is a level 1 node and should disappear from the PivotTable when Level 2 is browsed. Said in other words, all nodes of level of N-1 should not be made visible at the level N of the hierarchy.

Now, let us try to understand first why the two undesired rows appear in the PivotTable. If we look at the filter contexts of the first three cells of the PivotTable, we get this:


The filter context changes for the three cells. The first cell computes 4 rows, the second and the third ones compute only one row, which is the Annabel row. It is clear that we cannot leverage the fact that there is only one row in the filter context to hide the row because, doing so, we will compute incorrect results for all the leaves. The number of rows in the filter context is note the way to go.

Let us focus on the only row with of Annabel, with NodeID equal to six. It should be computed in the first level and hidden in the next ones. Thus, the DAX formulas will work on the very same row, but with different filter contexts. The key to solve this scenario seems to be to find a way to detect the current filter context and then, if the row should be hidden under the current filter context, find a way to hide it. Unfortunately, in PowerPivot there is no way to compute the current filter context. We can leverage the VALUES function, which will return the distinct values of a column in the current filter context but, in this specific scenario, VALUES (Tree[Filter1]) will return “Annabel” for all the three Filter columns in all of our three contexts. Said in other words, inside DAX there is no way to discriminate computation based on the filter context, we can only discriminate based on the column values and, in our scenario, the column values are always the same.

If we want to be able to discriminate the behavior of DAX in these three filter contexts, we need some column that has a different value in the different filter context. But, in order to do that, we need to have more than one row with the same set of attributes filtered by the contexts and with a new column that has different values in the various filter contexts.

Think in this way: one row contains Annabel and a column which says: “show me in level 1”, another row contains Annabel too but the column will say: “show me in level 2” and so on. This change in the data model will let us discriminate among the various instances of Annabel and let us decide whether a row should be made visible at each level. With this basic idea in mind, we can move a step further and note that we don’t need all of the filter contexts to be different, since we only need to discriminate between levels in which Annabel should be visible and levels in which Annabel should be hidden. Thus, two rows for Annabel are enough, we don’t need a row for each level.

We are moving toward the final solution but, before showing it, it is worth explaining the technique in more detail. If we duplicate the row of Annabel, making some mix between the last two queries (the one with COALESCE and the one without it), we will get this:


Now, these two rows are identical but the first filter context shows both of them, while the second and the third, filtering Level2=”Annabel”, will hide the first row. Now, inside DAX, by counting the rows, we will be able to discriminate between the two situations and we will be able to hide the second instance of Annabel if the first row is not visible. Well, we still need a way to hide the row, but we will come to that later.

There is still a small problem we have to face before duplicating rows: NodeID is a key in the table and it cannot contain duplicates. Moreover, in the first filter context we want to count Annabel only once, even if there are two rows visible. This is easily accomplished by setting the NodeID of the duplicated row set to a negative value, which is not conflicting with any other ID and does not relate to any row in the fact table.

Since it is clear that the duplication need to affect all the rows (each single row can be visible at a level and hidden at another one), the last SELECT of our query should become:

    NodeId = C.NodeId,
    Node   = T.Node,
    Level1 = COALESCE (T1.Node, T.Node),
    Level2 = COALESCE (T2.Node, T.Node),
    Level3 = COALESCE (T3.Node, T.Node),
    Level4 = COALESCE (T4.Node, T.Node),
    Level5 = COALESCE (T5.Node, T.Node)
    ComputeParentNodeIdAtLevels C
        LEFT OUTER JOIN Tree T  ON T.NodeID = C.NodeID
        LEFT OUTER JOIN Tree T1 ON T1.NodeID = C.ParentNodeIdAtLevel1
        LEFT OUTER JOIN Tree T2 ON T2.NodeID = C.ParentNodeIdAtLevel2
        LEFT OUTER JOIN Tree T3 ON T3.NodeID = C.ParentNodeIdAtLevel3
        LEFT OUTER JOIN Tree T4 ON T4.NodeID = C.ParentNodeIdAtLevel4
        LEFT OUTER JOIN Tree T5 ON T5.NodeID = C.ParentNodeIdAtLevel5
    NodeID = -C.NodeId,
    Node   = T.Node,
    Level1 = T1.Node,
    Level2 = T2.Node,
    Level3 = T3.Node,
    Level4 = T4.Node,
    Level5 = T5.Node
    ComputeParentNodeIdAtLevels C
        LEFT OUTER JOIN Tree T  ON T.NodeID = C.NodeID
        LEFT OUTER JOIN Tree T1 ON T1.NodeID = C.ParentNodeIdAtLevel1
        LEFT OUTER JOIN Tree T2 ON T2.NodeID = C.ParentNodeIdAtLevel2
        LEFT OUTER JOIN Tree T3 ON T3.NodeID = C.ParentNodeIdAtLevel3
        LEFT OUTER JOIN Tree T4 ON T4.NodeID = C.ParentNodeIdAtLevel4
        LEFT OUTER JOIN Tree T5 ON T5.NodeID = C.ParentNodeIdAtLevel5 

You can see that I added a UNION ALL and the new rows will contain NULL at the level at which the row should be hidden and the negated ID avoid duplicates and false relationships. The final result looks like this:


Now the data model is ready to be queried, time to move to the PivotTable and take a look at the last points to solve:

  • Detect when a row should be hidden
  • Find a way to hide it

Let’s go back to our PivotTable, add a measure called CountOfNodeID with the simple formula COUNTROWS (VALUES (Tree[NodeId])), we get this result:


I have highlighted the cells that need to be made visible, all other ones should be hidden. Moreover, this figure makes evident the different filter contexts (now Annabel appears under five different filter contexts, one for the root and two for each level each, where the level is Annabel of NULL). The other thing to note is that all the rows that need to be hidden have a COUNTROWS of 1. This is due to the fact that there are always at least two rows in the filter context, until we reach the point where the NULL value in the level is a discriminant. Then, the two rows are separated into two different contexts, each one of which shows only one of the two rows.

Well, the technique is now clear: we just need to hide all the rows that have a CountOfNodeID that equals to 1. Oh… yes, we still need a way to hide the rows. Ok, last point. Sorriso

If we remove the CountOfNodeID from the PivotTable, we will get this:


See what happened? All the rows that do not contain a value for Amount disappeared. This is due to the automatic NONEMPTY: all rows with empty values are automatically removed from the final result. Thus, if we put BLANK in a measure, whenever we want to hide a row, that row will automatically disappear from the PivotTable. Let us create a measure, called SumOfAmount, with this formula:

=IF([CountOfNodeID] > 1, SUM (Invoices[Amount]), BLANK ())

Adding it to the PivotTable results in this:


You see that only highlighted rows contain a value for the SumOfAmount measure. Thus, leaving the SumOfAmount measure alone in the PivotTable, we will reach the final result:


Et voilà, the visualization of Parent/Child looks exactly as it looks in SSAS, with no useless nodes. Since the value is computed with a measure, it can be easily sliced using the city of the invoice, as I did in the first figures of this post.

Before going to the end of the post, there are still some small considerations that I want to share.

First. If you want to make it evident the contribution of each node to its total (i.e. Brad has a value of 400 which is not shown in the report, it need to be deduced by the difference between Chris+Vincent and the total of Brad) you can easily add one visible level to the hierarchy, so that each node starts to be hidden one level later. This can be easily accomplished adding a COALESCE to the second part of the query. Another interesting technique is to differentiate between nodes that behave as aggregators and nodes that contain values. In our case, we will end up with two Brad: one who has invoices and another one who is the aggregator. This latter technique, IMHO, leads to a better data model and user experience.

Second. The speed of Parent/Child with this technique is great. In SSAS Parent/Child suffered from the lack of aggregations but, since Vertipaq is superfast without any aggregation, it is still superfast with this implementation of Parent/Child, that behave exactly as any other hierarchy. Try it with your data and share the results, I have not performed many tests on performance and I am eager to see some value published by my readers.

Third. If you need to get the aggregated value in a measure without having the hierarchy displayed on a PivotTable, all of the Level column are of no help. To accomplish that, you need to push the NodePath column computed by the first CTE to the final hierarchy, like this:


And then, to compute the aggregated value of a node, simply sum up all the rows that contain the NodeID somewhere in the path, using the DAX FIND function which works fine for this. Having used a space as a separator, we will not incur in wrong substring detection. A measure computed in this way can then be used to perform further computation, even if the hierarchy is not visible in the PivotTable.

Fourth: the count of nodes, that determines the visibility of a node, is carried on on the dimension table only. Thus, it runs very fast because there is no need to access the fact table to determine whether a node should be made visible or not. This means that the addition of negative ID in the hierarchy does not affect performances, since those nodes will never be computed against the fact table. Moreover, we need to duplicate the dimension table but this is not very worrying since, normally, dimensions tend to be pretty small (i.e. less than one million rows).

This is a long post so I think a small recap might be useful:

  • Since a hierarchy cannot be handled by PowerPivot we need to flatten it using a fixed number of levels. I think ten levels would suffice most scenarios.
  • The flattening of the hierarchy need to be done with SQL, since it handles recursion in a snap, while PowerPivot is unable to do this.
  • To be able to detect nodes to be hidden, we had to duplicate all nodes in the hierarchy using the Level as the discriminator.
  • To hide a row, it is enough to blank ALL the measures shown in the PivotTable and the row will disappear.

And, the most important lesson here is: when formulas tend to look too much complicated, a simple change in the data model will often result in a much cleaner solution. You have seen that the DAX formulas used in this post are really plain, the key of the solution is in the data model, not in the formulas.

To go to a conclusion: Parent/Child hierarchies are not present in PowerPivot, nevertheless they can be implemented pretty well, even if with some SQL and DAX acrobatics. The final result is very nice, elegant as the SSAS implementation is. It works fine and it will benefit from the tremendous speed of the Vertipaq engine providing a great user experience. At the end, it might be the case that PowerPivot handles Parent/Child hierarchies better than SSAS does.

This is just another example of what PowerPivot can do for your reporting needs. If you want to touch the real Karma of PowerPivot, don’t miss the opportunity to follow one of the workshops I and Marco Russo are bringing all over Europe in the next few months.

You can find more info on Hope to see you  there!

Published Tuesday, February 15, 2011 9:00 AM by AlbertoFerrari



gosta munktell said:

Thank you Alberto

I have playing around with the same ideas but you put it

"in print" very nicely.

First I have a very simple question. To get the hierachy in one PP table is the first step. Is it possible to present more than one column in a slicer or do we have to concatenate columns?

I have tested another approach which is like QlikView an other tools

work. You can in one slicer click on a level an defold the childer.

I you click on a childer you wrap up to the next level above. This is how the world works. I have done some work with VBA tree view control but this is a "dead end" as the result needs Excel with VBA

which is not available using Sharepoint as dashboard to my knowledge or?

Best regards

Gosta Munktell

February 15, 2011 9:27 AM

AlbertoFerrari said:


AFAIK, PowerPivot slicers are able to filter one column only and, because in PowerPivot there is not currently the possibility to create hierarchies with many columns inside, you cannot use slicers to browse the tree hierarchy.

February 15, 2011 9:49 AM

Alejandro Mesa said:


Thanks for sharing your knowledge with us.

I am not familiar with PowerPivot, but I think I understand a little bit your approach since, as you mentioned, it is something known in the BI community.

Please, correct me if I am wrong in my understanding.

Your approach is to flatten the hierarchy (even out), and back filling the empty levels with the last known node. For example (Annabel, Catherine, Catherine, Catherine, Catherine). The rest is to use the power of DAX to filter and hide all the useless levels.

My question is, if it is possible with DAX to compare two levels value?

If current value, in this case the name of the sales person, is equal to the value of previous level, then hide it. I know it will be better to compare the keys and not the names.



February 15, 2011 10:04 AM

AlbertoFerrari said:


The real pain here is to compute the "current level" of a cell. It is easy to see that Catherine is a level 2 row, and this can be computed in a calculated column pretty quickly.

But then, how do you decide, inside a single cell of the PivotTable, that you want to hide that row? It need to be hidden when the cell is at level 3, and shown when the cell is level 2 and, from the database point of view, you are accessing the very same row, unless you perform the duplication.

The whole technique is aimed to compute the "browse level" of the PivotTable cell, after that, everything is easy. :)

That said, accessing other levels should not be difficult, I am currently working on unary operators with P//C, I think I will publish a post maybe next week about it and, there, I will do exactly that.

February 15, 2011 1:55 PM

Eddy Nijs said:


Many thanks for sharing your knowledge. The article is very instructive and to the point. It helped to solve our problem.

It must have been a realy brain teaser to get the final result.

May 18, 2011 5:51 AM

Lou108 said:

Hi Alberto

Very neat as per your usual excellent standard!

What happens now with "Denali" PowerPivot that handles hierarchies (but, as I understand, still doesn't handle out-of-the-box parent-child relationship)?

My guess is that your modelling work will still necessary, but we will get slicers able to handle the hierarchy, or not?



July 18, 2011 10:28 AM

AlbertoFerrari said:


In Denali life is much easier! Parent/Child behavior is handled out of the box. Please check this post, from Kasper, explaining the basics of P/C in Denali.

July 18, 2011 10:37 AM

Alberto Ferrari said:

Some time ago, I wrote a post about how to handle Parent/Child hierarchies in PowerPivot 1.0. It was

July 18, 2011 6:15 PM

Nivas PM said:

i am using power pivot extensively but this week i stuck up with following issuee....

when we use PowerPivot and for "Sub Total" Design you can go to DESIGN tab and then can select SUBTOTAL button and upon clicking you will get the options like "Show subtotal at bottom of the group" and "Show subtotal at top of the group" etc. to match with my finance teams OLD templates i have to use "Show subtotal at bottom of the group" but the issue is that ...TOGETHER WITH SUBTOTAL VALUE & GROUP NAME AT BOTTOM IT ALSO SHOWS THE SAME GROUP NAME AT TOP OF THE ITEMS ALSO...WHICH IS KIND OF A "REPEAT"  AND I HAVE TO HIDE THAT...!!!!!!!!!!WHEN I HIDE ROWS MANUALLY IT EVENTUALLY HIDING some ROWS WHICH are opening  ON EXPANDING any of groups.....can you give me solution for this....?  YOU GOT CONFUSED WITH MY QUESTION i will put it in simpler way 1)"is there is any chance to get Group name & subtotal ONLY BELOW to a group only WITH OUT SAME GROUP NAME REPEATING ABOVE THE GROUP ..? this is my main issue.. if u can also look at following question would be also g8...

2)this will not solve my issue but there is any chance to enable/disable the expand/collapse for certain level parents...

means i have 5 level grouping for the accounting and i want expand/ collapse only for the group level 4 and rest of the main 1 to 3 group should be without expand/collapse feature.... thanks in advance

August 3, 2011 9:27 AM

AlbertoFerrari said:


This question has nothing to do with P/C and/or the topic of this post. Thus, I think asking it in the MS forums will give you more chances to get an answer. This is just the wrong place where to ask it.

For what concern my knowledge, I don't know the solution to neither of your questions but I guess that some Excel guru could find something to help you.

August 3, 2011 9:44 AM

Nivas PM said:

thanks Alberto for  you answer. i will check in MS forums for this question. i was using PowerPivot and Parent/Child hierarchies upto 5 levels in this reports and i had to handle that  extensively  in this project and that was the reason i posted it here.

August 4, 2011 3:18 AM

michael vardinghus said:

I have never seen a more elegantly description of almost anything in my entire life. And I'm not young anymore.

Tx, Michael

September 30, 2011 5:02 AM

AlbertoFerrari said:


Thanks a lot. This is the best comment I have ever received and, to be honest, I am very proud of your kind words.

Really appreciated.

September 30, 2011 5:16 PM

Vijay said:

Thanks you so much, really helpful.Implemented in my project

October 3, 2011 9:24 AM

Nicky said:

Thanks Alberto (and Marco) for helping me out with these hierarchies. With a few tweaks (see my blog) it worked out fine for me!


October 21, 2011 7:18 AM

SwainA said:

Hi Alberto,

thanks for this gem of a blog.

I dont have Excel 2010 and attempting this using Excel 2007. Do you know if I could implement your CountofNodeID>1 in just a normal Excel 2007 pivot table?

April 13, 2012 4:20 AM

AlbertoFerrari said:


This technique works fine in PowerPivot, and PowerPivot requires Excel 2010 to run. I don't think you can do anything similar in a normal PivotTable, mainly because DAX is missing there.

April 13, 2012 4:27 AM

RP said:

One problem I noticed with the Power-Pivots is that, if I expand or collapse any single node, it does the same on every other node. In other words, for example, if I open any single node, it opens all the nodes at a similar level. Any suggestions? Is there a way to expand or collapse one node at a time?

July 30, 2012 4:24 PM

RP said:

This is a continuation of my previous question. A good example for my question would be - in the above reports, is it possible to open the node for Annabel alone without opening the node for Bill. In my case, if I open the node for Annabel, it also opens the node for Bill.

July 31, 2012 10:31 AM

AlbertoFerrari said:

@RP, It looks strange, I am used to collapse single nodes without any problem. In PowerPivot, P/C are just standard hierarchies.

Maybe you can send me your workbook, so that I can take a look at it?

July 31, 2012 10:41 AM

RP said:

  Thank you a lot Albert for your input. What I am noticing is, for example, if we have 3 level hierarchy with Level 1, Level 2, and Level 3. For each row in level 1, there can be multiple rows in level 2. Similarly for each row in level 2, there can be multiple rows in level 3.If we look at the above report - Annabel and Bill are at level 1; Catherine and Brad are at level 2; Chris and Vincent are at level 3.


  Let us look into this scenario - suppose Brad has to report to Annabel as well as Bill. And so, Brad will be displayed under Annabel and Bill.In this case, when I expand or collapse the node for Brad under Annabel, the other node for Brad which is under Bill also collapses or expands accordingly.


  If we have one or two repeating values then easily we can manage. In my case, I have the same value (at level 2) that is applicable for several thousands of rows in Level 1.

  Whenever I expand or collapse the node that is corresponding to the specific value at level 2, it also expands or collapses the corresponding value at level 2 under each of the thousand level one rows.  

July 31, 2012 1:34 PM

AlbertoFerrari said:

@RP, that sounds an interesting case... again, the best option would be to share a workbook to make some test, I don't think there is an easy solution, but maybe we can find one. :)

August 1, 2012 5:05 AM

DanOrc said:

Absolutely great job! Thanks a lot, I owe you a big coffee!

September 12, 2013 10:30 AM

John Pradeep said:

Excellent article. What would be the formula for SumofAmount that I should use,

where I need just the value at the top Level. So in your example i want to finally see in pivot,

Annabel         600

  Catherine    1200

  Harry        800

  Michael      600

September 18, 2013 5:08 PM

Samuel said:

Thank you very much. This was very very helpful.

September 18, 2013 5:55 PM

Badrul said:

I'm trying to work out how to do calculations that utilize the totals at the higher level.

So if if have the following hierarchy

Total Assets  500

   Current Asset  300

   Long-Term  Asset 200

Total Liabilities  300

  Loan   300

Total Equity

 NPI   100

 Balancing figure xxx

Then I want to replace the xxx (which be zero to begin with) with a formula that is "Total Assets - Total Liabilities - NPI

Is that sort of calculation possible?

November 14, 2013 3:51 PM

Mubin said:

Hi Alberto,

This is really good,

but how can i do flattern of hierarchy upto n level?

Please provide your suggestion.

December 10, 2013 9:31 PM

Mohaned said:

Thank you for great article. It helps a lot

using CTE is complicated so instead i used regular while loop to create a natural presentation of the tree and it works perfect.

May 14, 2014 2:23 AM

Evgeny said:

Fantastic Help. Thanks

May 5, 2015 11:04 AM

Priyanka said:


How can I filter out the blanks if I am using the Hierarchy in a report filter?

Thanks in advance

July 13, 2015 7:59 AM
New Comments to this post are disabled

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Privacy Statement