We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Producing Reports by Month (using subsummaries?)

tonyt1234567
tonyt1234567 asked
on
Medium Priority
494 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
Comment
Watch Question

Commented:
Just sort the records by month as the 1st criteria, otherwise subsummaries does not work

Author

Commented:
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).

Commented:
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)

Author

Commented:
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
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...

Commented:
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.

Author

Commented:
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
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Commented:
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.

Author

Commented:
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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Commented:
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.
No offense, but I believe that the accepted answer of using GetSummary() was first directed by me.

Commented:
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

Author

Commented:
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.
 
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.