正文

【BIEE】11_根据显示指标展示不同报表

IP云
IP云V管理员/4 阅读
11/29
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

报表开发过程中，我们经常会需要根据所选择的不同指标，展示不同的报表！例如：

显示指标有： 金额与合同数 ，可以根据显示指标选择的内容进行相应报表数据展示

一、环境准备 
create table crm_info(

crm_name varchar(100) ,

crm_no varchar(40) not null primary key ,

crm_type varchar(8),

crm_lvl varchar(8),

crm_status varchar(8),

crm_indu varchar(8),

crm_sex varchar(8),

crm_birthdate Date,

crm_dept varchar(8),

crm_country varchar(8),

crm_email varchar(20));

insert into crm_info  values('牧云笙','crm001','uvip','A','','A','',to_date('1987-10-02','yyyy-mm-dd'),'A01','CN','crm001@163.com');

insert into crm_info  values('苏语凝','crm002','vip','B','','S','',to_date('1986-1-2','yyyy-mm-dd'),'A04','USA','crm002@163.com');

insert into crm_info  values('南枯月漓','crm003','vip','D','','D','',to_date('1973-02-02','yyyy-mm-dd'),'A03','UK','crm003@163.com');

insert into crm_info  values('赫兰铁朵','crm004','uvip','B','','M','',to_date('1999-12-01','yyyy-mm-dd'),'A02','USA','crm004@163.com');

insert into crm_info  values('牧云寒','crm005','vip','D','','T','',to_date('1965-01-01','yyyy-mm-dd'),'A01','USA','crm005@163.com');

insert into crm_info  values('硕风和叶','crm006','uvip','D','','U','',to_date('1996-01-23','yyyy-mm-dd'),'A01','CN','crm006@163.com');

insert into crm_info  values('盼兮','crm007','uvip','C','','B','',to_date('1998-01-01','yyyy-mm-dd'),'A03','CN','crm007@163.com');

insert into crm_info  values('穆如寒江','crm008','vip','D','','S','',to_date('1976-12-4','yyyy-mm-dd'),'A02','CN','crm008@163.com');

insert into crm_info  values('兰钰儿','crm009','vip','A','','S','',to_date('1986-4-6','yyyy-mm-dd'),'A03','UK','crm009@163.com');

insert into crm_info  values('牧云陆','crm010','vip','B','','L','',to_date('1988-4-5','yyyy-mm-dd'),'A01','CN','crm010@163.com');

commit;

create table contract_info(

contract_no varchar(40) not null primary key,

crm_no varchar(40),

contract_amt decimal(18,2),

start_date varchar(8),

end_date varchar(8),

register_date varchar(8));

INSERT INTO contract_Info values('contract01','crm002','','','','');

INSERT INTO contract_Info values('contract02','crm003','','','','');

INSERT INTO contract_Info values('contract03','crm004','','','','');

INSERT INTO contract_Info values('contract04','crm005','','','','');

INSERT INTO contract_Info values('contract05','crm006','','','','');

INSERT INTO contract_Info values('contract06','crm007','','','','');

INSERT INTO contract_Info values('contract07','crm002','','','','');

INSERT INTO contract_Info values('contract08','crm003','','','','');

INSERT INTO contract_Info values('contract09','crm004','','','','');

INSERT INTO contract_Info values('contract10','crm005','','','','');

INSERT INTO contract_Info values('contract11','crm006','','','','');

create table mapping(

Type VARCHAR(20),

Key varchar(4),

Value varchar(100));

INSERT INTO mapping VALUES('crm_lvl','vip','vip客户');

INSERT INTO mapping VALUES('crm_lvl','uvip','非vip客户');

INSERT INTO mapping VALUES('crm_type','A','优秀');

INSERT INTO mapping VALUES('crm_type','B','良好');

INSERT INTO mapping VALUES('crm_type','C','中等');

INSERT INTO mapping VALUES('crm_type','D','一般');

INSERT INTO mapping VALUES('crm_status','','存续客户');

INSERT INTO mapping VALUES('crm_status','','流失客户');

INSERT INTO mapping VALUES('crm_indu','A','农、牧、林、渔业');

INSERT INTO mapping VALUES('crm_indu','B','采矿和采石');

INSERT INTO mapping VALUES('crm_indu','C','制造业');

INSERT INTO mapping VALUES('crm_indu','D','电、煤气、蒸汽和空调供应');

INSERT INTO mapping VALUES('crm_indu','E','供水；污水处理、废物管理和补救');

INSERT INTO mapping VALUES('crm_indu','F','建筑业');

INSERT INTO mapping VALUES('crm_indu','G','批发和零售业；汽车和摩托车修理');

INSERT INTO mapping VALUES('crm_indu','H','运输与存储');

INSERT INTO mapping VALUES('crm_indu','I','食宿服务');

INSERT INTO mapping VALUES('crm_indu','J','信息和通信');

INSERT INTO mapping VALUES('crm_indu','K','金融和保险');

INSERT INTO mapping VALUES('crm_indu','L','房地产');

INSERT INTO mapping VALUES('crm_indu','M','专业、科学和技术');

INSERT INTO mapping VALUES('crm_indu','N','行政和辅助');

INSERT INTO mapping VALUES('crm_indu','O','公共管理与国防；强制性社会保障');

INSERT INTO mapping VALUES('crm_indu','P','教育');

INSERT INTO mapping VALUES('crm_indu','Q','人体健康和社会工作');

INSERT INTO mapping VALUES('crm_indu','R','艺术、娱乐和文娱');

INSERT INTO mapping VALUES('crm_indu','S','其他服务');

INSERT INTO mapping VALUES('crm_indu','T','家庭作为雇主的；家庭自用、未加区分的物品生产和服务');

INSERT INTO mapping VALUES('crm_indu','U','国际组织和机构');

INSERT INTO mapping VALUES('crm_sex','','男');

INSERT INTO mapping VALUES('crm_sex','','女');

INSERT INTO mapping VALUES('crm_dept','A01','财务部');

INSERT INTO mapping VALUES('crm_dept','A02','人事部');

INSERT INTO mapping VALUES('crm_dept','A03','采购部');

INSERT INTO mapping VALUES('crm_dept','A04','市场部');

INSERT INTO mapping VALUES('crm_country','CN','中国');

INSERT INTO mapping VALUES('crm_country','USA','美国');

INSERT INTO mapping VALUES('crm_country','UK','英国');

commit;

------建立维度视图

CREATE OR REPLACE VIEW V_DIM_CRM_LVL

AS SELECT KEY,VALUE FROM mapping WHERE Type='crm_lvl';

CREATE OR REPLACE VIEW V_DIM_CRM_TYPE

AS SELECT KEY,VALUE FROM mapping WHERE Type='crm_type';

CREATE OR REPLACE VIEW V_DIM_CRM_STATUS

AS SELECT KEY,VALUE FROM mapping WHERE Type='crm_status';

CREATE OR REPLACE VIEW V_DIM_CRM_INDU

AS SELECT KEY,VALUE FROM mapping WHERE Type='crm_indu';

CREATE OR REPLACE VIEW V_DIM_CRM_SEX

AS SELECT KEY,VALUE FROM mapping WHERE Type='crm_sex';

CREATE OR REPLACE VIEW V_DIM_CRM_DEPT

AS SELECT KEY,VALUE FROM mapping WHERE Type='crm_dept';

CREATE OR REPLACE VIEW V_DIM_CRM_COUNTRY

AS SELECT KEY,VALUE FROM mapping WHERE Type='crm_country';

-----建立合同客户关系视图

CREATE OR REPLACE VIEW CRM_CONTRACT_RELATION

AS

SELECT A.CONTRACT_NO,A.CONTRACT_AMT,A.START_DATE,A.END_DATE,A.REGISTER_DATE

,B.* FROM contract_Info A INNER JOIN CRM_INFO B ON A.CRM_NO=B.CRM_NO

根据上述脚本建表后，可得到如下数据：

CRM_CONTRACT_RELATION

V_DIM_CRM_COUNTRY

将上述两个视图导入到资料库，然后关联，接着设计分析

二、仪表盘提示设计

使用变量VAR接受值，然后传值给分析，在分析中通过 @{VAR} 进行引用

三、分析设计

①国家：

"V_DIM_CRM_COUNTRY"."VALUE"

②vip金额：

ifnull(CASE WHEN '@{VAR}{金额}'='金额' AND "CRM_CONTRACT_RELATION"."CRM_TYPE"='vip' THEN "CRM_CONTRACT_RELATION"."合同金额" WHEN  '@{VAR}'='合同数' AND  "CRM_CONTRACT_RELATION"."CRM_TYPE"='vip'  THEN  "CRM_CONTRACT_RELATION"."CNT"  ELSE NULL END,0)

③非vip金额：

ifnull(CASE WHEN '@{VAR}{金额}'='金额' AND "CRM_CONTRACT_RELATION"."CRM_TYPE"='uvip' THEN "CRM_CONTRACT_RELATION"."合同金额" WHEN  '@{VAR}'='合同数' AND  "CRM_CONTRACT_RELATION"."CRM_TYPE"='uvip'  THEN  "CRM_CONTRACT_RELATION"."CNT"  ELSE NULL END,0)

④金额：

'@{VAR}{金额}'

四、仪表盘设计

将分析与仪表盘结合在一起，然后展示报表

五、展示报表

当选择【金额】时，

当选择【合同数】时

通过以上可以发现，我们的报表现在可以通过选择的【显示指标】不一致，显示不同的结果。

相关阅读

目录[+]