Assistance with Reporting


#1

So this has been a very pleasant experience to date. I’ve managed to achieve in two days what it took me months to figure out in FileMaker.

I’m a bit stuck though and thought I’d ask if someone can point me in the right direction.

What I want to do is produce a Report (.pdf) with client specific data e.g. a running total of days spent on-site & off-site for a particular client but also summarise all other time spent. I want to include my own and client logos in the header (I’ve figured this part out), in the footer I want a disclaimer, page numbers etc. I think this needs be developed in a Form but I reached a road block (its between my ears).

See the attached - so in this case the visible data set is filtered to show DLCSI work. I need to be able to total the on-site and off-site time for DLCSI and then total all of the work for other CB’s (of which there are 4) as one line & total for the 2nd Party (of which there are 20) on another line. Obviously I would like to run this report for other CB’s in which case ABC’s data would be filtered and the DLCSI data now be included in Other CB’s totals etc.

Not sure you will be able to see the above .jpg

I hope this makes sense and any help / advice is appreciated.

Thanks


#2

We can see the .jpg image, but I think it might have been more useful if it showed the field names. What is DLCSI? What are “Other CB’s”? Where is that information in the original database? Are the second and third columns “On-Site” and “Off-Site”?


#3

Sorry I should have been clearer. My clients are (Certification Bodies) CB’s of which DLCSI is one of them and they are listed in a field called CB. All of the data / fields are in the one database - “Audit Log”.

Yes the second & third columns are fields called OnSite & OffSite and represent time in days.

The CB’s don’t need to know what work I do for each other or their customer details hence in the first .jpeg all of the Customer names are related only to that CB (DLCSI).

In the next .jpeg I’ve reordered to show column “Field” headings and a variety of CB’s.

Hope this makes sense.


#4

To start, you’ll need to make another column which contains either “DLCSI” or “Other CB’s”. I’ll assume you call this field Category, but you can call it whatever you want. Then this program will calculate the summaries you want.

field Category
select CB="DLCSI"
formulafill "DLCSI"
selectreverse
formulafill "Other CB's"
selectall
field Onsite
total
field Offsite
total
outlinelevel 2

Actually, this really won’t calculate the summaries you want, since you haven’t told us what the criteria for “2nd Party” is. But hopefully this will give you enough of a head start to figure that out.


#5

Thanks Jim - that’s almost it but this is still totals all of the OnSite / OffSite data in the one line -

I actually need the totals to be separated -

Cheers
Tony


#6

Whoops, forgot the groupup step. Also, I think the last line was wrong.

field Category
select CB="DLCSI"
formulafill "DLCSI"
selectreverse
formulafill "Other CB's"
selectall
groupup
field Onsite
total
field Offsite
total
outlinelevel 1

To learn more about the summary process, start here:


#7

You are a gentleman!

Thank you very much.