原文地址 :
存储过程调用描述
Cognos8.3 Report Studio设计动态报表，在前一节提到宏变量；本节是调用存储过程传递参数实现数据集返回，实时获取最新结果。对于特殊化的报表数据，不能用简单的SQL脚本设计，考虑存储过程中使用游标输出。通过Framework Manager导入存储过程，再对机构约束限制，但数据权限控制有点麻烦。我可以在Report Studio设计一个汇总信息查询与用户信息表查询进行连接，到达数据访问控制。
报表设计时，重点是对业务逻辑熟悉程度，要点是业务需求梳理及数据组织技能高低。针对复杂报表实现方式很多，具体问题具体分析，寻找快捷有效的方案。
过程设计与调用
基于Oracle数据库，设计存储过程，返回一个结果集。
存储过程代码如下：
CREATE OR REPLACE PROCEDURE pro_stat_op_plc_anly(v_mon_id in integer,
v_org_lvl in integer,
v_stat_list out SYS_REFCURSOR) IS
v_begin_date integer := v_mon_id * 100 + 1;
v_end_date integer := v_mon_id * 100 + 31;
Begin
open v_stat_list for
select b.org_lvl_nm,
b.org_lvl_sys,
b.parent_org_name,
b.parent_org_sys,
b.org_lvl_id,
b.lvl_name,
sum(a.acqu_ins_qty) as acqu_ins_qty,
sum(a.acqu_ins_prm) as acqu_ins_prm,
sum(a.acqu_ins_amnt) as acqu_ins_amnt,
sum(a.form_agg_tms) as form_agg_tms,
sum(a.stdpol_form_agg_tms) as stdpol_form_agg_tms,
sum(a.stdpol_qty) as stdpol_qty,
sum(a.nudepol_qty) as nudepol_qty
from bidm.ta_op_plc a, gldmdb.v_d_org_lvl_4 b
where a.internal_org_id = b.selling_org_id
and b.sign_id = 2
and b.org_lvl_id = v_org_lvl
and a.cal_day_id between v_begin_date and v_end_date
group by b.org_lvl_nm,
b.org_lvl_sys,
b.org_lvl_id,
b.lvl_name,
b.parent_org_name,
b.parent_org_sys
order by b.org_lvl_sys, b.org_lvl_id;
End;
在FM导入一个数据源，选择“Stored Procedure”
指定一个过程名，点击“Finish”。
对新建的查询定义窗口，编辑输入参数“v_mon_id”、“v_org_lvl”。
修改数据类型为“nVarChar”，如果按照过程输入参数类型来设置，会出现报错。
增加两个变量，实现后端定义参数，前端调用:传入参数定义窗口在value处定义调用的变量。
Example - Use Prompts with a Stored Procedure
If you define prompts for stored procedure variables, your users can set the variables in reports.
Steps
-
Create a stored procedure query subject that uses the sp_FIND_ORDER_DATE stored procedure.
The Query Subject Definition dialog box appears.
-
On the Definition tab, select the @order_number argument, and click the ellipsis (...) button.
-
In the Value box, type the following macro syntax and then click OK:
#prompt('Order Number','integer')#
Note: Framework Manager removes anything that is outside the number signs when running the macro.
-
If you want to test the prompt for the variable, do the following:
-
Click the Test tab, and then click Test Sample.
The Prompt Values dialog box appears.
-
In the Name column, click Order Number.
-
In the Value field, type 1234 and click OK.
One record is returned, showing the date for Order Number 1234.
Framework Manager uses this value for the duration of the current session or until you clear the prompt value.
-
Click OK.
点“Test”测试结果
输入两个参数，确认。结果如下：
导入的存储过程，类似于一个查询主题，自动装载输出结果字段名称。
Report Studio设计演示
发布到Web Server，用Report Studio设计一个带权限控制存储过程结果集，首先定义好权限控制用户信息查询，控制查询可以根据工号登录约束数据范围，关联字段是工号所属机构编码。再创建一个统计分析查询，记录事实数据信息。两个查询关联汇总到“汇总查询_FULL”查询。修改查询“处理”属性为“仅限本地”。
注意：关联时，“机构查询”与“统计分析”是1：1，或者是1：0
通过自定义一个工号8888，限制只能查询4个分公司数据。如下：
利用存储过程实时动态查询结果，解决不能处理复杂的算法，如10%的计算指标；考虑ETL每天刷新数据的压力，缩短数据处理时间，但影响系统开销，用户查看报表占用数据库资源，多用户同时执行同一报表选择不同条件，对系统开销增大，设计时要考虑数据量与硬件性能。
===================================================
以下是自己总结的cognos 在fm中创建 基于sp 的query subject 遇到的难题及解决办法
===================================================
问题描述：
做了个cognos报表，reportstudio做的，通过自定义sql（oracle环境）实现的，但是存在一个 亲和力问题，然后就打算把自定义sql改为存储过程，存储过程写好了，编译没问题，在 fm中 引入 sp数据源， 此时报错， RQP-DEF-0177 An error occurred while performing operation 'sqlExecute' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "execute".
ORA-06550: 第 1 行, 第 14 列:
PLS-00302: 必须说明 'PROC_XYK_MX_SHOP_TRAD' 组件
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
解决办法 ：
把报表的数据源中的 schema属性设置的和原来的相反即可，例如：原来为空，那么就写上 用户名，原来不空，那么就置空，问题搞定.
分析原因：
在网上搜索了半天，不少类似的帖子，但是只有提出问题的，却没有一个给出准确答案的。有的说是oracle环境的问题，解决办法就是，最好能搭建一个能执行成功的环境，看看cognos自动生成的执行sql就好了。
PLS-00302: 必须说明 'PROC_XYK_MX_SHOP_TRAD' 组件，显然是数据库在调用这个'PROC_XYK_MX_SHOP_TRAD‘ 时没找到，为什么没找到呢，明明就在这个用户下，在sqlplus中调用也没问题，可能原因：cognos自动生成的sql不是多了用户名就是少了用户名，对于少了用户名还好理解，一个数据库多个用户，不指定用户名，它不知道是哪个用户下的，加上用户名（schema）自然就能找到了。对于多了用户名，这就难理解了。一般人不会出这样的错误，就是把用户名写错，更不可能把用户名写成 username.username样式，多了用户此时该这样理解：是执行的 username.sp 还是执行的 sp。 oracle环境不一样配置也不一样，一个简单验证的方式就是在 cognos administration中，添加这个数据库的数据源，并执行测试，如果测试不用输入用户名和密码就能测试成功，那么在fm中 数据源的 schema 应置空（不填写）；如果测试需要输入用户名和密码才能测试成功，那么在fm中数据源的schema应写上用户名。
