View Only
  • 1.  How to Display Report Formula Summary in Grid Edit Header

    Posted 11-24-2021 18:12

    - I have a child table (Tasks) grid edit report (grouped by Phases) that is embedded on a Parent (Projects) Main Form. The full version of the child table grid edit report is available from the parent main form also.

    - The phase headers on the Full Report are collapsible by group (Phase) The picture attached show ALL since this is coming from the embedded report, but the FULL report is grouped by phase. The phase headers naturally demonstrate total for the group (Red Boundary in upper right of attached .jpg)

    - I created Report Formula to calculate the # tasks complete. I also created a Report Formula for Total Count but do not need this Report Formula because the total is already provided inherently. I added these two columns to the grid edit report (Yellow hashed boundary in upper right of attached .jpg)

    My problem: I would like to be able to display the # of Completed Tasks (from the Report Formula) in the Header Row for the Group (Phase) so that I can display "# complete" of n tasks. (Yellow shaded in upper right of attached .jpg). How would I do this?

    Alternative: If this is not possible I can also live with embedding a summary report, but I can't quite figure out the Summary Report settings to display as a column (see 2nd attached .jpg with is not filtered)

    Annetta Coleman

  • 2.  RE: How to Display Report Formula Summary in Grid Edit Header

    Posted 11-24-2021 18:36
    An option is to use a very new feature called Formula Queries to calculate the total number of completed tasks for that phase as well as the total number of tasks for that phase (of course just for the project, the other filters that you currently have on the report).

    If you had those two numbers as fields on every task then you could build a text formula to use to group the Tasks, so the field would be like

    List(" ", [Phase], ToText([Formula Query field for # completed]) & " Tasks Completed out of Total " & ToText([ Formula Query field for totral # of tasks] & " Tasks")

    Formula Queries are new and the syntax is a bit challenging, but they are super powerful.

    Can't just rip off the formula for you as the syntax is tricky and I would have to know all your field IDs.  If you want to learn for me or you can go looking for some one on one assistance. I don't know if the care team would be able to help you with that or not. Or you could contact me directly by email but I need to charge for my time.

    As for making an additional summary report by phase you can make a formula field on tasks which results in a numeric result of 1 if the task is completed and 0 if incomplete.  Then use that field on the Summary report.

    Mark Shnier (YQC)

  • 3.  RE: How to Display Report Formula Summary in Grid Edit Header

    Posted 11-25-2021 06:18

    Mark -

    Thanks for mentioning formula queries.   I read the article about them during an early AM coffee.   They seem very powerful * useful.   I am going to park this particular use case (which is classified as a "nice to have" for our MVP) until later this weekend so that I can burn down the "must have" list.  Oh.... I also need to cook a turkey today for family coming for Thanksgiving dinner this afternoon.   :) 

    I will definitely check out formula queries and appreciate you introducing me to them.

    Annetta Coleman