How to filter tasks for report creation

MarkFX

Dear all,

I have a folder full of projects. Each project is being "hosted" in its own subfolder, with two sheets each; "PROJECT METADATA" and "PROJECT PLAN". I created this structure based on the idea of the "Project Management Office" template.

The connections between project plan and meta data and project overview (intake sheet) works well.

So now I am trying to create a report that I can use for resources planning (as card view) As every two sheets in every project folder are being named the same "PROJECT PLAN" and "PROJECT META DATA" as I create these from templates, I already created a help column in the project plan, that provides the name of the project and the name of the task (parent) and sub tasks, using the following formula: =JOIN(ANCESTORS(Tasks@row), " - ").

Where the upper most task is the name of the project. As every template / project plan does have exactly the same tasks, I would not be able to tell apart the different tasks in a report. (Where the red mask would be the project name).

Unfortunately the report does not allow me to have the "primary" column in my report. I renamed it to "Tasks", but it is just not available to select.

The second problem is, that the report shows me ALL tasks, so also the parents, but as these are not being assigned to anyone but are only used to group sub tasks, I don't want them in my report.

I can not just filter on empty tasks or not assigned to anyone, as some open sub tasks have not yet been assigned.

I am very very sorry, if this sound confusing, but I try my best to explain. Any hint or recommendation or question is highly appreciated. Thank you! 😘

Ray Lindstrom

I understand about the helper column you're using.

1.) Let's look at your first problem: "Unfortunately the report does not allow me to have the "primary" column in my report. I renamed it to "Tasks", but it is just not available to select."

Which column are you referring to as "primary"? Are you referring to the helper column with the ancestors formula in it? The actual Primary column is automatically included in Reports, and you can't exclude it (you can hide it). If you're talking about the helper column with the ancestors name, that should be available to select for your Report. I just tested it (as a text/number column), and I was able to pull that column in no problem.

If I'm not understanding right, please let me know.

2.) Let's look at your second problem. You don't want your Parent rows included in your Report. I use a checkbox type helper column to accomplish that. I name the column "Parent Row Check" although you could name it anything. I then use this formula as a column formula. =IF(COUNT(CHILDREN()) > 0, true, false)

From the report, I would then set a filter that says "Parent Row Check" is "Unchecked". That way I only get the child rows, and no parent or ancestor rows.

Let me know if this helps, and if you're still hung up. We'll get you through this!

Ray Lindstrom

The Primary column is not available to select in Reports.

Instead it's included by default, and there's no option to exclude it.

It shows up as "Primary", but you can change the name of it. It's the only column in Reports that you can change the name of.

MarkFX

Thank you @Ray Lindstrom ! 😊

I know, and that is why I am using an additional help column. But how would I be able to filter the tasks to only have the child tasks. As for resource planning, the ancestor tasks yre veryy confusing and are messing everything up, when I try to use the report as a card view for resources planning...

I am very afraid that my work now doesn't pay off and I have to start all over again... 😥

Ray Lindstrom

I understand about the helper column you're using.

1.) Let's look at your first problem: "Unfortunately the report does not allow me to have the "primary" column in my report. I renamed it to "Tasks", but it is just not available to select."

Which column are you referring to as "primary"? Are you referring to the helper column with the ancestors formula in it? The actual Primary column is automatically included in Reports, and you can't exclude it (you can hide it). If you're talking about the helper column with the ancestors name, that should be available to select for your Report. I just tested it (as a text/number column), and I was able to pull that column in no problem.

If I'm not understanding right, please let me know.

2.) Let's look at your second problem. You don't want your Parent rows included in your Report. I use a checkbox type helper column to accomplish that. I name the column "Parent Row Check" although you could name it anything. I then use this formula as a column formula. =IF(COUNT(CHILDREN()) > 0, true, false)

From the report, I would then set a filter that says "Parent Row Check" is "Unchecked". That way I only get the child rows, and no parent or ancestor rows.

Let me know if this helps, and if you're still hung up. We'll get you through this!

MarkFX

Dear Ray,

thank you! I have to admit, today I learned a lot. 😃

1) "Unfortunately the report does not allow me to have the "primary" column in my report. I renamed it to "Tasks", but it is just not available to select."

I did now find out that my primary column is being displayed in the report, but nit by the name I gave it ("tasks"), but by the original name "primary". That's why I could not find it and messed everything up.

2) Yes, in the meantime, I did find a solution for that. Based on another community post, I added another help column, that is counting the children for each task. Then in the report I do only display rows that have zero children; so any parents are not being provided in the report.

I am very sorry @Ray Lindstrom if I didn't come back to this post, early enough, but I just finished correcting my setup. - Still you helped me a lot, as you were pushing me into the right direction and already clearifying the issue with the "primary column". 😉

Now I am already facing the next issue. As I am using templates to create my project plans, I sometimes have tasks in my templates that I would later remove (delete the rows' contents) but this indeed only removes the content but not the formulas. So now these empty rows still appear in the report. 😪 So I do understand that I in fact have to "Delete"(!) these row, and not only delete the content...

Slowly but surely my project management system grows... 😂

Thank you very very much for your kind support @Ray Lindstrom ! 👍️

Ray Lindstrom

@MarkFX,

Great news, I'm so glad you were able to get through the obstacles!

Your filter criteria and helper column formula are another flavor of the same idea I suggested with the "Parent Row Check". This isn't uncommon. There's often multiple ways to arrive at the same solution/result.

All the best to you, sir, and good luck in your solutioning!

-Ray