Summary Reporting… Without Summarizing
Do you have a summary (cross-tabbed) reporting need where you either need to
- Display the sum for a single, filtered, value rather than summing two values?
- Display details for single rows, without aggregating them, but a tabular report is too much clutter?
Here are two tricks to creating custom calculations that are for unique, but frequent, circumstances that we’ve encountered at Top Step LLC.
Custom Calculation (Value * 1)
Custom calculations can be multiplied by 1 if you create a constant detail field, set to “1”.
Do this by clicking “Custom Detail Field” on the Custom Calculation form, and choose the transaction table for which you wish to have a constant.
Create a new field:
Important Reminders:
- The field must be a “transaction” record. Meaning it will not help if you create a Project constant, or User constant.
- This can only be multiplied against the values you are wishing to sum.
- For example, Invoices can be multiplied by a charge constant of “1”, while Revenue can only be multiplied by a revenue transaction constant of “1”.
You can now apply this Custom Detail field in Calculations to sum values using Filters:
Add the Custom Detail (Constant) Field to a Report
Adding your newly created constant field to a report will force all rows to display on a report, even if their value is zero!
Important Reminder:
- This is only true if at least one transaction exists for the period reported on
- For example, can build an Hours-by-Task report to display all tasks on a summary report, even if there have been no time entries to some tasks
- By adding a Daily Task Assignment constant to the report, as long as the tasks have just one assignee, they will display, regardless of time entries
Notice that normally, Design through Deploy would be withheld from the report due to zero time entries, but because one task assignment exists, they are forced into the Summary Report.
