After the NC65 free report parameters are set, after the report is published as a node, click on the query, and after entering the conditions in the query box, the parameter value is not spliced into the sql solution
Put the sql statement in the semantic analysis model, set the fields and query query
The sql of the semantic model uses parameter to input parameters
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.dept code, 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
--Pension
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='employee basic pension insurance'
group by a.pk_psndoc) b on a.pk_psndoc=b.pk_psndoc
-- Unemployment Insurance
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='unemployment insurance'
group by a.pk_psndoc) c on a.pk_psndoc=c.pk_psndoc
--Injury insurance
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='work injury insurance'
group by a.pk_psndoc) d on a.pk_psndoc=d.pk_psndoc
--medical insurance
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='employee social medical insurance'
group by a.pk_psndoc) e on a.pk_psndoc=e.pk_psndoc
--Maternity Insurance
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='maternity insurance'
group by a.pk_psndoc) f on a.pk_psndoc=f.pk_psndoc
-- serious illness
--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='Employee critical illness medical subsidy'
--group by a.pk_psndoc) g on a.pk_psndoc=g.pk_psndoc
--Complementary medicine
--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='employee supplementary medical insurance'
--group by a.pk_psndoc) h on a.pk_psndoc=h.pk_psndoc
--Medical insurance includes maternity
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='basic medical insurance (including maternity)'
group by a.pk_psndoc) j on a.pk_psndoc=j.pk_psndoc
--Query conditions
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 w here a.cuserid=macro('LoginUser')) and a.pk_financeorg in (parameter('param5'))
order by a.financeorgcode, a.orgcode, a.deptcode1, a.deptcode, a.psncode
After designing the report form, publish it as a node, and open the node to query the corresponding parameters, as shown in the figure below:
The results obtained are actually all, not the query parameters.
According to the recorded log, it is true that the sql did not pass in the query parameters:
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 SHY LF11 , 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 YL HSYF10 , t_1.ylhsyf11 YLHSYF11 , t_1.ylhsyf13 YLHSYF13 , t_1.dwjjhjf5 DWJJHJF5 , t_1.grjjhjf10 GRJJHJF10 , t_1.dwbjhjf11 DWBJHJF11 , t_1.grbj hjf13 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.yl hsyf10 , 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 ) + is null ( 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 ) + is null ( 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.syf1 3 , 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 J OIN 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 J OIN ( 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 W HERE b.name = N'employee basic pension insurance' 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_1 0 ) 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'unemployment insurance' 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'Injury Insurance' 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'employee social medical insurance' 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'maternity insurance' 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'basic medical insurance (including maternity)' 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.cuser id = 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 , finance orgcode , deptcode , orgcode , psncode
solution:
As shown in the figure below, in the [Report Design-Global/Group/Organization] node, click Parameters
In the pop-up box, set the value method and parameter value. After confirming, save the report, as shown in the figure below:
Then close the node and reopen it, click Query to enter the query conditions, as shown in the figure below:
After clicking the OK button, the result is the result of the parameter value, as shown in the figure below:
Setting of report serial number:
Use a cell formula – ROWNO()
Right click on the cell and select Cell Formula.
Select the formula ROWNO() and click Save.
