-
-
yangm 数据达人Lv4
发表于2020-3-3 18:08
楼主
本帖最后由 yangm 于 2020-12-14 16:45 编辑
bi在做表时 ，往往能遇到本级有数据 但是绑定维表之后设置了下钻级次，系统会将下级的数据汇总到本级导致本级的数据被覆盖。如图下级数据将本级的数据覆盖。
计算成sql如下：
select row_.*,rownum from ( select sum(a.DQ) as B1, SUBSTR(a.XZQHSZ_DM,1,2) as A1
from MODEL_ND_HYZJZ_CLXX_VIEW a
where (a.XZQHSZ_DM like '11%' or a.XZQHSZ_DM like '32%' or a.XZQHSZ_DM like '37%' or a.XZQHSZ_DM like '41%' or a.XZQHSZ_DM like '44%')
group by SUBSTR(a.XZQHSZ_DM,1,2) )row_ where rownum <= 1000
那么我们怎么做能避开这个问题呢。如图，过滤条件中写入过滤条件：dim(right(QQQ.XZQHSZ_DM,4)='0000') )将下级的数据全部过滤掉，剩下的数据就是本级的数据。
因为省级维表id右边4位都是0，所以取右边四位为零的id去汇总。
加过滤条件后查到的计算sql：select row_.*,rownum from ( select sum(a.DQ) as B1, SUBSTR(a.XZQHSZ_DM,1,2) as A1
from MODEL_ND_HYZJZ_CLXX_VIEW a
where (SUBSTR(a.XZQHSZ_DM,-4) = '0000' AND (a.XZQHSZ_DM like '11%' or a.XZQHSZ_DM like '32%' or a.XZQHSZ_DM like '37%' or a.XZQHSZ_DM like '41%' or a.XZQHSZ_DM like '44%'))
group by SUBSTR(a.XZQHSZ_DM,1,2) )row_ where rownum <= 1000
(SUBSTR(a.XZQHSZ_DM,-4) = '0000'这句话就已经将不需要汇总的数据去掉啦。
简单的过滤就将问解决了。
bi在做表时 ，往往能遇到本级有数据 但是绑定维表之后设置了下钻级次，系统会将下级的数据汇总到本级导致本级的数据被覆盖。如图下级数据将本级的数据覆盖。
select row_.*,rownum from ( select sum(a.DQ) as B1, SUBSTR(a.XZQHSZ_DM,1,2) as A1
from MODEL_ND_HYZJZ_CLXX_VIEW a
where (a.XZQHSZ_DM like '11%' or a.XZQHSZ_DM like '32%' or a.XZQHSZ_DM like '37%' or a.XZQHSZ_DM like '41%' or a.XZQHSZ_DM like '44%')
group by SUBSTR(a.XZQHSZ_DM,1,2) )row_ where rownum <= 1000
那么我们怎么做能避开这个问题呢。如图，过滤条件中写入过滤条件：dim(right(QQQ.XZQHSZ_DM,4)='0000') )将下级的数据全部过滤掉，剩下的数据就是本级的数据。
因为省级维表id右边4位都是0，所以取右边四位为零的id去汇总。
加过滤条件后查到的计算sql：select row_.*,rownum from ( select sum(a.DQ) as B1, SUBSTR(a.XZQHSZ_DM,1,2) as A1
from MODEL_ND_HYZJZ_CLXX_VIEW a
where (SUBSTR(a.XZQHSZ_DM,-4) = '0000' AND (a.XZQHSZ_DM like '11%' or a.XZQHSZ_DM like '32%' or a.XZQHSZ_DM like '37%' or a.XZQHSZ_DM like '41%' or a.XZQHSZ_DM like '44%'))
group by SUBSTR(a.XZQHSZ_DM,1,2) )row_ where rownum <= 1000
(SUBSTR(a.XZQHSZ_DM,-4) = '0000'这句话就已经将不需要汇总的数据去掉啦。
简单的过滤就将问解决了。
