Production Reporting Configuration in Workspace

After the successful EPM configuration then we should be able to launch the workspace using below url.

http://hostname:9000/workspace/

Below are the steps that need to be performed in Workspace in order to work with SQR Production Reporting stuff. 

1.Check if Reporting and Analysis Framework is enabled in workspace ,if not enable it
2.Create Production Reporting Engine
3.Create Production Reporting Database Server

1. Enabling Reporting and Analysis Framework in Workspace

• Navigate to Navigate-->Administer-->Workspace Settings-->Server Settings 
• Open Enabled Products
• Make sure Reporting and Analysis Frame work selected/enabled
• If not selected, then select the check box and re-login workspace

2. Creating Production Reporting Engine

•  Navigate to Navigate-->Administer-->Reporting and Analysis-->Production Reporting Engine
• Click on + symbol to create new Production Reporting Engine  .

Lets create a SQL Server Production Reporting Engine.

Select “MS SQL Server” as the Engine type and provide the sqr.exe location as Product Location

For example

C:\Oracle\Middleware\EPMSystem11R1\products\biplus\bin\SQR\Server\ODBC\bin

Note: for MS SQL Server there is no specific SQR server engine available in the installed server, It has to connect through ODBC. Hence ODBC path provided for this engine

 

 

3. Creating Production Reporting Database Server

• Navigate to Navigate-->Administer-->Reporting and Analysis-->Production Reporting Database Servers
• Click on + symbol to create new Production Reporting Database Server . 
• lets create a MS SQL Server database server
  Note: Before this , create a 32 bit ODBC DSN with DataDirect 7.0 SQL Server Wire Protocol driver in the Windows Server where SQR engine installed and configured.
  Use %WINDIR%\SysWOW64\odbcad32.exe to open  32 bit ODBC manager and Add new to create one DSN in System DSN tab
  Use the DSN name as sql server database name to avoid connectivity problems from the workspace. 

Now we should good to start Production Reporting jobs from workspace.

Dynamic SQL in SQR Program

INPUT command prompts the user to enter value and that dynamic value can be used in sql query to fetch user requested data. 

sample SQR code for dynamic sql:

begin-program
 do call_dynamic_sql
end-program

begin-procedure call_dynamic_sql
INPUT $state maxlen=2 type=char 'Enter State Acronym'
let $state=upper($state)
begin-select
name (,1)
state (+1,1)
 position (+1)
from  customers
where state=$state
end-select
end-procedure !call_dynamic_sql

Once you process the above code (INPUT command) will prompt you to enter value





after that the supplied value will be passed to "Where" clause which is supposed to filter data.

where state=$state will be added as dynamically to the query and renders data only for entered value.