I have a report built that is similar to an employee work log. A group is created for each employee which contains an itemized list of all the work they have completed. At the end of each employee, the total hours worked and amount earned is displayed in the Group Footer band. At the end of the entire report, the total hours and earnings of all employees is displayed.
I also have a separate report that is a summary of this same information, but without the details. It is a report that lists an employee, hours worked, and total earnings on each row of a table. This is essentially the same information that is displayed in the Group Footer of the other report.
The same SQL command is used in both reports. I would like to combine them into a single report that generates an itemized work log first, and then a detailed summary at the end. But I can't see a way to do this.
Actually, maybe I could turn both of my reports into subreports and create a master report that holds them both. Is this the best approach? Or is there a better solution?
Best Answer
You can certainly put them both into the same report.
You'll need to run the SQL query twice.† Putting your summary report into the summary band of your detailed report as a subreport will be the simplest solution. You'll use the same SQL connection, then the subreport will run its own query (which just happens to be identical to the query in the main report).
Alternative solutions include:
One main report that is nearly empty which holds two subreports. This can be a good solution for complex reports. But given your requirements, it creates a bit more work without any benefit.
Using a Table component (or List component) in the summary band rather than a subreport. I generally prefer this. If you didn't already have the two reports created, I would probably recommend this. But re-writing your report as a table component would again be additional work with no real benefit.
† That's not a fundamental requirement. If you decided that the SQL query is tremendously long-running and not further tune-able, then it would surely be possible to do everything in a single pass through the result set. It would just require extra cleverness to capture all of the information that you need in that single iteration and then display it in the summary. It would be significantly more work than the other variations.