报表其实就是根据客户需求–需要哪些字段反馈给甲方，比如我们有一千多个表，需要的字段在各个表中都可能会有涉及

首先到语义建模进行sql的拼写

在这里可以根据客户给的excel比如

这种，对此进行sql，我的sql如下：

WITH RECURSIVE org_hierarchy AS (
  SELECT 
    id,
    parentid,
    name,
    1 AS level,
    CAST(name AS CHAR(255)) AS full_path
  FROM iuap_apdoc_basedoc.org_admin
  WHERE parentid NOT IN (SELECT id FROM iuap_apdoc_basedoc.org_admin)
  UNION ALL
  SELECT 
    o.id,
    o.parentid,
    o.name,
    oh.level + 1,
    CONCAT(oh.full_path, '/', o.name)
  FROM iuap_apdoc_basedoc.org_admin o
  JOIN org_hierarchy oh ON o.parentid = oh.id
)
SELECT  
  count(*) as '人数',
  SUBSTRING_INDEX(SUBSTRING_INDEX(oh.full_path, '/', 2), '/', -1) as 上级部门,
  SUBSTRING_INDEX(SUBSTRING_INDEX(oh.full_path, '/', 3), '/', -1) as 二级部门,
  CASE 
    WHEN LOCATE('省', t3.name) > 0 THEN LEFT(t3.name, LOCATE('省', t3.name))
    ELSE t3.name
  END as '籍贯'
FROM iuap_apdoc_basedoc.bd_staff t1
JOIN iuap_apdoc_basedoc.org_admin t2 ON t1.dept_id = t2.id
JOIN iuap_apdoc_basedoc.bd_baseregion t3 ON t1.origin = t3.id
JOIN org_hierarchy oh ON oh.id = t1.dept_id
WHERE 1=1
<#if param('部门')!=''>
   AND t1.dept_id IN (
    <#list param('部门') ? split(",") as item > '${item}' <#if item ? has_next >,</#if > </#list >
  )
</#if>
GROUP BY 
  t2.name,
  SUBSTRING_INDEX(SUBSTRING_INDEX(oh.full_path, '/', 2), '/', -1),
  SUBSTRING_INDEX(SUBSTRING_INDEX(oh.full_path, '/', 3), '/', -1),
  CASE 
    WHEN LOCATE('省', t3.name) > 0 THEN LEFT(t3.name, LOCATE('省', t3.name))
    ELSE t3.name
  END

语义建模达到目的后就（发布后期再设计报表的时候可以引用）

报表设计：

根据我们的要求进行参数与字段的拼接：

点到某个字段后可以进行设置：

然后

这是效果！