NC65自由报表参数设置后，报表发布成节点后，点击查询，在查询框输入条件后，参数值没有拼接到sql中的解决方法

NC65自由报表参数设置后，报表发布成节点后，点击查询，在查询框输入条件后，参数值没有拼接到sql中的解决方法

在语义分析模型中把sql语句放进去，把字段和查询查询设置好

语义模型的sql使用了parameter把参数输入

select a.org, a.financeorg, a.cp, a.deptname1, a.dept, a.psnname, a.id, a.hkxz, a.pk_group, a.pk_psndoc, 
isnull(b.ylf1,0) ylf1, 
isnull(b.ylf5,0) ylf5, 
isnull(b.ylf10,0) ylf10, 
isnull(b.ylf11,0) ylf11, 
isnull(b.ylf13,0) ylf13, 
isnull(c.syf1,0) syf1, 
isnull(c.syf5,0) syf5, 
isnull(c.syf10,0) syf10, 
isnull(c.syf11,0) syf11, 
isnull(c.syf13,0) syf13, 
isnull(d.gsf1,0) gsf1, 
isnull(d.gsf5,0) gsf5, 
isnull(d.gsf11,0) gsf11, 
isnull(e.shylf1,0) shylf1, 
isnull(e.shylf5,0) shylf5, 
isnull(e.shylf10,0) shylf10, 
isnull(e.shylf11,0) shylf11, 
isnull(e.shylf13,0) shylf13, 
isnull(f.sybf1,0) sybf1, 
isnull(f.sybf5,0) sybf5, 
isnull(f.sybf11,0) sybf11, 

isnull(j.ylhsyf1,0) ylhsyf1, 
isnull(j.ylhsyf5,0) ylhsyf5, 
isnull(j.ylhsyf10,0) ylhsyf10, 
isnull(j.ylhsyf11,0) ylhsyf11, 
isnull(j.ylhsyf13,0) ylhsyf13, 
isnull(isnull(b.ylf5,0)+isnull(c.syf5,0)+isnull(d.gsf5,0)+isnull(e.shylf5,0)+isnull(f.sybf5,0)+isnull(j.ylhsyf5,0),0) dwjjhjf5, 
isnull(isnull(b.ylf10,0)+isnull(c.syf10,0)+isnull(e.shylf10,0)+isnull(j.ylhsyf10,0),0) grjjhjf10, 
isnull(isnull(b.ylf11,0)+isnull(c.syf11,0)+isnull(d.gsf11,0)+isnull(e.shylf11,0)+isnull(f.sybf11,0)+isnull(j.ylhsyf11,0),0) dwbjhjf11, 
isnull(isnull(b.ylf13,0)+isnull(c.syf13,0)+isnull(e.shylf13,0)+isnull(j.ylhsyf13,0),0) grbjhjf13, 
isnull(isnull(b.ylf5,0)+isnull(c.syf5,0)+isnull(d.gsf5,0)+isnull(e.shylf5,0)+isnull(f.sybf5,0)+isnull(j.ylhsyf5,0)+isnull(b.ylf11,0)+isnull(c.syf11,0)+isnull(d.gsf11,0)+isnull(e.shylf11,0)+isnull(f.sybf11,0)+isnull(j.ylhsyf11,0),0) dwhj, 
isnull(isnull(b.ylf10,0)+isnull(c.syf10,0)+isnull(e.shylf10,0)+isnull(j.ylhsyf10,0)+isnull(b.ylf13,0)+isnull(c.syf13,0)+isnull(e.shylf13,0)+isnull(j.ylhsyf13,0),0) gghj, 
a.orgcode, a.deptcode1, a.deptcode, a.psncode, a.financeorgcode, a.pk_financeorg
from (select distinct e.name org, parameter('param1')+'-'+parameter('param2') cp, f.deptname1, f.deptname dept, c.name psnname, c.id, g.name hkxz, a.pk_group, a.pk_psndoc, a.workorg, e.code orgcode, f.deptcode1, f.deptcode, c.code psncode, i.code financeorgcode, i.name financeorg, e.pk_org, a.pk_financeorg
from bm_data a
inner join bd_psndoc c on a.pk_psndoc=c.pk_psndoc
inner join hi_psnorg h on a.pk_psndoc=h.pk_psndoc
inner join hi_psnjob d on a.workdept=d.pk_dept
inner join org_orgs e on d.pk_org=e.pk_org
inner join v_dept_tree f on d.pk_dept=f.pk_dept
inner join (select a.pk_psndoc, max(a.cyear+a.cperiod) cp
from bm_data a
where a.cyear+a.cperiod between parameter('param1') and parameter('param2')
group by a.pk_psndoc) b on a.pk_psndoc=b.pk_psndoc and a.cyear+a.cperiod=b.cp
left join org_orgs i on a.pk_financeorg=i.pk_org
left join bd_defdoc g on c.characterrpr=g.pk_defdoc
where h.lastflag='Y' and d.lastflag='Y' and d.ismainjob='Y') a
--养老保险
left join (select a.pk_psndoc, sum(a.f_1) ylf1, sum(a.f_5) ylf5, sum(a.f_10) ylf10, sum(a.f_11) ylf11, sum(a.f_13) ylf13
from bm_data a
inner join bm_bmclass b on a.pk_bm_class=b.pk_bm_class
where a.cyear+a.cperiod between parameter('param1') and parameter('param2') and b.name='职工基本养老保险'
group by a.pk_psndoc) b on a.pk_psndoc=b.pk_psndoc
--失业保险
left join (select a.pk_psndoc, sum(a.f_1) syf1, sum(a.f_5) syf5, sum(a.f_10) syf10, sum(a.f_11) syf11, sum(a.f_13) syf13
from bm_data a
inner join bm_bmclass b on a.pk_bm_class=b.pk_bm_class
where a.cyear+a.cperiod between parameter('param1') and parameter('param2') and b.name='失业保险'
group by a.pk_psndoc) c on a.pk_psndoc=c.pk_psndoc
--工伤保险
left join (select a.pk_psndoc, sum(a.f_1) gsf1, sum(a.f_5) gsf5, sum(a.f_11) gsf11
from bm_data a
inner join bm_bmclass b on a.pk_bm_class=b.pk_bm_class
where a.cyear+a.cperiod between parameter('param1') and parameter('param2') and b.name='工伤保险'
group by a.pk_psndoc) d on a.pk_psndoc=d.pk_psndoc
--医疗保险
left join (select a.pk_psndoc, sum(a.f_1) shylf1, sum(a.f_5) shylf5, sum(a.f_10) shylf10, sum(a.f_11) shylf11, sum(a.f_13) shylf13
from bm_data a
inner join bm_bmclass b on a.pk_bm_class=b.pk_bm_class
where a.cyear+a.cperiod between parameter('param1') and parameter('param2') and b.name='职工社会医疗保险'
group by a.pk_psndoc) e on a.pk_psndoc=e.pk_psndoc
--生育保险
left join (select a.pk_psndoc, sum(a.f_1) sybf1, sum(a.f_5) sybf5, sum(a.f_11) sybf11
from bm_data a
inner join bm_bmclass b on a.pk_bm_class=b.pk_bm_class
where a.cyear+a.cperiod between parameter('param1') and parameter('param2') and b.name='生育保险'
group by a.pk_psndoc) f on a.pk_psndoc=f.pk_psndoc
--重疾
--left join (select a.pk_psndoc, sum(a.f_1) zdf1, sum(a.f_5) zdf5, sum(a.f_11) zdf11
--from bm_data a
--inner join bm_bmclass b on a.pk_bm_class=b.pk_bm_class
--where a.cyear+a.cperiod between parameter('param1') and parameter('param2') and b.name='职工重大疾病医疗补助'
--group by a.pk_psndoc) g on a.pk_psndoc=g.pk_psndoc
--补充医疗
--left join (select a.pk_psndoc, sum(a.f_1) bcf1, sum(a.f_5) bcf5, sum(a.f_11) bcf11, sum(a.f_13) bcf13
--from bm_data a
--inner join bm_bmclass b on a.pk_bm_class=b.pk_bm_class
--where a.cyear+a.cperiod between parameter('param1') and parameter('param2') and b.name='职工补充医疗保险'
--group by a.pk_psndoc) h on a.pk_psndoc=h.pk_psndoc
--医疗保险含生育
left join (select a.pk_psndoc, sum(a.f_1) ylhsyf1, sum(a.f_5) ylhsyf5, sum(a.f_10) ylhsyf10, sum(a.f_11) ylhsyf11, sum(a.f_13) ylhsyf13
from bm_data a
inner join bm_bmclass b on a.pk_bm_class=b.pk_bm_class
where a.cyear+a.cperiod between parameter('param1') and parameter('param2') and b.name='基本医疗保险（含生育）'
group by a.pk_psndoc) j on a.pk_psndoc=j.pk_psndoc
--查询条件
where a.pk_org in (parameter('param3')) and a.pk_psndoc in (parameter('param4')) and a.pk_org in (select c.pk_org from sm_user a inner join sm_user_role b on a.cuserid=b.cuserid inner join sm_subject_org c on c.subjectid=b.pk_role where a.cuserid=macro('LoginUser')) and a.pk_financeorg in (parameter('param5'))
order by a.financeorgcode, a.orgcode, a.deptcode1, a.deptcode, a.psncode

注意，如果参照想要多选，sql语句的查询条件用in时，要加()，如

设计完报表表样后，发布成节点，打开节点进行相应的参数查询，如下图：

得到的结果居然是全部的，并不是查询参数的。

根据录取的日志，得到sql确实是没把查询参数传入进去:

SELECT t_1.cp CP , t_1.org ORG , t_1.financeorg FINANCEORG , t_1.deptname1 DEPTNAME1 , t_1.dept DEPT , t_1.psncode PSNCODE , t_1.psnname PSNNAME , t_1.id ID , t_1.hkxz HKXZ , t_1.ylf1 YLF1 , t_1.ylf5 YLF5 , t_1.ylf10 YLF10 , t_1.ylf11 YLF11 , t_1.ylf13 YLF13 , t_1.syf1 SYF1 , t_1.syf5 SYF5 , t_1.syf10 SYF10 , t_1.syf11 SYF11 , t_1.syf13 SYF13 , t_1.gsf1 GSF1 , t_1.gsf5 GSF5 , t_1.gsf11 GSF11 , t_1.shylf1 SHYLF1 , t_1.shylf5 SHYLF5 , t_1.shylf10 SHYLF10 , t_1.shylf11 SHYLF11 , t_1.shylf13 SHYLF13 , t_1.sybf1 SYBF1 , t_1.sybf5 SYBF5 , t_1.sybf11 SYBF11 , t_1.ylhsyf1 YLHSYF1 , t_1.ylhsyf5 YLHSYF5 , t_1.ylhsyf10 YLHSYF10 , t_1.ylhsyf11 YLHSYF11 , t_1.ylhsyf13 YLHSYF13 , t_1.dwjjhjf5 DWJJHJF5 , t_1.grjjhjf10 GRJJHJF10 , t_1.dwbjhjf11 DWBJHJF11 , t_1.grbjhjf13 GRBJHJF13 , t_1.dwhj DWHJ , t_1.gghj GGHJ , t_1.dwhj + t_1.gghj zhj FROM ( SELECT a.org org , a.financeorg financeorg , a.cp cp , a.deptname1 deptname1 , a.dept dept , a.psnname psnname , a.id id , a.hkxz hkxz , a.pk_group pk_group , a.pk_psndoc pk_psndoc , isnull ( b.ylf1 , 0 ) ylf1 , isnull ( b.ylf5 , 0 ) ylf5 , isnull ( b.ylf10 , 0 ) ylf10 , isnull ( b.ylf11 , 0 ) ylf11 , isnull ( b.ylf13 , 0 ) ylf13 , isnull ( c.syf1 , 0 ) syf1 , isnull ( c.syf5 , 0 ) syf5 , isnull ( c.syf10 , 0 ) syf10 , isnull ( c.syf11 , 0 ) syf11 , isnull ( c.syf13 , 0 ) syf13 , isnull ( d.gsf1 , 0 ) gsf1 , isnull ( d.gsf5 , 0 ) gsf5 , isnull ( d.gsf11 , 0 ) gsf11 , isnull ( e.shylf1 , 0 ) shylf1 , isnull ( e.shylf5 , 0 ) shylf5 , isnull ( e.shylf10 , 0 ) shylf10 , isnull ( e.shylf11 , 0 ) shylf11 , isnull ( e.shylf13 , 0 ) shylf13 , isnull ( f.sybf1 , 0 ) sybf1 , isnull ( f.sybf5 , 0 ) sybf5 , isnull ( f.sybf11 , 0 ) sybf11 , isnull ( j.ylhsyf1 , 0 ) ylhsyf1 , isnull ( j.ylhsyf5 , 0 ) ylhsyf5 , isnull ( j.ylhsyf10 , 0 ) ylhsyf10 , isnull ( j.ylhsyf11 , 0 ) ylhsyf11 , isnull ( j.ylhsyf13 , 0 ) ylhsyf13 , isnull ( isnull ( b.ylf5 , 0 ) + isnull ( c.syf5 , 0 ) + isnull ( d.gsf5 , 0 ) + isnull ( e.shylf5 , 0 ) + isnull ( f.sybf5 , 0 ) + isnull ( j.ylhsyf5 , 0 ) , 0 ) dwjjhjf5 , isnull ( isnull ( b.ylf10 , 0 ) + isnull ( c.syf10 , 0 ) + isnull ( e.shylf10 , 0 ) + isnull ( j.ylhsyf10 , 0 ) , 0 ) grjjhjf10 , isnull ( isnull ( b.ylf11 , 0 ) + isnull ( c.syf11 , 0 ) + isnull ( d.gsf11 , 0 ) + isnull ( e.shylf11 , 0 ) + isnull ( f.sybf11 , 0 ) + isnull ( j.ylhsyf11 , 0 ) , 0 ) dwbjhjf11 , isnull ( isnull ( b.ylf13 , 0 ) + isnull ( c.syf13 , 0 ) + isnull ( e.shylf13 , 0 ) + isnull ( j.ylhsyf13 , 0 ) , 0 ) grbjhjf13 , isnull ( isnull ( b.ylf5 , 0 ) + isnull ( c.syf5 , 0 ) + isnull ( d.gsf5 , 0 ) + isnull ( e.shylf5 , 0 ) + isnull ( f.sybf5 , 0 ) + isnull ( j.ylhsyf5 , 0 ) + isnull ( b.ylf11 , 0 ) + isnull ( c.syf11 , 0 ) + isnull ( d.gsf11 , 0 ) + isnull ( e.shylf11 , 0 ) + isnull ( f.sybf11 , 0 ) + isnull ( j.ylhsyf11 , 0 ) , 0 ) dwhj , isnull ( isnull ( b.ylf10 , 0 ) + isnull ( c.syf10 , 0 ) + isnull ( e.shylf10 , 0 ) + isnull ( j.ylhsyf10 , 0 ) + isnull ( b.ylf13 , 0 ) + isnull ( c.syf13 , 0 ) + isnull ( e.shylf13 , 0 ) + isnull ( j.ylhsyf13 , 0 ) , 0 ) gghj , a.orgcode orgcode , a.deptcode1 deptcode1 , a.deptcode deptcode , a.psncode psncode , a.financeorgcode financeorgcode , a.pk_financeorg pk_financeorg FROM ( SELECT DISTINCT e.name org , N'-' cp , f.deptname1 deptname1 , f.deptname dept , c.name psnname , c.id id , g.name hkxz , a.pk_group pk_group , a.pk_psndoc pk_psndoc , a.workorg workorg , e.code orgcode , f.deptcode1 deptcode1 , f.deptcode deptcode , c.code psncode , i.code financeorgcode , i.name financeorg , e.pk_org pk_org , a.pk_financeorg pk_financeorg FROM bm_data a INNER JOIN bd_psndoc c ON a.pk_psndoc = c.pk_psndoc INNER JOIN hi_psnorg h ON a.pk_psndoc = h.pk_psndoc INNER JOIN hi_psnjob d ON a.workdept = d.pk_dept INNER JOIN org_orgs e ON d.pk_org = e.pk_org INNER JOIN v_dept_tree f ON d.pk_dept = f.pk_dept INNER JOIN ( SELECT a.pk_psndoc pk_psndoc , max ( a.cyear + a.cperiod ) cp FROM bm_data a GROUP BY a.pk_psndoc ) b ON a.pk_psndoc = b.pk_psndoc AND a.cyear + a.cperiod = b.cp LEFT outer JOIN org_orgs i ON a.pk_financeorg = i.pk_org LEFT outer JOIN bd_defdoc g ON c.characterrpr = g.pk_defdoc WHERE h.lastflag = N'Y' AND d.lastflag = N'Y' AND d.ismainjob = N'Y' ) a LEFT outer JOIN ( SELECT a.pk_psndoc pk_psndoc , sum ( a.f_1 ) ylf1 , sum ( a.f_5 ) ylf5 , sum ( a.f_10 ) ylf10 , sum ( a.f_11 ) ylf11 , sum ( a.f_13 ) ylf13 FROM bm_data a INNER JOIN bm_bmclass b ON a.pk_bm_class = b.pk_bm_class WHERE b.name = N'职工基本养老保险' GROUP BY a.pk_psndoc ) b ON a.pk_psndoc = b.pk_psndoc LEFT outer JOIN ( SELECT a.pk_psndoc pk_psndoc , sum ( a.f_1 ) syf1 , sum ( a.f_5 ) syf5 , sum ( a.f_10 ) syf10 , sum ( a.f_11 ) syf11 , sum ( a.f_13 ) syf13 FROM bm_data a INNER JOIN bm_bmclass b ON a.pk_bm_class = b.pk_bm_class WHERE b.name = N'失业保险' GROUP BY a.pk_psndoc ) c ON a.pk_psndoc = c.pk_psndoc LEFT outer JOIN ( SELECT a.pk_psndoc pk_psndoc , sum ( a.f_1 ) gsf1 , sum ( a.f_5 ) gsf5 , sum ( a.f_11 ) gsf11 FROM bm_data a INNER JOIN bm_bmclass b ON a.pk_bm_class = b.pk_bm_class WHERE b.name = N'工伤保险' GROUP BY a.pk_psndoc ) d ON a.pk_psndoc = d.pk_psndoc LEFT outer JOIN ( SELECT a.pk_psndoc pk_psndoc , sum ( a.f_1 ) shylf1 , sum ( a.f_5 ) shylf5 , sum ( a.f_10 ) shylf10 , sum ( a.f_11 ) shylf11 , sum ( a.f_13 ) shylf13 FROM bm_data a INNER JOIN bm_bmclass b ON a.pk_bm_class = b.pk_bm_class WHERE b.name = N'职工社会医疗保险' GROUP BY a.pk_psndoc ) e ON a.pk_psndoc = e.pk_psndoc LEFT outer JOIN ( SELECT a.pk_psndoc pk_psndoc , sum ( a.f_1 ) sybf1 , sum ( a.f_5 ) sybf5 , sum ( a.f_11 ) sybf11 FROM bm_data a INNER JOIN bm_bmclass b ON a.pk_bm_class = b.pk_bm_class WHERE b.name = N'生育保险' GROUP BY a.pk_psndoc ) f ON a.pk_psndoc = f.pk_psndoc LEFT outer JOIN ( SELECT a.pk_psndoc pk_psndoc , sum ( a.f_1 ) ylhsyf1 , sum ( a.f_5 ) ylhsyf5 , sum ( a.f_10 ) ylhsyf10 , sum ( a.f_11 ) ylhsyf11 , sum ( a.f_13 ) ylhsyf13 FROM bm_data a INNER JOIN bm_bmclass b ON a.pk_bm_class = b.pk_bm_class WHERE b.name = N'基本医疗保险（含生育）' GROUP BY a.pk_psndoc ) j ON a.pk_psndoc = j.pk_psndoc WHERE a.pk_org IN ( SELECT c.pk_org pk_org FROM sm_user a INNER JOIN sm_user_role b ON a.cuserid = b.cuserid INNER JOIN sm_subject_org c ON c.subjectid = b.pk_role WHERE a.cuserid = N'1001A11000000006PCQM' ) ) t_1 WHERE t_1.pk_group = N'0001A11000000000037X' ORDER BY deptcode1 , financeorgcode , deptcode , orgcode , psncode

解决方案：
如下图，在【报表设计-全局/集团/组织】节点中，点击参数

补充：

在弹框中，设置取值方式和参数值，确定后，保存报表即可，如下图：

然后关掉节点重新打开，点查询输入查询条件，如下图：

点击确定按钮后，结果就是参数值的结果，如下图：

报表序号的设置：
使用单元格公式—— ROWNO()

点击单元格右键，选择单元格公式。

选择公式 ROWNO()，点击保存即可。