I have a report with 15 subreports (including Top 10s and graphs). The criteria for the subreports are start date, end date and/or customer ID. There is currently a form set up to produce a report for a single customer, The underlying querries use the information entered in the form for criteria.
I need to create reports for all customers - one report per customer. The date range will be the same for the run, but the customer ID will change to go through all the customers in the db. I've thought about using a form with a list of customers as the record source and advancing through the records to create the reports, but that seems clunky. Any thoughts or suggestions on how to loop through the customers and pass the criteria without a form would be greatly appreciated.
I can do that for the actual customer IDs. The problem that I can't seem to come up with a 'clean' solution for is passing the ID's to the queries and/or reports. I can use the child/master relationship for some of the subreports, but others, like the Top 10's, need to get the criteria sooner or the results are the top 10 for all customers rather the the top 10 for the specific customer.
I inherited this report and it's one of the scariest things I've ever laid eyes on. It also originally took a good five minutes to open. I've managed to streamline it a bit, but I still have a ways to go.
Oh yeah I see what you mean re the top 10. So the report does not actually work as it is right? The problem is not just printing out for all customers but printing out for one customer n times?
Most efficient thing has to be filtering all the data at the lowest possible level (assuming there are queries on queries on queries). Stick the ID in an invisible text box on the form, refer all the "base" queries to the textbox to filter the ID. Run report, movenext on recordset, write to textbox, repeat.
The report works for a single customer. Currently there is a form where the user inputs the customer ID and a date range then hits print. This creates the report (3 pages - 15 subreports) for that individual customer and date range.
They now want to do a monthly run for all customers. The final result will be for the user to click a button and create a report for each customer with activity for the past month, the report will print to pdf then be attached to an email and sent to the customer contact(s).
I was trying to avoid populating an actual form with data for each customer, but with the way the report is set up, I seem to be stuck doing just that.
Well - you can avoid it - I would just say it is the simplest and most efficient way to do it. You could have your queries call a function instead for example and pass the customer id via that.
Since you need to pre-filter your queries before the report uses them you've got to loop - it is just what method or medium you use to get each ID to the query. I wouldn't agonise about it too much - just code it up
what happens if you create another top level reportthat acts as a placeholder for customer details.. eg customer ID, name etc.... then link the current top level report on the customer ID.
Id want to make sure that I had sufficient tea/coffee, enough coins for the electricity meter to pass away the hours as the system grinds on.
I can't see why you shouldn't be able to bastardise the current report. presumably its currently being limited to a specific company by a parameter.
however depending on how that parameter is passed it should be possible to use the current report, setting a group on company ID, or probably better company name and ID, put a form feed in say the group footer.
I have a similar report to this in the past, 5 sides of A3, incorporating 4 sub reports each, sub reports designed for portrait, but printed in the top level report in landscape. I still have nightmares when I hear the call from 'that' customer,nice as they are.
Thanks for the input. I actually started by setting up the parent-child links on the subreports and following the queries back to eliminate/streamline where possible. The biggest hurdles were the top 10 and graph subreports - the customer ID needed to get to the query itself. I was trying to avoid using form criteria because the form that is referenced in the underlying queries is tied to a recordsource and used for several other things.
Here's what I ended up doing: change the form recordsource to null, open the rs for the list of customers, fill the form fields to be used in the queries fromthe rs, print the report to pdf, email the report, move to next record, set the form recordsource back to what it was.