Using Stored Procedures in a Custom Report

Article
06/07/2011

Applies To: Operations Manager 2007 R2

You can save a query in a Transact-SQL stored procedure to create a report. Stored procedures provide more efficiency and security over embedded SQL queries, but they must be created by a script. For more information about stored procedures, see Stored Procedure How-To Topics (https://go.microsoft.com/fwlink/?LinkId=220266).

To create a stored procedure

Open Microsoft SQL Server Management Studio and connect to the database server.
In Object Explorer, connect to an instance of the database engine, and then expand that instance.
Expand Databases, expand the OperationsManagerDW database, and then expand Programmability.
Right-click Stored Procedures, and select New Stored Procedure. A query editing pane opens.
This pane shows a script that creates the stored procedure in the database for later use. A skeleton of the script is provided for you; placeholder items that you must replace are indicated with brackets (<>).
Replace the placeholder text following CREATE PROCEDURE with the name that you want to give the procedure. Make a note of this name; you must have it when you design the report.
If you are using a specific user to execute the report, for example, the readonly user recommended in Setting up the Environment, that user must have permission to use the stored procedure. Locate the following lines in the script:
```
USE [OperationsManagerDW]
GO
```
After those lines, add this code:
```
GRANT EXECUTE TO readonly
GO
```
This provides the readonly user who has permission to run the script.
If you are not using parameters in this query, you can comment out (preface with --) the two lines that begin <@Param. If you are using parameters, provide the name of the parameter, the data type, and the default value, if any. Note the names of the parameters for later use in designing your report. For more about how to use parameters with your query, see Custom Report Parameters.
Replace the SELECT statement with the query that you have created to use in your report.
On the toolbar, click Parse to test the syntax of your query.
After the syntax is correct, click Execute to create the procedure in the database.
Close SQL Server Management Studio.

After you have created the stored procedure, it is available to use instead of the query in any report you create.

To use a stored procedure in your report

Create a new report or open an existing report. For more information about creating a report, see Using a Report Builder. If you are creating a new report, use the wizard until you reach the point of creating the query. If you are using an existing report, right-click the dataset for this report, and then select Query. The Query Designer pane opens.
In the Query Designer, click Edit As Text. The Query Designer switches to text editing mode.
Click the Command type selection box, and then select Stored Procedure. In the text box, type the name of the stored procedure that you created earlier. Click ! to test the stored procedure.
Click OK to close the Query Designer.

The report now returns data as if you had used the Transact-SQL query directly.

The procedures discussed so far work with stored procedures that are created on the local report server. If you are using a stored procedure to be distributed with a management pack, you must add a script to the management pack to manage that stored procedure. The script requires three separate sections, for installing, uninstalling, and upgrading the stored procedure. The Authoring console has tools to assist with this.

Warning

Perform this procedure on existing reports only. If you create the scripts in the Authoring console at the same time as you create the report definition itself, you cannot save the scripts. Create the report definition, apply it to the management pack, save the management pack, and then create the scripts as described here.

To add a stored procedure to a management pack

Open the management pack containing the report in the Authoring console. Click the Reporting tab.
Right-click the report in which you are using the stored procedure, and select Properties. The Properties page for that report opens.
Click the Dependencies tab. In the Data Warehouse Scripts section of this tab, click Create. The Choose a unique identifier dialog box opens. Enter an identifier that you can use to locate the script later. Click OK. The Properties page for this script opens.
On the General tab, in the Name section, enter a user-readable name that can be used to identify the script. Optionally, in the Description section, enter a description of what the script is used for.
The Properties page has a tab for each script that you must provide. Click the Install tab. In this box, enter the Transact-SQL code required to install your stored procedure. This code begins with the following lines:
```
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'stored_procedure_name')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[stored_procedure_name] AS RETURN 1')
END
GO
```
Replace stored_procedure_name with the name that you have selected for your stored procedure. After the line that shows GO, enter the Transact-SQL code for your stored procedure. If your stored procedure is already written and saved to a file, click Load content from file, navigate to the location of the file, and click OK. If you prefer to enter the code in an external editor, click Edit in external editor, and the editor of your choice opens.
Click the Uninstall tab. In this box, enter the Transact-SQL code required to install your stored procedure. This code is usually much simpler than the installation code, as it only requires a DROP PROCEDURE statement. That code should look like this:
```
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'stored_procedure_name')
BEGIN
        DROP PROCEDURE dbo.[stored_procedure_name]
END
GO
```
Again, replace stored_procedure_name with the name that you have selected for your stored procedure. If your stored procedure is already written and saved to a file, click Load content from file, navigate to the location of the file, and click OK. If you prefer to enter the code in an external editor, click Edit in external editor, and the editor of your choice opens.
Click the Upgrade tab. In this box, enter the Transact-SQL code required to upgrade your stored procedure. This code is almost the same as the code for the installation procedure. However, the IF NOT EXISTS clause is not required, because the upgrade will not run if the procedure does not already exist. Instead, begin with this code, which should already be present in your stored procedure:
```
ALTER PROCEDURE [dbo].[stored_procedure_name]
```
Again, replace stored_procedure_name with the name that you have selected for your stored procedure. Then add the code that defines the stored procedure. Ensure that the check box Automatically upgrade previous versions of the script is selected. If your stored procedure is already written and saved to a file, click Load content from file, navigate to the location of the file, and then click OK. If you prefer to enter the code in an external editor, click Edit in external editor, and the editor of your choice opens.

Click OK to close the script properties. The script now appears in the Data Warehouse Scripts section of the report properties. Click OK to close this Properties page.
Save the management pack, but do not deploy it yet.
You must perform an additional step that the Authoring console does not do for you. You must edit the XML directly. Open the .xml file for the management pack in the XML editor of your choice. The scripts you just created are located in the DataWarehouseScripts section of the management pack. If you examine this section of the management pack, you will see separate sections with the tags <Install>, <Uninstall>, and <Upgrade>. Immediately following the Install tag is an opening <CDATA> tag, which encloses the content of the script. The Upgrade section also has a <CDATA> tag. The Uninstall section does not have a <CDATA> tag; you must add it manually. Add the opening and closing <CDATA> tags so that the Uninstall section now looks like this:
```
<Uninstall><![CDATA[IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'stored_procedure_name')
BEGIN
   DROP PROCEDURE dbo.[stored_procedure_name]
END
GO]]></Uninstall>
```
Save the file, and then close the XML editor. Deploy the management pack. When the management pack is imported, the script installs the stored procedure on the local report server.

Using Stored Procedures in a Custom Report

To create a stored procedure

To use a stored procedure in your report

To add a stored procedure to a management pack

Additional resources