Help with report - activities as columns and last timeline update

[chrisnutt]

Hi There,

I have a need to create a report that a project manager will use to feedback to management. I've created a nice concise progressive capture and business process using custom fields and activities to answer questions at certain stages. That works well. The problem is that I am now struggling to report on it.

Attached is my report output (and an edited version of what I would ideally like to see) as of right now and screenshots of my tables and filters. More info can be provided if needed.

What I am trying to achieve is to have one row per request, and include the last update in the timeline made by a human, the time of that update and to have each activity as a column with the outcome in the row. At the moment I am getting a row for each update and for each activity.

I hope that makes sense.

Is this possible? Is there a better way to achieve what I am trying to do? Or indeed, is there a better way entirely?

Thanks in advance

Chris

IT Projects issue log report_937_CN.xlsx

IT Projects issue log report_937_CN-edit.xlsx

[chrisnutt]

Hi All,

So part of this has been resolved in the below thread. I still need help in getting the last human update only to show against the request in a report.

Chris

 

[Victor]

On 8/2/2018 at 1:48 PM, chrisnutt said:

the last human update

The problem getting the last update when is that it requires subqueries as you are retrieving data from two or more tables... and that's simply not possible. You can get a list of all updates, filtered as you wish but it will be all updates of that type...  

[chrisnutt]

Hi @Victor

I'm afraid I don't understand fully what you are saying above. Can you expand?

Thanks

Chris

[Victor]

@chrisnutt so, if you need a list of requests and for each of these request you need to only retrieve the latest update, the way you can do this in an SQL query is to parse the requests table and for each parsed request parsed, you need to query the activities table to retrieve only the last update for that request (querying the activities table for the last update would be the subquery). There is no such functionality (i.e. subqueries) in the current reporting engine.

[chrisnutt]

Thanks, @Victor

I see what you mean now.

You say the current reporting engine. Does this mean there will be a different reporting engine? I am aware that this one is still relatively new. But the ability to get something that I could do with a query would be beneficial.

Thanks

Chris

[Victor]

@chrisnutt - apologise, is just poor semantics from my part ... I meant basically with current functionality you cannot do this... 

[chrisnutt]

@Victor No worries!