pc Generating a large and complex report, with loops?

[teaaddict]

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

 

[comment]

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.

[teaaddict]

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?

[comment]

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.

[Brian C]

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.

[comment]

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.

[teaaddict]

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.

[comment]

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.

[teaaddict]

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.  

[comment]

 So thankfully I an avoid the problem altogether.

 

You should do it anyway, it's good scripting exercise.

[teaaddict]

Ha!  I feel like I'm getting plenty of that already