Report Query from Stored Procedure (SProc)

 

This is an extremely powerful module that allows you to display the results of a stored procedure in a grid within Arena. This module uses SQL queries to automatically pull in fields of data based upon your specifications and the existing information on the page. The information displayed can be tied to the specific person who has signed in.

Requirements

Access to Arena Administration

Access to SQL Server Management Studio 2008r2 through 2016

 

Setup

First, you must be familiar with Stored Procedures. A Stored Procedure is a set of Structured Query Language (SQL) statements with an assigned name that is stored in the database in a compiled form so that it can be shared by a number of programs. In Arena, these are used to automatically process and display data on a web page. Stored procedures are the backbone of Arena. Almost any action taken within the Arena program runs stored procedures automatically in order to get the content requested to appear on the page.

Below are the steps for setting up this process:

1.          Determine what you want to accomplish and where you want this information to be displayed in Arena.

2.          Build the Stored Procedure in SQL Server Management Studio.

3.          Add the Report Grid From SPROC module on to the desired page.

4.          Configure the settings of the Report Grid from SPROC module settings.

5.          Test

 

Application

The following example shows the steps taken to create a Stored Procedure that displays the logged in users name on the Home Page.

 

Build the Stored Procedure in SQL Server Management Studio

1.          After opening SQL Server Management Studio, locate your database.

2.          Create a stored procedure. (Enter the text of the stored procedure.)

NOTE: Your stored procedure must include the parameter of "@OrganizationID INT". As of version 2011.2.100, Arena passes this value when executing a stored procedure.

3.          Click the Execute button to create the stored procedure. (If you need to make changes later, change the word "CREATE" at the beginning of the stored procedure to "ALTER" then execute again to save the changes.)

 

Add the Report Grid from SPROC module on to the desired page

 

1.          From the Administration menu, select the Pages option.

2.          For this example we are using the My Queries page which is a new page created under Administration.

3.          Click the Main Content tab.

4.          Click the Add link on the Main Content tab.

5.          Click the New Module link when it appears.

6.          Change the Type of Module from "Advance Html Text" to "Report Grid From SPROC".  Change the Title field to describe the purpose of this module.

 

Configure the settings of the Report Grid From SPROC.

 

1.          From the Main Content tab, notice the Settings section. This is where you configure the settings of the module.

2.          nclude Current User ID - Set this to "True". This will pass the "@CurrentUser" parameter to the stored procedure without having to specify it under Parameters.

3.          Enter the parameters of the stored procedure if needed.  Note: If parameters are defined in the module setting these values can also be passed through the URL.

4.          Enter the stored procedure you wish to use in the Stored Procedure box.

5.          Click the  button.

 

Test

 

1.          Browse to your page with the Report Grid From SPROC module to see the grid display the results of the stored procedure and settings used.