This is the Report Dataset:
select distinct t.task_id,
r.cross_reference,
t.task_status,
dbo.GetCodeTranslation(NULL, 'INCOMPLETE_REASON', e.description) AS Incomplete_Reason,
t.user_def15 FollowUpTask,
t.plan_start_dttm,
t.plan_end_dttm,
r.cust_prob_descr as Description,
t.created_dttm,
(select STRING_AGG(text_rtf,+ ','+ CHAR(13)) FROM task_text tt where tt.task_id =t.task_id) WorkNotes,
ru.user_def7 as coverage_code,
pl.name,
(select concat(address, ',',CITY,' ',ZIPPOST,',',country) from address where address_id=t.address_id) ConsumerAddress,
pl.phone,
pl.alternate_phone,
pl.email_address,
ru.model_id PNC,
pr.user_def1 as MLCode,
m.EXTERNAL_SYSTEM_ID as modelId,
pr.user_def5 as SerialNumber,
CONVERT(varchar, pr.MFG_INSTALLED_DT, 103) PurchaseDate,
(select description from global_code_table where code_name='BRAND' and code_value = m.default_brand) Brand,
(select description from global_code_table where code_name='ELUX_RETAILER' and code_value = pr.user_def4) Retailer,
m.product_family ProductSubgroup,
ip.PartNeed1,
ip.RexPart1,
ip.Description1,
ip.Quantity1,
ip.PartNeed2,
ip.RexPart2,
ip.Description2,
ip.Quantity2,
ip.PartNeed3,
ip. RexPart3,
ip.Description3,
ip.Quantity3,
ip.PartNeed4,
ip.RexPart4,
ip.Description4,
ip.Quantity4,
ip.PartNeed5,
ip.RexPart5,
ip.Description5,
ip.Quantity5,
up.PartUsage1,
up.PuRexPart1,
up.PuDescription1,
up.PuQuantity1,
up.PartUsage2,
up.PuRexPart2,
up.PuDescription2,
up.PuQuantity2,
up.PartUsage3,
up.PuRexPart3,
up.PuDescription3,
up.PuQuantity3,
up.PartUsage4,
up.PuRexPart4,
up.PuDescription4,
up.PuQuantity4,
up.PartUsage5,
up.PuRexPart5,
up.PuDescription5,
up.PuQuantity5,
/*pu.part_id as puPartID,
(select EXTERNAL_REF from part where part_id=pu.part_id) PUREX_PARTid,
(select INTERNAL_DESCRIPTN from part where part_id=pu.part_id) PUPartDescription,
pu.quantity puQuantity,*/
t.user_def18 DefectCode,
t.user_def19 ComponentCode,
t.user_def5 FaultReported,
t.user_def8 ActionTaken,
t.actual_start_dttm,
t.actual_end_dttm,
npu.user_def_num5 ActualDuration
from task t
inner join request r on r.request_id=t.request_id
inner join request_unit ru on r.request_id=ru.request_id and ru.request_unit_id = (select max(request_unit_id) from request_unit where request_id = ru.request_id)
inner join place pl on t.place_id_cust=pl.place_id
inner join product pr on ru.product_id=pr.product_id
inner join model m on ru.model_id=m.model_id
inner join task_event te on te.task_id=t.task_id
--left outer join part_usage pu on t.task_id=pu.task_id
left outer join non_part_usage npu on t.task_id=npu.task_id
left outer join isp_part_need_view ip on ip.task_id=t.task_id
left outer join isp_part_usage_view up ON up.task_id = t.task_id
LEFT JOIN elux_code_table e ON e.code_value = t.task_status_comment AND e.code_name = 'INCOMPLETE_REASON'
where /*t.task_status='DISPATCHED'and
(te.event_type='DISPATCHED' ) */
(t.task_status ##TaskStatus $$TaskStatus OR @TaskStatus IS NULL OR t.task_status = '')
and --convert(date,t.plan_start_dttm) between convert(date,@DateFrom) and convert(date,@DateTo)
CONVERT(DATE, t.plan_start_dttm) BETWEEN TRY_CAST(LTRIM(RTRIM(@DateFrom)) AS DATE) AND TRY_CAST(LTRIM(RTRIM(@DateTo)) AS DATE)
and pl.user_def2=@Country and t.person_id=@ISP
Below XML I am trying to run is working fine but i want multiple task_status to be given in xml like TaskStatus=’completed’,’Incomplete’
<perform_batch>
<sequential_dependent />
<!-- 1. Get Dates -->
<hierarchy_select return_only_requested_attrs="true" result_name="result01">
<attrs>
<attr>C_GET_DATES_VIEW.DATEFROM</attr>
<attr>C_GET_DATES_VIEW.DATETO</attr>
</attrs>
<from>
<table>C_GET_DATES_VIEW</table>
</from>
</hierarchy_select>
<!-- 2. Build report parameter string -->
<perform_evaluate_expression result_name="param_string">
<parameters>
<table_name>C_GET_DATES_VIEW</table_name>
<keys>
<key_item>
<column_name>DATEFROM</column_name>
<column_value xpath_node="//c_get_dates_view_hierarchy_select_resultT@result_name='result01']/c_get_dates_view/datefrom" />
</key_item>
</keys>
<expression><!_CDATA_
"Country=NL&ISP=ISPNLUSER&DateFrom=" + C_GET_DATES_VIEW.DATEFROM + "&DateTo=" + C_GET_DATES_VIEW.DATETO + "&TaskStatus=COMPLETED"
]]></expression>
</parameters>
</perform_evaluate_expression>
<!-- 3. Generate report with dynamic params -->
<perform_get_report_attachment>
<parameters>
<report_name>ISPSERVICEJOBDISPATCHREPORT</report_name>
<report_parameters xpath_node="//perform_evaluate_expression_result;@result_name='param_string']/response" />
</parameters>
</perform_get_report_attachment>
<!-- 4. Send the report via email -->
<perform_send_notification_mail>
<parameters>
<distribution_list_id>ELUX_TEST_I</distribution_list_id>
<message_name>ELUX_NL_ISP_SERVICE_JOB_REPORT</message_name>
<check_time_limit>N</check_time_limit>
<attachment_id xpath_node="//perform_get_report_attachment_result/attachment_id" />
</parameters>
</perform_send_notification_mail>
</perform_batch>