Define Custom Report using Stored Procedure

· User can write a Custom report using Stored Procedure just by selecting the Stored Procedure option while defining the report. A proper procedure must be created in the SQL server for which good knowledge of SQL is required.

Step 1: Define Procedure

· Here we define the procedure and give it a unique name which the user will be entering into the tool while defining the report.

· Open MS SQL Server Management Studio and the Object Explorer.

· Expand the Databases to find the user server.

· Server used in this example is ‘SamplePharma’.

· Right click on the server for the new query.

· User can define the Query as per requirement. Here we write Query to combine selected headers from Work Order Plan and Item Master. Refer to the below image for a template. Execute it.

· User can modify the procedure by expanding SamplePharma --> Programmability --> Stored Procedures.

· Right click on SP_SampleReport and modify.

· The procedure will be stored in SP_SampleReport.

Step 2: Defining Report

· Changes should be made during the addition of the Custom Report.

· Select Stored Procedure and click on Generate. Enter SP_SampleReport (Which contains the procedure defined in SQL Server). Add it.

· Custom Menu Design and User Authorization remains the same as followed in this Example