How to design the reporting process that involves in multiple sub-processes

One problem that you might be facing (if you are doing the above mentioned) is that the main stored procedure is calling multiple sub-processes (i.e., other stored procedures) and generate/process some data and returns it to the main process. How do you simplify the entire process?

You need the following tables in order to split the entire process into smaller task which eases you from the testing to the maintenance:

• A table for storing the user parameter values - in this  table, it stores the user parameter values in a row. Each row is meant for one process session. By having this table, you will have to pass the process session ID to the sub-process and the sub-process will be able to load the parameter value from that is required. Another advantage of having this table is that you will be able to tell the actual parameter value that has been passed in. In case, the auditor would like to view what and who has been queried, you may retain the record in this table.
• A few report working tables - all the data that has been processed will be stored in this table for other module to pickup. For example, the report writer will query this table for the final output. The reason why you need many working tables is that it allows you to query the necessary data and stores into first table. Then, run an aggregate process and store it into another table as the final output. This will be very useful when it's not straight forward for you to get the final output by using one SELECT statement.

The stored procedure design:

• A stored procedure that is responsible for saving the parameter into the parameter table.
• All stored procedures must have "sess_id" (i.e., the process session ID in UniqueIdentifier type).

Note: please take note that the above design is relying on "sess_id" (i.e., the process session ID in UniqueIdentifier type).

Real life example:

• Generating Balance Sheet report (i.e., an Accounting report) which consist of multiple sections that includes at least the fixed assets, current asset, current liabilities and capital. The raw data might be stored in different table and requires some aggregate over the historical data.