Many times, we get a requirement to show multiple reports in the same manner. In that case, it will be feasible to create a generic screen to show all reports on the same screen. This requirement itself is showing it is a technique that will consume less time as we are creating one page for all reports.
This blog will be useful to you if we want to develop the dynamic report generation (the same requirement which is mentioned above).As we are creating a common page for all reports, we need to save all report specific data on server side. For this reason, we have to create two tables let’s named them as Rpt_Master and Rpt_Search.
Rpt_Master:
Rpt_Master table will be saving all reports name, title, ProcedureName, Role Id and its status.
The table schema will be like:
CREATE TABLE [dbo].[Rpt_Master](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ReportName] [nvarchar](max) NULL,
[ProcedureName] [nvarchar](max) NULL,
[RoleId] [int] NULL,
[IsActive] [bit] NULL,
CONSTRAINT [PK_Rpt_Master] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
Let's save one record for an example purpose:
INSERT INTO [dbo].[Rpt_Master]([ReportName],[ProcedureName],[RoleId],[IsActive])
VALUES('ABC','SP_ABC_prc',1,1)
Let's go through it each column:
Id: Report id
ReportName: Title of the Report.
ProcedureName: Name of the stored procedure to get the report.
RoleId: User role Id
IsActive: Flag to check whether report in use or not.
Rpt_Search:
As we know, most of the times we need to retrieve some specific data for report and for such things we need to have some data filtration mechanism to get it. As we are using single page for all reports so it will not be feasible to maintain filtration control for each report on the same page. For that reason we are storing the required controls in one table called “Rpt_search".
This table contains all required controls for each report to get the data and the schema of that table will be like:
CREATE TABLE [dbo].[Rpt_Search](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ReportId] [int] NULL,
[FieldName] [nvarchar](50) NULL,
[FieldType] [nvarchar](10) NULL,
[Operations] [nvarchar](max) NULL,
[ProcedureName] [nvarchar](50) NULL,
[IsDependency] [nvarchar](50) NULL,
[Parameters] [nvarchar](50) NULL,
[TableName] [nvarchar](50) NULL,
[IsMandatory] [bit] NULL,
[DependentFieldName] [nvarchar](50) NULL,
CONSTRAINT [PK_Rpt_Search] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
Let's write one insert query for understanding of each column.
INSERT INTO [dbo].[Rpt_Search] ([ReportId] ,[FieldName],[FieldType],[Operations],[ProcedureName],[IsDependency]
,[Parameters],[TableName],[IsMandatory] ,[DependentFieldName])
VALUES (1 ,'FromDate' ,'date' ,'=' ,NULL ,'N' ,'PartNumber' ,NULL ,0 ,NULL)
Let’s go through each column of Rpt_search:
1. ReportId: It’s a foreign key of Rpt_Master table to store controls report wise.
2. FieldName: Label name for control
3. FieldType: Datatype of same control.
4. Operations: Arithmetic operator or condition.
5. ProcedureName: Procedure may require to get the data (In case of dropdown)
6. IsDependency: Yes if control's value is dependent on any other control's value
7. Parameters: What type of data does it required to call procedure
8. TableName: NULL (for future purpose)
9. IsMandatory: Whether that control is mandatory to select to get the records or not.
10. DependentFieldName: Control name which is dependent on this control.
As we have seen above, we are storing all controls of report in Rpt_Search table. Though, we need to handle those controls on cshtml page also.
In our example, we have inserted one record of from date control in Rpt_Search. Let's stick with the same example and try to design our cshtml page.
I have attached one file named “htmlCode.txt” please go through it for reference. In the htmlCode file, you can see there are two table controls. One of them is used to add filters on the screen and second one is to show the report data. Each report will go through this html page, as per report requirement we can add its relevant data filtration parameter in the table called 'ReportTable' (in cshtml page) and we can store the result data in table called ‘reportGridDiv’.
We have also taken two buttons called 'Display' and ‘Export’ and these buttons are used to display the report and to download the report respectively.
Let's go through process step by step and understand how the logic will flow:
1.Get the data filtration controls on the basis of report id:
As you can go through the attachment, there is a one ‘jqueryCode’ file. This file has many jquery functions to execute the page functionalities.There is one jquery function called ‘LoadReportDetails’. This function contains the code related to the jquery datatable. There are two columns has been taken to store label and controls respectively.
To identify which control need to be add in second column, we have taken one separate jquery function called ‘fillColumnTypeValue’. We are passing control type to this function and as per its type, we are adding the control on that page accordingly.
There is also one base function called ‘LoadReport’. This function is helpful to get the data from application server and add row(s) to jquery datatable. We are passing ‘reportid’ to this function to get its related data filtration controls from server. This function is calling one application server function ‘GetReportDetailById’. This function inside itself execute one stored procedure which is retrieving data from Rpt_search table to get its relevant records and create one json result to pass it in ajax call result.
For this functionality we have used following methods:
jquery function: LoadReportDetails, fillColumnTypeValue, LoadReport
function: GetReportDetailsById
stored procedure: GetReportDetailsById_prc
For example, we have all Indian citizens’ data. Sometimes we might be having data for one specific state only. In that case we need to have a dropdown control to show names of state to filtrate the data according to selected state only.
2. Get the data for control to filled up for filtration:
This is the optional scenario and it will come up when dependent control needs to fill up with its respected data. For this purpose we have written one jQuery function called ‘LoadValues’.
This function accept three parameters to get the data. It has one Ajax call which is calling ‘GetReportSearchDetailsById’ function by accepting report ID and search ID parameters. This Ajax call is executing one application server method ‘GetReportSearchDetailsById’ which is internally executes GetReportSearchDetailsById_prc stored procedure to get the required data to be filled the control value.
For example, we have one dropdown as data filtration control then it must be having data itself in the main result.
For this functionality we have used following methods:
jQuery function: LoadValues
Function: GetReportSearchDetailsById
Stored procedure: GetReportSearchDetailsById_prc
For example, let’s say we have a state dropdown and we want to show all Indian citizens records those are related to Maharashtra state only then in that case state dropdown must be having some data to filtrate main result to show Maharashtra state records only.
3. Functionality of display and export report while clicking on display or export button respectively:
Generally, we need report either to be displayed on screen or to get download. For this purpose, we have given two different buttons to handle this functionality.
To meet the display functionality we have written one jQuery function ‘displayReportButtonClick’. It has one Ajax function ‘GetReportJSONData’ which accepts report id, json string of report parameter and one flag which is either receive call for display or export. This method inside executes ‘Rpt_GetReportParameter_prc’ stored procedure which is a generic stored procedure which gets the data from Rpt_Master table of that report by using report id and executes the report stored procedure by applying user’s filtration data control values.
Once it comes with the output, the output get arranged as per jQuery data table requirement and finally one JSON string get created to pass it to Ajax call in success response.
In the success clause of ‘displayReportButtonClick’ jQuery function, there is another jQuery function is called (loadReportDataInGrid) which accepts report ID and server responded data to load this data in jQuery data table to display it to the user.
To download the same result, we have one jQuery function called ‘ExportButtonClick’. This function gets called when user clicked on the export button to download the report. This jQuery function having one Ajax call inside of it which is calling one server side method ‘DowloadReport’. This method is accepting report id and json string of report parameter to get the result as per the report. This function is having a FileResult data type which returns an output in a file format with its data and its format to save the file.
For this functionality we have used following methods:
jQuery function: DisplayReportButtonClick, ExportButtonClick
Function: GetReportJSONData, DowloadReport
Stored procedure: Rpt_GetReportParameter_prc
Now, let’s take an example for understanding purpose, we want one report data to be download while clicking on export. For that we have already created one stored procedure (Rpt_GetReportParameter_prc). To execute this procedure we'll need to send parameter data in specific format to execute it.
Execution format must be like:
EXEC Rpt_GetReportParameter_prc
userid=1111, reportid=2222, reportParameter='<rows><row><ColumnType>InvNo</ColumnType><ColumnTypeValue /><Equation /><FieldType>textbox</FieldType><TableName /><SpParameter>InvNo</SpParameter></row><row><ColumnType>FromDate</ColumnType><ColumnTypeValue /><Equation>2017-12- 18</Equation><FieldType>date</FieldType><TableName/><SpParameter>FromDate</SpParameter></row><row><ColumnType>ToDate</ColumnType><ColumnTypeValue /><Equation>2017-12-18</Equation><FieldType>date</FieldType><TableName/><SpParameter>ToDate</SpParameter></row></rows>',displayorExport=1
(Note: Once you download the attachment, please provide jQuery data table and CSS references to execute the htmlCode file.)
There are multiple ways to achieve the same result. I have tried to represent what I have chosen to implement.
Hope you have understood each step to get the result and you can also download all required stored procedures, application methods and jQuery functions which are needed to execute this code. There can be easier ways to achieve the same, if you find it please provide reference link or if you have any question help us by writing it down in comment box.
Thanks for reading my blog, have a great day!!