Using Stored Procedures in Reports

Here's a simplified requirement that requires using stored procedures in reports to set values.

Requirement: Create a chart report to display Target Sales value. Provide users with option to set Target Sales Values that should get reflected when other users run the same report.

Solution:

Step 1: Create a simple Stored Procedure that inserts ProdId, Date and Target Value into the target table. For our example and for simplicity I have created a SQL Server Stored Procedure. The Stored Procedure should return a dummy value so that we can create a Data Query Stored Procedure in FM.

Stored Proecures are of 2 types - Data Query and Data Modification. Data Query Stored procedures are visible for use in report studio while Data Modification Stored Procedures are not visible for use in report studio.



Step 2: Import the Stored Procedure in FM and set the prompt values.

Step 3: Create a Prompted report to get the input values from the user. Insert text box prompts in the prompt page as shown below.



Step 4: In the report page drag the dummy return value and set the visible property to No. This will force the SP to get executed.Insert a text item to display a message to the users on sucessful execution of the Stored Procedure.



Step 5: Create the main report to display the chart object. Insert an HTML item to open the Prompted report:

HTML Item:

<script>
function SetValues()
{
window.open('<Prompted Report URL>','','width=400,height=400,menubar=no,toolbar=no');

}
</script>

<input type="button" value="Set Values" onclick="SetValues();">

Insert a RePrompt Button to refresh the report.