Solved

Producing Reports by Month (using subsummaries?)

Posted on 2004-04-08
17
459 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
Comment Utility
Just sort the records by month as the 1st criteria, otherwise subsummaries does not work
0
 

Author Comment

by:tonyt1234567
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

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

Expert Comment

by:lesouef
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

12 Experts available now in Live!

Get 1:1 Help Now