My client has a modestly sized database that stores information about his business. One table in this database records data for ongoing jobs for his clients. A line in this table represents a job worked on a specific date. There can be multiple jobs on the same date, and there can be multiple dates for a particular job.
I have a report that shows three columns of about 30 values. The report is generated from the above-mentioned table (and related tables) based on a job number and a date. The first column presents data for the given job on the given date. The second column shows the same information for the job, but as a year-to-date summary. The third column gives the same information again, but for all jobs up until the specified date. The three columns could be named "current", "jtd", and "ytd".
For example:
+------------------------------------------+
| Current JTD YTD |
+------------------------------------------+
| Hours 2 21 98 |
| Output 17 190 872 |
| Expenses 23 84 199 |
+------------------------------------------+
The report works fine, but in a rush to get it working I used ExecuteSQL for nearly every field (roughly 100).
I can easily retrieve any of the three sets of data by filling in the correct search criteria.
I can create separate copies of my table on the relations graph, with the appropriate relationship criteria to show the desired output, but it seems that the separate copies are bound to share a context in calculation fields, so again I can only see one set of data at a time.
Is there a better way to see all three sets of criteria simultaneously on one report view?
