reports with wide range of records

[mzimmers]

Hi, all -

I'm putting together a layout for a report. The reported information includes some fields in other records, which I'm displaying through a portal. In any given report, there may be only a few related records, or a couple hundred.

I'm trying to figure out the best way to represent this in the layout. Should I just make a "worst-case" guess for the largest number of records any report might have, and then put the slider on it? It seems like sort of a sloppy way to do it, but I can't think of another.

Thanks...

[Genx]

... you can only display the information from the first related record of a current record...

assuming this is a one to many relation ship... you should be doing this from the many side of the relationship... you can pull fields from the one side... for example, if you were reporting on a contact who has many numbers, you would do a report tied to the numbers TO... pulling information about the contact through the relationship...

need more help just post again, and im sure either me or someone else will either explain some more or just make you up a sample file to have a look at .

~genx

[mzimmers]

assuming this is a one to many relation ship... you should be doing this from the many side of the relationship... you can pull fields from the one side...

~genx

That's excellent advice, and well-put. I can now see how to do this without using portals, and it makes for a cleaner report.

Thanks for the help.

mz

[mzimmers]

OK...so, I created this report, and now I realize that I screwed up, and need to make it sort on a particular field in the body. How do I go back into the layout formatter and do that?

Thanks...

[Genx]

I'm a bit confused, but ill just assume you used the layout generator to create your report. Just goto your report, enter layout mode, drag the word "part" from the filemaker bar, place it anywhere above the body, but below header, title header etc.

Then proceed to choose the field you want to sort by, enter whatever you want in the sub summary part, go back to browse mode, goto the records menu and choose sort records, sorting first by your first subsummary, then your second sub summary etc etc. You can also script this sorting.

~Genx

[mzimmers]

I used the "New Layout/Report" command to create my layout.

I can see that my earlier post was pretty unclear. What I want is to create a layout that automatically sorts based on a couple of fields.

I think part of the problem is that I'm confusing a few concepts: grouped data, sub-summaries and sub-totals. Can someone distinguish these for me? Once again, the online help really isn't too great for the conceptual stuff.

Thanks a lot...

mz

Edited for (hopefully) further clarification. When I said "automatically" above I was referring to how a layout will take the sort criteria that you set up when you create the layout, and use that criteria without you needing to hit the "sort" button.

The real question is: *where* is that sort criteria captured? It doesn't seem to be a visible part of the layout, and I can't find a way to go back in and modify that sort criteria. "How to...?"

Thanks again...

Edited February 22, 2006 by Guest

[Genx]

Ok, lets start form the very begining, a very good place to start, when you read you begin with abc when you do reports you begin with body's and subsummary's (i love that movie hehe).

Anywho, getting on to the point, lets get a few things straight here.

Firstly, a report is just a layout.

Secondly, a report is made up of various different parts.

Records are "split" within a report by a subsummary part.

A subsummary part will split records, by its particular field.

There can be multiple subsummary parts, for example, if you were splitting your records by year and month. For your first sub summary part which must be placed below the header and above (or below) the body, you would use the year field, and then you would either insert a merge code for that field or the actual field (this is not actually required), and any others you would like to include in your sub summary part. The second subsummary part would subsummarize by month, and would be below the first one.

Subsummary parts can be added by clicking on the word "part" in your file maker toolbar - or else dragging the word "part" physically on to your layout - when in layout mode. When inserted, you can change what the subsummary part subsummarizes by, by clicking on the actual part tag and changing the settings.

Finally, for any of this to work, we either need to perform a manual sort (go into browse mode, click records, click sort) of the correct fields or else script it using the sort script step found under "found sets".

In this case where the first subsummary uses the year field and the second the month, so the correct sort order would be:

Year

Month

Note that you can sort by other fields aswell however it is compulsory that these 2 fields which are both used to subsummarize are sorted, from highest subsummary, to lowest subsummary. So you could place day after month if you wanted.

Hope this helped,

~Genx

[Genx]

Oh, as for your confusion. Grouped-data - no idea what your talking about. Sub-totals - these are just fields defined to be "summary" rather than text or number etc. then set to the total of a particular field. This "summary" field is then put into a subsummary part where it will evaluate appopriatley, to provide you with "subtotals"

~Genx

[mzimmers]

That information is indeed helpful...thanks. I think there's still a missing ingredient somewhere though. In the layout generator, it gives you the opportunity to group information. For each group item I select, I get a sub-summary field. OK...I got that.

But *then* it asks me whether I want to sort on any additional fields. This sorting criteria doesn't seem to need a sub-summary field (the generator doesn't create one). So my question is: where is this sorting information kept?

Also, my further experiments with the layout generator have suggested that I can almost do without a body for some report, just by putting all the needed fields into the sub-summary areas. Is this OK to do?

Thanks...I think I'm getting there, albeit slowly...

mz

[mzimmers]

Oh, and one other thing...using the sub-summary parts as a vehicle for getting the data sorted works OK, but adds some space to my report, since I don't have any fields in the sub-summary parts. Is it possible to set a part to be non-printing, or is this a sign that I'm doing something lame again?

[Genx]

Ok, one more time, subsummary parts will be responsible for "grouping" your data, the fields in them make no difference, you can drag the part up and make it almost non-existant should you want. As far as i know, filemaker generates a script to sort for your report, i never generate reports this way but I would think thats where the sorting information is stored. Finally, like i said, you can sort by secondary fields, it wont split your data, but it will order it within your "grouped" sets of data.

Genx