This sounds like data that isn't very likely to change much--especially data from last year.
You might want to set up a table of data where the totals shown in your report are "Precalculated" so that instead of calculating a total for a large number of records in each portal row, a simple number field displays a value or a summary field shows data based on a relatively small number of records.
I designed a "5 year comparison report" here where we have monthly sales totals where the user specifies a year, selects a material or a group of similar materials from drop downs and the report then displays a 5 x 12 grid of data showing both totals and averages of data that originally came from literally millions of records. But since the data is actually shown from a summary table where a nightly script run from a server schedule has created one record for each material from the records from the days sales, it updates quite rapidly as the monthly totals are computed from a much smaller number of records (about 25 records a month for each material).
That sounds more reasonable. For mine I would probably want a record count and maybe min and max too.
Any suggestions on how to set up that report? I take it you were saying to set up a whole new table with fields that are calculations from the original table and generate the report on that. So far playing around with a report I'm having trouble figuring out how to group by month, but if the new table uses a date field that is just month/year it should group them fine, right?
It's a whole new table with simple data fields, date, text and number fields.
What I set up here years ago that we still use is a script that finds all records that don't have a 1 in a particular number field and that have a particular value in a second field that shows it's status. Normally, that's all records used to print a receipt today. The script then uses the value in a field in the first record of this found set to constrain the found set to just this one material. Then a series of set field steps bot create a new related record and copy the value of certain summary fields to corresponding number fields in this summary table. (There's a relationship match by date and type of material with "allow creation..." enabled.) Then this found set is "marked" by using replace field contents to set the number field to 1.
This is done in a loop where the exit condition is an empty found set so if no such records are found, the loop exits.
Find unsummarized "printed" records
exit loop if no records were found
Isolate all unsummarized records with same material field as first found record
Create new related record with values copied from summary fields in original table
Mark the records as being summarized
Do you set up the new table to just hold the calculated values?
I was thinking it would have something like Dept, Month, Year, Billing (the gross billing for that dept/month), JobCount, ?
Yes, but keep in mind that they don't update automatically if the source data is changed. This works for me in my situation because once an Purchase Order "tag" or Receipt is printed and signed by the customer, we never change that record (and its associated line items) except in very rare situations where we might void the transaction and issue a new one with today's date.
That's a simple enough update, that I can script the needed update on the extremely rare case where it might be needed. (Can't think of a single case in the last 5+years where we didn't find and make such a correction on the same day that we printed the tag....)
I think mine is even simpler since I am just looking for monthly totals and maybe an occasional mid month report for a quarterly management meeting if someone wanted to see more than the last completed month.
So I just need to import all the calculated data up to the end of this April. Then write a script that will run automatically at the end of each month to get that month's data and one that will do a custom "month to date" for that rare occasion someone wants to see that. Maybe just do a duplicate set up fields (make them global) to hold that temporary month to date data?
You don't want to import all the same record data into your summary table. You need to set up a process that "condenses" the data from a sub set of your total records down into simple data values in a single new record in your summary table.
I'd write and test that "end of the month" script first until it produces the needed summary data the way that you need it. Then you can adapt a version to run on the existing data as a one time "batch update" to produce the needed summary values for your past data.
Your "month to date" data could be handled a number of different ways--such as simply a relationship matching to the month's data from your original table, or you could set up a nightly script that purges and reloads a "monthTOdate" summary table if you need faster response times for your monthly total. I'm not sure how you'd set that up for global fields without having to do this each time a person pulls up the report and that would be pretty slow.
Hmm, maybe a Perform Script On Server performed script could update a set of global fields each night to show the current month to date data...
That is what I meant, take the data from the main table, create the month/dept records in the summary table and set the calculated fields.
Thinking that month to date over, I could do a portal that shows the current month/dept. With only one it shouldn't slow things down as bad as my original with a dozen + portals.
Or if I go the global route, the nightly script would probably be best but would need a set for each dept (4 of them).
When scripting that data "import" I do the find and sort on the main table to get the dept/month combination. Then to create the record in the report table should I use a set field, i.e. set Field report::total = maintable::total (summary field) or should I go through variables?
OK, got it to work. I used set variables-go to report layout-create new record-set fields with a loop to go through the main table month by month. Then modified the script after running it for each dept to get the data up to the end of last month. Now I can just work on the updating scripts to enter each subsequent month at the first of the following month and generate the on demand partial months.
Well, at least my script worked, turns out the other departments data was not complete going back as far as my dept, so I'm missing much of that data, but it should work from now on.