Report Designer - Job Cost Report - Exclude Certain Categories

SUGGESTED

I am trying to customize a standard job cost report in Report Designer. It currently details the costs by cost codes and categories. It is totaled at the bottom, where then a % is added on to account for overhead. We are now allocating some of the overhead costs to the jobs in their own separate categories, so I would like to add another total at the bottom (which I can easily add) to exclude these category costs that we have already moved from overhead since we do not want to calculate overhead costs on these additional costs.

When I try to create a formula based on the categories I want excluded, it doesn't do anything. For example, I have tried IF(JC category <> "S", JTD cost(category) or JTD Total - IF(JC Transaction = "S", Amount(transaction), and several other formulas of the like. I have tried using the JC-Transaction file, the JC-Category file, and a few other files. Does anyone know how to get certain categories excluded from a total or if it is even possible? I don't want to condition the report to exclude them, because I do want these to still show up on the cost reports, just not in the total the overhead % is based off of. Thanks!

  • 0
    SUGGESTED
    Assuming your report is designed to print all Category records, you would need to use the ASUM function to calculate a total that excludes the "S" category (per your example). ASUM is calculated on an initial read through the records before the printing begins. You can find the syntax in the Help files. You would include your "IF" statement within the ASUM syntax: ASUM (Job[category], IF (Category <> "S", JTD cost [category], 0)) would give you a Job total for all Category JTD Costs EXCEPT Category "S". You would then place this formula in the total section of your report and have it print once per job with the totals.