How to design a better reporting process

Published on: 7th Aug 2012

Updated on: 19th May 2021

Overview

Reports is an important feature for any system which either prints out the necessary data keyed in by the user (i.e., transactional report or listing report) or generates a summary view to the data (i.e., summary report). The former is straightforward and the later requires a better design in stored procedure and performance is most likely an ongoing maintenance issue.

Our approach

A table for storing the user parameter values. It stores the user parameter values in a row. Each row is meant for one report request session.

One advantage using this table is that the request session ID can be passed to the sub-process and then the sub-process will load the parameter value. This reduces the number of parameters to be passing around. The caveat is that it increases the number of data read.

Another advantage is that you will be able to tell the actual parameter value that has been passed in. This also benefits in the security auditing who wants to find out who has requested which report.

The following table is our way to keep the information requested by the user.

create table tb_user_rpt_param (
    user_rpt_param_id uniqueidentifier not null     --primary key    
    , created_on datetime default getdate()
    , created_by nvarchar(255)              --the user who requested the report.
    , sess_id uniqueidentifier not null     --the report session ID.
    , rpt_id int                            --the report ID.

    -- the user parameter values. You may expand the fields.
    , start_dt datetime not null            --the start date of the period.
    , end_dt datetime not null              --the end date of the period.
    , dept_id uniqueidentifier null         --optional. If NULL, returns all departments.

)

For example, a sales summary report that has the following section,

1. Daily sales for the given period.
2. Sales by each department for the given period.

In this case, you need the following stored procedures to prepare the data and then pass it to the reporting program.

1. pr_rpt_sales_summ_by_day @sess_id
2. pr_rpt_sales_summ_by_dept @sess_id

The above stored procedures will accept the @sess_id parameter and the stored procedure will load the actual parameter values from tb_user_rpt_param.

Advanced technique

To make this design more powerful, we may add the following requirement,

1. Keep the duration for generating the report.
2. Allows users from scheduled report generation.
3. Inform the management team if any one is requesting for lots of reports.

create table tb_user_rpt_param (   
    ...
    , gen_start_on datetime          -- the report generation start time.
    , gen_end_on datetime            -- the report generated end time.

    , sch_run_on datetime            -- the scheduled date/time for generating this report.
    , rpt_recipient nvarchar(max)    -- the scheduled report recipient.
    ...
)

Where,

• The gen_start_on and gen_end_on fields are meant for tracking the duration for generating the report. With this information, you will have to define the standard duration and then create a notification if it does not meet the standard duration.

• The sch_run_on field stores the scheduled date/time for generating the report. For example, a monthly sales report should be generated on the 1st of the month. Upon completion, the report will be sent to the sales manager where the email address is kept in the rpt_recipient field.

For how to send the notification, please refers to: design a custom mail process

For the third requirement mentioned above, you need a stored procedure that performs the auditing periodically. This is a challenging requirement which requires lots of thoughts. In simplicity, the audit requirements should be able to answer the following:

• How many reports have been generated by a user on a daily basis?
• How many reports have been generated by a user for the last 30 days, 60 days and 90 days.

Related posts

• Dynamic query

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.