Totalling Variable Values into a Footer

Okay, I’m sure this is probably something simple, but I just don’t get it[:(]. This stuff makes my head hurt.[V] Please note: I’m a Newbie. I’m trying to write an Aged AR report by Region. Region is stored on the customer table - but isn’t a key field. But - Territory is a key field, and Region is the first two characters of Territory. So I’m using Territory as my key, and checking for the Region to change. I’m reading the Customer table by Territory, and then retrieving all Customer Ledger Entries that are open to find the current open AR balance (date filtered). I’m aging the “Remaining Amount ($)” on the entries by due date into my 5 aged amount variables by customer(CustTotal30, CustTotal60, CustTotal90, CustTotal120, CustTotalOver), and by REGION(RegTotal30, RegTotal60, RegTotal90, RegTotal120, RegTotalOver). These are just globals with decimal type. All seems fine with the details - they’re printing and calculating correctly at the Customer Level. BUT - I CAN’T GET THE REGION TOTALS TO PRINT![xx(][V][:(] What is the magic to getting the GroupFooter to print? I’ve put Region in my Customer table “GroupTotalFields” property, and - since my aging variables aren’t in the table - I originally didn’t have any “TotalFields”. I just calculate them manually and clear them when my Region changes. But now - out of sheer desperation - I’ve even put a “Balance ($)” field into the “TotalFields” property to try to ‘fake it out’ (I’m guessing - not necessary?). Guess what - it still doesn’t work. I’m desperate for HELP! This is my 3rd try at this report, and I’m needing it to work badly![:(] Any Ideas Oh Great Ones?[:)]

Hi, Is Region field is part of Key selected in DataItemTableView Property?

I would guess that if you are sorting by Territory, you would have to use Territory in your GroupTotalFields. In your GroupFooter, then you would have to check if it is a Region break vs. a Territory Break.

Hello, Without having ever seen the US version of Financials, I would still make the assumption that you have a table there called Region. If so, I would use the Region table at the topmost level of this report, and then DataItemLink between Region and Customer. If you then use Createtotals(CustTotal30, CustTotal60, …) in OnPreDataItem of Region that should do the trick. Then all you need is a footer section for Region with those varialbles in it. /Tero /Tero

No, unfortunately we don’t have a Region table, and because the Territory is a further breakdown of Region, and we do have a Territory table, we decided not to create one (the first 2 characters of Territory is the Region). Well, I’ve done some “playing” with this thing (pointed in this direction by Crisk - thanks!), and what I’ve discovered is that if the field is not part of the key - you won’t get a group header or footer printed. I entered Territory in as part of the key, and I did get the group header (for Territory - not Region) - but still not the footer. However, this is probably from something I’m doing wrong in my coding. (Isn’t it always?[8D]) I’m still at it, but glad to know about the KEY being an important part of this.

HINT: If you press F1 [:(] , you will get this:


GroupTotalFields Use this property to specify those fields of a data item that should be used to group records. The GroupTotalFields and the TotalFields properties can be used in conjunction to maintain totals for one or more fields, and both the group totals and the grand totals can be output by using the appropriate sections. Applies to Data items Settings A comma-separated list of field names. The fields can be selected by using the assist-edit function in the Value field of this property. Any field in the list must be a field in the active key of the underlying table. If it is not, the system will not run the report. Comments When records are retrieved, they will be grouped by the contents of the fields that are defined as the GroupTotalFields. If there is more than one field in the list, the groups will be nested. This means that there will be a group composed of records that have the same contents in the first field indicated. Inside of this group, records will be grouped according to the contents of the second field in the list, and so forth. When using GroupTotalFields, you must be aware of the relationship to the currently active key of the underlying table. See the Example section below. The GroupHeader section will be output before a new group is begun, and the GroupFooter section after all records in a group have been processed. At this time, the fields designated as TotalFields will contain group totals for the group that was last processed. The group totals are printed by placing controls that have these fields as their source expressions in the GroupFooter section. The TOTALSCAUSEDBY C/AL function can be used to determine which field changed contents, and thus which group has been finished. Using this function can be necessary if you have defined more than one field in GroupTotalFields and it has to be decided whether to output a GroupHeader or GroupFooter section or not. The fact that the functionality of GroupTotalFields is closely connected with the active key demands that some precautions be taken. Suppose, for example, that these two fields are listed as GroupTotalFields: No.,City while the active key consists of these three fields: No.,Name,City If the contents of the Name field change as the records are processed, the grouping that was supposed to take place, based on the City field, will fail. Therefore, it may be necessary to deprive the user of the possibility of choosing a key from the request form of the report, as the grouping will only work with the one key that it was designed for. You can remove the request form altogether by using the UseReqForm, or, less drastically, remove the Sort… button by using the DataItemTableView property to set an explicit sorting order (key) for the data item.


Thanks David - that’s good info! I guess it’s all in knowing where to look. I’d checked under Footer and everything else. Gracias mucho amigo! Another problem solved!

Yes the help and manuals are great, but do take some Navigation skills. PS have you atually read them? I read them all originally, so now it is pretty easy to find things quickly.


Originally posted by David Singleton
Yes the help and manuals are great, but do take some Navigation skills. I read them all originally, so now it is pretty easy to find things quickly.

David, This is probably more appropriate for the start of another thread, but I wanted comment on your statement about the manuals. I agree that when they used to exist the manuals were good and that the manuals combined with the help were pretty all inclusive. But there have been no functional application manuals since Attain was released. I find the help material by itself quite limited. Just to check if I was the only one with that feeling, some time back at a Navision US meeting with about 150 NSCs represented, I raised the issue “Could we get printed manuals again, similar to what used to exist”. The response from the NSC attendees was so strongly in support that the Navision folks moderating the meeting had a bit of a hard time regaining control. As a result there was a preliminary effort to strongly encourage Mavision a/s to consider the issue. But, I’m sorry to say, other issues, including the purchase by Microsoft got in the way, and nothing happened. My main point is, we no longer have application function manuals, only administrator and development manuals.

Point taken. The manuals I read are for an older version, and I have just used the philosophy of searching the Help files for information about new functionality after each product release. I also read the Log documents in detail, but this is really like using a microscope to get a view of the Grand Canyon. For me, it is just a case of each year reading the new bits, but I guess now for someone to read the 2.60 manuals, and then work up, does not make too much sense. As an FYI, the manuals I read cover to cover were 3.04 (DOS) 0.9 Financials and 2.01 Financials.

Careful there - you’re showing your age. [:D]