Using JMO's Restart Subsummary Numbering calc...question

[deego55]

This if for John or anyone else familiar with his calc field for restarting numbering for subsummary sections:

Case(

GetNthRecord(Category; Get(RecordNumber)) ≠ GetNthRecord(Category; Get(RecordNumber) - 1);

1;

GetNthRecord(RestartNumbers; Get(RecordNumber) - 1) + 1

)

This calc works great BTW...my question is this:

Is it possible to add a calc field to each record that will take a field and divide by the number of records in the subsummary section?

For instance if my field NUMBERS is 50 and i have a report that uses your calc to number say 10 records in a subsummary section 1-10 then the calc would be 50/10.

this would help me immensely if this is possible.

Thanks for any help.

Edited November 30, 2006 by Guest

[deego55]

I got it to work using the Last() command and a sort.

[deego55]

turns out the Last() function doesn't work as i hoped. Does anyone know a way to grab the number of records in each subsummary part?

Either a count or grabbing the last number using JMOs method above?

[Fenton]

You'd need a Summary field. It could be "Count of" some field. Or, if you've got a Constant=1 calculation field, it could be "Total of". Last() is an aggregate function, not much to do with Subsummaries; more for fields or relationships.

[deego55]

hmm. the issue i am facing is that the value needs to be on every record in the body section in the subsummary part. i dont think a summary field will work here because it will return everything.

i am trying to compare a rollup of values in one table against individual values in a second table, but i cannot get the rolled up values to sum across the break fields, since there is no value in each record of the body.

[comment]

I am still not sure what your question is. At first, I thought you wanted "the number of records in the subsummary section". That's easy: define a summary field sCount that counts your SerialID. Then, a calculation field =

GetSummary ( sCount ; Category )

will give you the number of records belonging to the same category as the current record (when sorted by category, of course).

But you mentioned another table and "a rollup of values" and I got lost.

[deego55]

sorry to confuse you. i am confused as well, obviously

i have a table that includes several records for a given fund code. i need to compare the total of those funds (i.e. rolled up) against individual records in a budget table (related by fund code), to get a balance. when i sort the report by fund code, the totals are correct (which are a summary of the fund amounts), but i cant seem to get the total of all the summary amounts. For instance a project manager may have several fund types, each with several budget records.

i guess i need a "summary of a summary field"

i was thinking of creating an intermediary table that would sum the fund amounts first, and then relate that to the budget table.

wow, did i just confuse you even more? it all sounded so clear in my head!

[comment]

Yes, it's rather confusing. So you have your totals per fund code. To get the totals per manager, add another subsummary part (when sorted by manager), place the same summary field in that part, and sort by manager, then by fund code.

How this is related to your first post is beyond me.

[Søren Dyhr]

against individual records in a budget table

Try to put the summary field in as both the breaker value as well as what it uses to be in the GetSummary( calc'function ...this is bound to be a scripted matter - if accidenatally left un-attendet on a arbitrarily openend layout will it cause hourglasses or bathball's according to operatingsystem ...if the found set is of some size.

--sd

[deego55]

yes that is the first thing that i tried, but it didnt work. my assumption is because there are no fund amounts in the body of the report, only on the subsummary by fund parts. this is the only report i have ever had issues with, so i think i am going to start over from scratch.

[comment]

Summary fields don't care if the body part is missing or not.

[deego55]

ok thanks, i was wondering about that. im going to start from scratch!