在任意一个项目,涉及到报表的数据展现时,都得考虑不同的部门及账号等打开报表之后分别能看到什么数据。这就涉及到了报表数据权限的问题。
常规的,数据权限都会在后台通过写SQL语句进行控制。BO顾问会在后台设计一张专门用于做数据权限的后台表,然后通过围绕这张后台表写SQL做数据权限控制。
如下场景:
不同账号登录BO后,分别能看到该账号本身所属组织的或其以下组织的数据;
第一步:后台开发如下后台控制表,列出系统内所有人员及其组织信息。同时,有字段标记该条人员数据的最小组织层级及对应编码。mini_sort,mini_org
第二步:通过写SQL 语句实现逻辑控制。代码如下:
/********* Begin Procedure Script ************/
BEGIN
declare ini_all integer; --初始值为0
declare s0 integer;
declare s1 integer;
declare s2 integer;
declare s3 integer;
declare s4 integer;
declare s5 integer;
declare s6 integer;
declare s7 integer;
declare s8 integer;
sort_rs = select *
from SAPQ01.zcrmt_016n t16_1
where mandt = '500';
-- and uname = trim(varuname);
sort_rs1 = select distinct mandt,partner,sort1,sort2,sort3,sort4,sort5,sort6,sort7,sort8,
sort11,sort22,sort33,sort44,sort55,sort66,sort77,sort88,sysdat,sysdatend,mini_org
from SAPQ01.zcrmt_016n t16_3
where mandt = '500' ;
select count(*) into s0 from :sort_rs where mini_sort = '' and uname = trim(varuname);
select count(*) into s1 from :sort_rs where mini_sort = 'SORT1' and uname = trim(varuname);
select count(*) into s2 from :sort_rs where mini_sort = 'SORT2' and uname = trim(varuname);
select count(*) into s3 from :sort_rs where mini_sort = 'SORT3' and uname = trim(varuname);
select count(*) into s4 from :sort_rs where mini_sort = 'SORT4' and uname = trim(varuname);
select count(*) into s5 from :sort_rs where mini_sort = 'SORT5' and uname = trim(varuname);
select count(*) into s6 from :sort_rs where mini_sort = 'SORT6' and uname = trim(varuname);
select count(*) into s7 from :sort_rs where mini_sort = 'SORT7' and uname = trim(varuname);
select count(*) into s8 from :sort_rs where mini_sort = 'SORT8' and uname = trim(varuname);
if :s0 >= 1 then
sql_s0 =
select
t16_1.mandt,
t16_1.uname as "UNAME",
--t16_1.partner,
t16_1_1.partner,
t16_1_1.sort1,
t16_1_1.sort2,
t16_1_1.sort3,
t16_1_1.sort4,
t16_1_1.sort5,
t16_1_1.sort6,
t16_1_1.sort7,
t16_1_1.sort8,
t16_1_1.SORT11,
t16_1_1.SORT22,
t16_1_1.SORT33,
t16_1_1.SORT44,
t16_1_1.sort55,
t16_1_1.sort66,
t16_1_1.sort77,
t16_1_1.sort88,
t16_1_1.sysdat,
t16_1_1.sysdatend,
t16_1_1.mini_org
from :sort_rs t16_1
left outer join :sort_rs1 t16_1_1
on (t16_1_1.mandt = t16_1.mandt)
where t16_1.mandt = '500' and t16_1.uname = trim(varuname);
end if;
if :s1 >= 1 then
sql_s1 =
select
t16_1.mandt,
t16_1.uname as "UNAME",
--t16_1.partner,
t16_1_1.partner,
t16_1_1.sort1,
t16_1_1.sort2,
t16_1_1.sort3,
t16_1_1.sort4,
t16_1_1.sort5,
<