Reporting Module - Nesting a Summary Table that Repeats Each Ingredient

[Eric_Meyer]

Hi all –

I’m working on a production report for a client and I’m running into a challenge with campaign-level summaries.

Context

• Main data source is a Named Query (MainQueryDetails) that returns all fields I need: Work Order, Campaign, Product, Line, Batch Number, Ingredient, Target, Actual, etc.

• My grouping hierarchy in the report:

  • Work Order
    • Campaign
      • Product
        • Line
          • Batch Number
            • MainQueryDetails (all data)

Current Behavior

• Each batch group lists all ingredients and their amounts.

• A batch group footer calculates per-batch ingredient totals (works fine).

• The Campaign group footer can show a single aggregated record (total usage for all ingredients combined), but not multiple rows per ingredient.

Goal
After each campaign, I would like a summary table that:

• Lists each ingredient used in that campaign.

• Shows the sum of Target and sum of Actual for that ingredient across all batches in the campaign.

What I am trying to accomplish is the highlighted summary of each campaign in the attached mockup. I currently have all the other parts working with a single table group and then grouping previously mentioned.

[dkhayes117]

First thing I would ask, why not add sum target and sum actual columns to the batch groupings. You already have the ingredients listed there, why repeat those lines again?

Edit: I missed the part where the sums include all batches under a campaign, but I think it could still work. Can you supply an example dataset?

[Eric_Meyer]

It would look something like this with multiple shop orders and campaigns present. Like I said, I can put a summary row on the campaign group which totals all ingredients for the entire campaign, but I can’t detail out the campaigns totals for each ingredient. I can always add another table at the end of the report that has totals of each ingredient grouped by campaign only, but I was trying to make the report more streamlined.

[dkhayes117]

Can you post a csv or something that I can copy/paste?

[Eric_Meyer]

export.csv (1.7 KB)

[dkhayes117]

I see what you mean by not being able to get multiple rows for your ingredient summation lines.

I think the only way you can do this is to add the summary for the appropriate group (line I think), then make that row unstructured. Then you can add another table inside that summary row to get multiple lines. Note that the space used for this row won’t be dynamic in height.

You’ll have to figure out how to manipulate your data, or add window functions to your query, and or subquery your data to get the ingredient totals you need to populate the tables.