Your header footer idea is fine if your data output is one column per food type and 1 row with the totals:
Coffee Tea Muffin
4 6 5
However it will not work well if it is like i think it is with a column of food type and totals and one row per item:
coffee 4
Tea 6
Muffin 5
If I am incorrect please let me know as this assumption is the premise for my answer.
When you place an item on the report it is going to evauate it for 1 row, it does not really look at all of the rows and then choose the appropriate item. There are ways to mimic that but I think the simplest solution is for you to use Running totals. I understand it is redundant because you already have the total, however this process essentially makes it possible for you to get a result that evaulates all of the records and then give you back the one record you are looking for, like coffee, and place it so you can consistently join it to a text field with the word "coffee" in it.
When creating a running total you can tell it to only sum the items where another related field is a particluar thing (like "coffee").
So my example of "Evaluate as a formula: table.item="Lunch" is referring to making the running total check all of your data and only return back a sum (which in your case is going to be redundant of yur already summed data) of all of your rows where your data also="Lunch"
Follw this example to seee what I mean.
Right click on Running totla and select NEW.
In the Running total Name call it "Lunch"
In Field to Summarize select your database field that has your totals in it
in Type of Summary select SUM
in the Evaluate section click on the toggle next to Use a Formula
Click on the X-2 button - a window should open
this is where you put in the formula to tell it when to sum so it is going to be something like
{table.descriptionfield}= "Lunch"
You will have to replace the {table.descriptionfield} with your actual table name and field from your database. Make it = to the exact output that lets you know that that row is your LUNCH item. It may be the word Lunch as in my example or it may be a code like L1 or something else.
Save and close the formula window (making sure you do not get an error)
In the Reset section leave it toggled to Never ( this assumes you do not have multiple groupings in your report for a new menu display for multiple clients. IF that is true this can still work we just need to reset it differently)
Click OK
You now have a RT field called "Lunch" in your list of available DB items in your Field Explorer.
Drag and drop this on the Group or Report footer (Running total do not work in headers).
Preview the report.
You should see the field with only the total of # of lunches. you can drag that all around the footer and it won't chaneg the number. YOu can ad a text field net to it that states "Lunch".
If you repeat this for each of the items you now can always join the correct label with the correct item and place them in any order you want on the footer.
Does this make sense?
IP Logged
