Replies to This Discussion

Permalink Reply by Keith Govender on February 14, 2012 at 2:16pm

Hi Leo,

 

Let me know if this does help you.

Stored Procedures

Sage Accpac Intelligence supports the use of Stored Procedures as the source for Data Containers for certain Database systems. For a list of the supported systems please go to Stored Procedure Supported Databases.

Adding a Stored Procedure Container - Step by Step

1. In the Connector add a new Container to your Connection. When prompted to Select the Container Type select Stored Procedure.
2. Enter a descriptive name for the Container.

3. When prompted to Specify the Stored Procedure details enter the name of the Stored Procedure and any Input Arguments for the stored Procedure. In the example below the Stored Procedure is called sp_sales_by_region and it expects a integer argument for the Year and a string argument for the Continent.

4. Click OKto complete adding the Container.
5. Right click on the new Container and select Add Expressions. When prompted for the Expression Type to add select Data Fields.
6. The list of fields that the Stored Procedure returns will appear. Select those fields which should be available for reporting on and click OK. The Container should now be ready to be used in Reports.

Dynamically Setting Stored Procedures Input Arguments at Report Run Time - Step by Step:

To dynamically parameterize reports that use a Stored Procedure that expects input arguments the Container must be used in conjunction with Pass Through Variable(s). The defined Pass Through Variables must in turn be defined as Parameters on the report.

1. Add Pass Through Variable(s)to the Container.
2. Add Parameters to the report based on the Container Pass Through VariableExpressions create in Step 1.
3. Edit the Source Container property of the Container to include the Pass Through Variable defined codes in the stored procedureinput argument list. For the example above the syntax would be:

   @YEAR@, @CONTINENT@

   where @YEAR@ and @CONTINENT@ are the Expression Source (or Variable Code) property values of defined Pass Through Variables within the Container. Note: When a stored procedure expects non numeric (e.g. text based inputs) then the Variable code must be prefixed and suffixed with a single quotation mark (').

Limitations of Using Stored Procedures as Containers

The following limitations exist when using Stored Procedures as Data Containers:

• Only Expression Fields of types Data Field and Pass Through Variable can be used in the Container. SQL Expression and Excel Formula type expressions are not supported.
• Reports that use Stored Procedure type containers cannot use Filters or Aggregate Filters. Filtering can only be achieved with a combination of Report Parameters, Pass Through Variables and Stored ProcedureInput Arguments.
• Stored Proceduremust return a single data set only.
• Only a single Stored Procedure can be used in a Container. Stored Procedures cannot be joined.

• ▶ Reply