Solved

Producing Reports by Month (using subsummaries?)

Posted on 2004-04-08
17
461 Views
Last Modified: 2010-04-27
Hi,

I have created a survey to measure patient satisfaction at a hospital in Pakistan, using Filemaker Pro 6.  

I would like to produce a layout that allows me to view results by month.  As I understand, this can be done by using subsummaries.  However, when I do this, Filemaker gives VERY erratic results.  Instead of grouping surveys by month, it seems to produce each and every survey that is in the set.  

So, if there were 1,000 surveys over a 5 month period, I would expect to see 5 reports--one for each month.  Instead I get 1,000 reports!

Also, is there any way I can allow a user to select a period of months which they wish to analyse?

Any thoughts or suggestions would be most appreciated!

Tony
0
Comment
Question by:tonyt1234567
  • 7
  • 7
  • 3
17 Comments
 
LVL 28

Expert Comment

by:lesouef
ID: 10798403
Just sort the records by month as the 1st criteria, otherwise subsummaries does not work
0
 

Author Comment

by:tonyt1234567
ID: 10798760
Hi!

I've done this, and I still cannot get Filemaker to group the reports by month.

If I have 1,000 surveys over three months, and try to do a subsummary, I get 1,000 results instead of three.  (i.e. even though the database is sorted by month, it doesn't actually group the surveys by month).
0
 
LVL 28

Expert Comment

by:lesouef
ID: 10800292
I forgot to mention that this summaries are only meant for reports, therefore they summarize only when you print or in preview mode; don't expect the browse mode to display summaries.
If this is still not true for you, then yr sub-summary definition is wrong somewhere, but I can only judge this with the file itself... Can you post an empty copy of it somewhere? If you do so, I'll have a look to it (just in case the above reasons do not fix anything)
0
 

Author Comment

by:tonyt1234567
ID: 10803094
Hi lesouef!

I have viewed the reports in Print Preview mode, and I still have the same problem.  

Thank you VERY much for taking the time to look at my report model.  I have an empty database model (that is compressed), but I'm not sure where I can post it.  Do you have any ideas??  If there is any chance I could e-mail it to you directly, you can write me on:

tonyt12345@yahoo.co.uk

Thanks again for your help!

Tony
0
 
LVL 2

Expert Comment

by:KungFoolio
ID: 10805305
Use the getsummary(field,breakfield) function. I end up using this all the time to get summaries inside the subsummary section.

Yes, this means more fields in your database, and a real pain for adding new reports, but it's the only way to get some summaries into your reports.


You may also try prompting the user for a starting month and month span, setting a global for month, and a global for the month counter, finding just the records in that month, preview/print report, then check the counter if=0, if false, increase month global by 1, reduce counter by 1, find, etc...
0
 
LVL 28

Expert Comment

by:lesouef
ID: 10806995
KF, I do that when I don't print them, otherwise I don't... I'll have a look to his database first as this seems rather basic the way he explains it.
0
 

Author Comment

by:tonyt1234567
ID: 10807195
Hi KF and lesouf!

First, let me say that I think my problem is a relatively simple (I hope!)--but I am open to any approach that will allow me to do this.

I am also very excited to hear that it IS possible to allow the user to select a month (or range of months)--any advice you may have on doing this would be MOST appreciated!   If I can get this to work, then my report model will do everything I need it to.

Thank you again to you both!

Tony
0
 
LVL 2

Assisted Solution

by:KungFoolio
KungFoolio earned 250 total points
ID: 10810116
Tony, I looked at your file. You're report is setup incorrectly.
1) Move your break field from the Leading summary into the Body
2) Delete the Leading summary section
3) Change the Body to Sub-summary when sorted by...
    -select page break after each occurrence
    -select restart numbering (and add page numbers to your report for style :))
    **when prompted, select to put the summary "After"
4) OK, done! You may want to navigate to this report via a script which performs the sort for you...which brings us to the next point...

5) Your break field won't work. Notice that when you concatenate the month name & year, "April2003" is followed by "August2003". Not good. So, keep your current break field (but add a space for grammar) so that it reads correctly on the final report. But we're not going to sort on that one. For sorting's sake, define a new calculation field which concatenates the year and month number seperated by a "-". Call it YearMonth,  whose calc is Year(Survey Date)&"-"&Month(Survey  Date). Calculation result is text, unstored.
6) In Layout mode, double click the Subsummary section and select this new YearMonth field. Also make this your sort field in the script which takes you to this report.

Please let me know if you need more detail. We'll save the month prompt/counter for another time.

KF
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:tonyt1234567
ID: 10810675
Hi!

Thanks VERY much for your advice!  I managed to get the subsummaries to work (almost!), and have a few final details to go through.

On first glance, it seems that some months are being repeated--for example:

May 2002 22 surveys
June 2002 12 surveys
June 2002 8 surveys
July 2002  15 surveys

"June 2002" is repeated twice, and I'm not sure what might be causing this.  I will look closer tomorrow, and see if it is something simple that I am overlooking.

(Note: I have sorted the reports as you suggested, year-month no. (2004-4 for April 2004))

Thanks again!!

Tony
0
 
LVL 28

Expert Comment

by:lesouef
ID: 10811669
Too late, overtaken by an KF while I was sleeping! Due to time shift EE work 24h/day!
However, looking to yr file, first thing to change is the "survery date month year"
redefine it as = Year (Survey Date) & " - " & RIGHT("0" & Month(Survey Date),2).
this will sort properly by year/month even id the month is < 10 (it seems that KF skipped that
Then you should move in the subsummay every field which has to be summarized like sums, etc, and remove fields in the body if you do not need details beneath summaries (talking about the survey analysis by month layout)
I won't waste more time than necessary if you achieved the goal with KF's  advices.
Let me know if you need more, I keep the file so far.
About the duplicated month, there must be a record with a fancy date which has been forced at entry time, with an extra  space or so... Without the data, I can't say more.
Selecting the month to summarize is purely a date search before printing, so where do you need help for this?
To be able to enter a range? You can make this with 2 global dates; once operator has entered them, you concatenate them with "..." in between and search this expression in the survey date field.
0
 

Author Comment

by:tonyt1234567
ID: 10817449
Hi Lesouef and KF!

Thanks to both of you, I have now gotten the survey to sort with subsummaries!  Both of you have been tremendously helpful--and I really appreciate it.

I have one related question, which I would greatly value your advice on.

Some of the fields in the forms show a count and a calculated percent (for example, G0A and G0B--which are used to measure the percent of patients having visitors).  I would like to have a count, and a percent.  

So, for April 2002, it should say something like:

Visitors (Yes): 8        Visitors (Yes Percent): 80%
Visitors (No):  2        Visitors (No Percent):  20%

May, June, and so forth would have value for these months.

HOWEVER,

While I get an apropriate count, the percentage seems to be calculated based on ALL surveys, rather than all surveys within the current month.

So, if the average proportion of patients (in all months) having visitors was 85%, then I would get the following, for example, from April 2002:

Visitors (Yes): 8       Visitors (Yes Percent): 85%
Visitors (No):  2       Visitors (No Percent) : 15%

How can I make this calculation work for the values of each month?

Any ideas would be most appreciated!

Tony
0
 
LVL 28

Accepted Solution

by:
lesouef earned 250 total points
ID: 10822640
You need another field which summarize visits per month. At the moment, you have probably use the overall amount of visitors per patient.
Use GetSummary ( yr existing amount of visits , breakField )
yr breakfield does not exist yet but can be computed on the fly = month (date visit).
Then calculate the visit % based on this new field.
The only problem is that I have not seen any date for visits in yr base; is a survey considered as a visit??
0
 

Author Comment

by:tonyt1234567
ID: 10837523
Thanks to both Lesouef and Kungfoolio!!

Your advice has been extremely helpful, and, thanks to you, I have been able to create the database that I needed.  I really appreciate your time and efforts!

Two minor questions:
1) Is it possible to show total number of pages, in addition to page number? (i.e. Page 1 of 3)
2) How do I change the color of the background from white to, for example, grey?

Thanks again!

Brgds,

Tony
0
 
LVL 28

Expert Comment

by:lesouef
ID: 10840160
1: no, but you may be able to cheat by writing a script which goes to preview mode, activates the last page, paste the current page number in a field, and put that field in yr layout (juts a thuoght, not tested)
2: either by drawing a color box underneath everything in the layout, or by selecting the body or subsummary by its label, and setting its colour in the colour palette as any object.
0
 
LVL 2

Expert Comment

by:KungFoolio
ID: 10842255
No offense, but I believe that the accepted answer of using GetSummary() was first directed by me.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 10843100
Actually, I used it only for the second part of the question , but I agree, this could have been at least shared. I leave the questioneer to decide and see if he can change that with the moderator
0
 

Author Comment

by:tonyt1234567
ID: 10844268
Hi Kungfoolio and Lesouef,

You both were extremely helpful in answering my question, and taking the time to review my database.  When I closed this question, I tried to share points between you--250 each, to reflect that the combination of your suggestions had given me the perspective I needed to make my database work correctly.  (Though, as you two note, perhaps I should have given more points to Kungfoolio--a 300, 200 split?)

If only one of you had gotten the points, then please let me know and I will make the needed corrections.  I am still new to experts-exchange, and so it is quite possible that I had inadvertently given lesouef all 500 points.  Please accept my apologies, and know that I will do all possible to ensure that this is sorted.
 
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now