Jump to content

Generating a large and complex report, with loops?


This topic is 2601 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Hi Everyone,

 

I'm trying to write a script that will generate a rather large and 'nested' report from my database.  I haven't gotten very far yet- I'd like to know if what I'm attempting is feasible, and if anyone has suggestions for the route to take.  

 

My database includes records of products ordered, when, who by, and many other details.  The report I need to generate will contain many pages, with smaller reports nested amongst others.  

 

For example, the first page would be a list of the companies which have placed orders in a time period, and the total cost of the products they ordered.  Following this would be another page for each company, each breaking down the departments which have ordered items, and the total cost incurred by each.  For every department, there would then be another page detailing the Staff members who have placed orders, and their individual totals.  And finally for each individual there would be a report listing the codes of products they ordered and the cost total for each of those.

 

Each of the individual pages is possible to make using a sub-summary report and a find/ sort, but I'm not sure how to script it all together.  I was thinking that it may be possible to achieve this using nested loops.  Do you think this would work?

 

Something like:

1. Count the number of Companies which ordered products in the the specified time period.

1. Generate the report with the list of Companies.

2. Count the number of departments in each company which ordered something in the specified time period.

3. Loop through the different companies, for each, Count the number of Departments which ordered products in the the specified time period. Then produce a report with the list of departments.

4. Inside that loop, loop through the different departments, Count the number of Staff members which ordered products in the the specified time period. Then produce a report with the list of Staff.

5. Inside that loop, loop through each staff member producing a report with the list of products.

 

I hope you understand what I mean.  My first stumbling block is the 'counting' parts- to get the number of unique values in a field for a found set.  I understand that this may be possible using a self-join relationship, and am working on it at the moment.

 

 

Any and all suggestions would be welcome- is this going to work?

 

Many thanks!

 

-Sarah

 

Link to comment
Share on other sites

My first stumbling block is the 'counting' parts- to get the number of unique values in a field for a found set. 

 

I'm not quite sure how this would help you get further. It seems to me you just need to generate the four individual reports, one at a time, and put them together - perhaps by appending them together into a single PDF.

 

In any case, relationships will not be helpful in this, since they ignore the found set.

Link to comment
Share on other sites

  • Newbies

Perhaps I was unclear- I need to generate a report for each company, listing departments.  There are only 5 or so of those.  But then for each department in each company(~10 departments for some) I need another report with the staff members.  And for each Staff member (usually 10-15) I need another individual report with the product list. They don't necessarily have to be interleaved in the order I outlined above, but that would be ideal.

 

With that in mind, I thought I would need to know the number of departments, staff members etc. who have ordered within the time period I'm looking at , and then loop through that number of times, producing a page (and appending it to the single PDF) each time.  

 

I can't see a way of 'just generating the reports', but I may well be missing something obvious.  Do you mind explaining a bit more?

Link to comment
Share on other sites

Do you mind explaining a bit more?

 

No, but I want to make sure I understand the basics here first. So please correct me if I am mistaken about this (because this is the most important part here, IMHO): all your reports are based on the same table (let me call it ProductSales) and on the same found set. IOW, once you have found all the product sales within the time period of interest, you would:

 

1. Summarize them by Company: that's your first report;

2. Summarize them by Company and Department: that's your second report;

3. Summarize them by Company, Department and Staff member: that's your third report;

4. Summarize them by Company, Department, Staff member and Product: that's your fourth report.

 

These reports are not "nested"; they are different versions of each other. They would be nested only if you had produced just the last, 4th report with sub-summary parts for each of the 4 headings - thus giving up on the possibility of viewing company-level data all at once on the same page.

Link to comment
Share on other sites

I think that the nesting that teaaddict is referring to is embedding each of these reports into one another (excluding the first page of companies) so that each company's department and staff pages are all grouped together.  This way someone does not have to sort it all by hand to make sure the right bits are all where they should be.  Hence the reason for referring to loops and counts.  

 

That being said, Teaaddict: as long as you are dealing with a found set of records you should be able to use the function get(foundcount) to determine how many records are in the current found set instead of having to do a loop just to get a count.

Link to comment
Share on other sites

you should be able to use the function get(foundcount) to determine how many records are in the current found set

 

I don't think that's going to address the question here:

 

My first stumbling block is the 'counting' parts- to get the number of unique values in a field for a found set.
Link to comment
Share on other sites

  • Newbies

You're both right, it is the same report repeated with different levels of detail.  And the reports should be ordered so that each Staff member is ordered behind the department they are from, and so on.

 

All of the data for this report is, indeed in one table.

Link to comment
Share on other sites

I think we need to have a worked out example here - because there is a significant difference between having a report (or a series of reports) organized as:

 

1. Companies:
• Company A ... 150k
• Company B ...  75k

2. Departments:
• Company A
  - Dept. A1 ... 60k
  - Dept. A2 ... 40k
  - Dept. A3 ... 50k
• Company B
  - Dept. B1 ... 35k
  - Dept. B2 ... 40k

3. Staff:
• Company A
  - Dept. A1
    - Adam    ... 25k
    - Betty   ... 35k
  - Dept. A2
    - Cecil   ... 25k
    - David   ... 25k
  - Dept. A3
    - Eve     ... 50k
• Company B
  - Dept. B1
    - Frank   ... 15k
    - George  ... 20k
  - Dept. B2
    - Herbert ... 40k


or:

 

 

1. Companies:
• Company A ... 150k
• Company B ...  75k

2. Company A :
• Dept. A1 ... 60k
• Dept. A2 ... 40k
• Dept. A3 ... 50k

3. Dept. A1
• Adam    ... 25k
• Betty   ... 35k

4. Dept. A2
• Cecil   ... 25k
• David   ... 25k
 
5. Dept. A3
• Eve     ... 50k

6. Company B
• Dept. B1 ... 35k
• Dept. B2 ... 40k

7. Dept. B1
• Frank   ... 15k
• George  ... 20k

8
. Dept. B2
• Herbert ... 40k

 

 

What I said about all reports being based on the same found set is true only with regard to the first option. To produce the second variant, you will need to reduce the found set as you "drill down" within each branch - with option to return to the previous found set when you're done. Alternatively, you could use the FastSummaries method to produce the entire report as text.

Link to comment
Share on other sites

  • Newbies

Thank you for your help, I think you've got to the real crux of the issue with your example.  Following the difficulty I was having, I returned to the client to make sure that this was completely necessary or if there was any flexibility in the structure of the report.  Although she had specified the second of the options you outlined, on closer inspection of what the report is used for it seems the first option would be fine.  So thankfully I an avoid the problem altogether.

 

Once again though, thank you for the time and expertise you've given me.  

Link to comment
Share on other sites

This topic is 2601 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Similar Content

    • By Jon David Wardlow Petrie
      Hello My name is Jon Petrie and I am in the same boat as you are and I am trying to setup the samthing as you are with Filemaker To keep track of my bank transactions.
    • By Rich
      Greets, and a happy, holiday season to all.
      This is one of those "Well, I could swear it _used_ to work..." FMP (PC) problems: I want a script to create a custom-named folder on the desktop (PC) [for exporting data purposes] so I used the following (see attached) Send Event command: 
      "cmd /c md " & Quote ( Right ( Get ( DesktopPath ) ; Length ( Get ( DesktopPath ) ) -1 ) & "Job Chop-Chop! Backup" )
      ...in earlier versions of FMP--and if memory serves--it used to work. (See https://fmrift.wordpress.com/2010/02/03/creating-a-folder-from-filemaker-windows/ ). However, when I try to run the script in FMP v17.x the script fails with the following error message in the Script Debugger, "[3] Command is unavailable (for example, wrong operating system or mode)".
      So, I'm trying to figure out why the command's failing--could it possibly be it's because I'm using FMP (PC) in VMWare's Fusion PC emulator (v10.1.4, running in OS Mojave) instead of on a "real" PC? Any ideas for a tweak/fix?
      Cheers,
      Rich

    • By muskee
      Hi guys!

      I would kindly appreciate your help!
      I need a report for my employees uniforms. I have the employee table where I have these variables:
      employeCat, shirt size, shirt amount , pants size, pants amount, shoe size, shoe amount          (employeCat can be administrative, designer, reception)

      So, I made 3 summary fields to add up the amounts and thus obtain the totals of each item.

      Soo, I'm having problems when creating the report, can't get it to work.

      For example, I need to see the shirt size L and see how much shirts I need for that size and the amount for the category the employee is in.
      I. E Shirt size L, 11 required for category Administrative
      Pants size XL, 7 required for category Designer
      Same for shoes....

      And of course eould also need a grand total at the bottom
      Thanks for your help!

       
    • By Ponderosa
      I have a set of order forms, in which each order form has X amounts of orders from X amounts of clients. I can summarize with a Summary field the number of orders each client has ordered on one order form (weekly order form) and now I want to make a report that tells me the breakdown from week to week, and quarter to quarter. I can get the report to break apart the weeks by quarter, and it displays each week's correct total, but it won't do a total sum of each quarter, let alone for the entire database.
      I've tried summarizing the weekly summary field to get the quarterly amount, either in a new field, or on a trailing sub-summary part. The new field just copies the number from the weekly summary field from the record that is active, and the other way just shows the last above record. Wanna pull my hair out!
      I have a feeling that the problem is in how I set up the original weekly summary field, but I just can't see how, or how to make it work.
    • By pomilpetan
      Good morning everyone  
      I'm creating a database where I have to print a report. I created the whole structure but I'm at a block. The report is on several pages and I do not find the solution to be able to have at the bottom of each page the subtotal. The section is inserted, in fact at the end of the list I find the result. But if the list is longer than a page, I do not have the data of the subtotal but I find it on the second page at the end of the list. I tried to change the field settings by setting total progressive in the options and inserting it in the footer section but I always find the total integer, while if I look at the data in the table format the field has progressive values Could someone help me on this? I have been reading manuals and internet for two days but I do not find the solution (hoping there is) Thank you all
  • Who Viewed the Topic

    7 members have viewed this topic:
    Gnurps  kamot37  Sinky  JerrySalem  JackPotts  jee  Tattwam 
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.