Sunday, October 2, 2011

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.










1 comment:

Arun kumar Citla said...

Hi,
could you pls explain how the finish button works in the report with the text box prompts you have for the user to input the values. how do we control the stored procedure to execute when finish is clicked, or does this happen automatically ?

I have a very similar requirement and your post is very helpful. Thanks.

Regards,
Arun