Crosstab style reporting from Gantt data

barryrowland

Looking for some help on Crosstab style reporting from Shartsheet Gantt data.

I have a Gantt project like the below ...

How can I produce a report like this one I mocked up in an Excel Pivot table after cutting and pasting and "massaging" the Gantt data? ...

Do I need the premium Pivot App (which we don't have) or is there another way?

Or as as a hybrid solution can I create "helper columns" in Smartsheet that I can then cut & paste the data to Excel for my Pivot Table to be the report?

The helper columns would be for each level 2 task (i) a column to show the level 1 task and (ii) another column to show the level 0.

I used the formula =PARENT([Task Name]@row) to get the value for (i) but what formula do I need for (ii)??

Any help is appreciated ... Barry

Genevieve P.

Hi @barryrowland

Yes, this is an instance where the Pivot App would be helpful.

However there are some ways we can get similar outcomes without it. My first question is it if needs to be vertical like this. I ask as I'm wondering if we could achieve a similar result by using a Row Report, Filter the Report so you only see level 2 rows (lowest level), then Group by the Task Name column.

This would then give you header rows for each Group so you can quickly see percentages together.

For example, this is my source data, where I have 2 helper columns pulling the top level 0 Task into one column and then the second level 1 task:

Department Formula:

=IF(COUNT(ANCESTORS([Task Name]@row)) = 0, "", IF(COUNT(ANCESTORS([Task Name]@row)) = 1, PARENT([Task Name]@row), SUBSTITUTE(JOIN(ANCESTORS([Task Name]@row)), PARENT([Task Name]@row), "")))

Document Formula:

=IF(COUNT(ANCESTORS([Task Name]@row)) > 1, PARENT([Task Name]@row))

Then in my Report I've filtered so it's only level 2 showing, and Grouped first by the Task name then next by the Department Formula column, using the Summary to find an Average. See: Redesigned Reports with Grouping and Summary Functions

You could hide that "Department Formula" column since it's repeated to only have the Task Name showing next to the Document and the Percentage.

Would this work for you?

Cheers,

Genevieve

barryrowland

Thanks so much @Genevieve P. The formula's are totally what I was looking for and they work for me too. I'm going to have to study them to understand how they're working though ;-) Report wise I'm not seeing the compactness that I was looking for but let me study your ideas a bit more and review your link. Worst case your formulas allow me a quick cut & paste to Excel to then do a Pivot table quickly there for reporting purposes. Thanks again!!

MarcoFossati0012

@Genevieve P. I've a similar problem with my project grid, the difference is that I always need to find the Ancestor=2. In your formula, if the ancestor is greater than 1, you put the Parent with Ancestor=0.

Could you kindly help me?

Thank you very much!

Genevieve P.

Hey @MarcoFossati0012

Apologies, I'm not quite sure I understand your set-up or request. Would you be able to post a screen capture (but block out sensitive data) and explain what you want to appear on each row?

Thanks,

Genevieve

MarcoFossati0012

Yes, sorry my explanation was not clear.

From my screenshot you can see my example. In the third column there's pasted your formula, in the fourth column what I desire to have: I need that the column always shows the upper Parent with Ancestor=1, for all the rows with Ancestor>=1.

Genevieve P.

Hey @MarcoFossati0012

Thank you for this image, this is really helpful!

So for this I would add 2 columns. One to bring the Parent Row Task Name into the current column:

=IF(COUNT(ANCESTORS([Task Name]@row)) > 1, PARENT([Task Name]@row))

And one to check each level and give different instructions based on that level:

=IF(COUNT(ANCESTORS([Task Name]@row)) = 0, "", IF(COUNT(ANCESTORS([Task Name]@row)) = 1, [Task Name]@row, IF(COUNT(ANCESTORS([Task Name]@row)) = 2, PARENT([Task Name]@row), IF(COUNT(ANCESTORS([Task Name]@row)) = 3, PARENT(Parent@row), IF(COUNT(ANCESTORS([Task Name]@row)) = 4, SUBSTITUTE(JOIN(ANCESTORS(Parent@row)), PARENT(Parent@row), ""))))))

This maintains Level 2 as the value to bring into Levels 2 - 5, with a maximum of 5 levels. Will that work for you?

Cheers,

Genevieve