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 aRow Report,Filterthe Report so you only see level 2 rows (lowest level), thenGroupby 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 anAverage.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!!