i have main data set called "incomerecievedgeneralneeds". used on main report. includes field called "scheme"
i have created new dataset called "scheme" , parameter called scheme, want scheme parameter show schemes in main dataset.at present showing me schemes not in main dataset.
the scheme code here -
select distinct loc.scheme 'scheme' ih_location loc the incomerecievedgeneralneeds code here -
select distinct trans.tncy_sys_ref 'tncysysref' ,ten.tenancy_ref 'tenancyref' ,trans.created_date 'transactioncreateddate' ,mhs.startdate 'startofweekdate' ,mhs.enddate 'endofweekdate' ,mhs.financialyear 'transactionfiscalyear' ,mhs.monthname 'transactionfiscalmonthname' ,mhs.month 'transactionfiscalmonth' ,mhs.week 'transactionfiscalweek' ,concat('week ', mhs.week, ' (', convert(varchar(11),mhs.startdate,103), '-', convert(varchar(11),mhs.enddate,103), ')' ) 'transactionfiscalweekwithdates' ,trans.comment_ 'transactioncomment' ,trans.trans_amt 'transactionamount' ,case when trans.account_type in ('in','li') 'income' when trans.account_type = 'hb' 'housingbenefit' else '' end 'accounttype' ,trans.account_type ,acc.description 'accountcode' ,loc.scheme 'scheme' ,loc.mgt_area 'management area' ,loc.location_type 'location type' ,loct.description 'location type description' ,ten.tncy_start 'tenancystartdate' ,ten.tncy_end 'tenancyenddate' ,case when ten.tncy_end null or trans.created_date < ten.tncy_end 'current' else 'former' end 'incometenancystatus' ,ten.tncy_status 'tenanacystatus' ,case when ten.tncy_status = 'for' loc.former_arrs_ofcr else loc.arrears_ofcr end 'officercode' [dbo].[re_tncy_trans] trans inner join re_tenancy ten on trans.tncy_sys_ref = ten.tncy_sys_ref inner join re_tncy_place tenpl on ten.tncy_sys_ref = tenpl.tncy_sys_ref inner join ih_location loc on tenpl.place_ref = loc.place_ref inner join [dbo].[re_accounts] acc on trans.account_code = acc.account_code , trans.account_type = acc.account_type inner join [mhsinsight].[dbo].[mhs_month_week] mhs on trans.created_date between mhs.startdate , mhs.enddate inner join [dbo].[ih_location_type] loct on loc.location_type = loct.location_type trans.account_type in ('in','hb','li') , mhs.startdate >= convert(datetime, '2014-04-07 00:00:00', 102) , loc.place_ref <> '9999999999'
in parameter query, use same , clause use in main query, , same schemes have in main dataset.
Comments
Post a Comment