I'm not sure why but my IIF Statement doesn't seem to want to work.
=IIF(IsNothing(Fields!Calculated_Address.Value),Fields!Calculated_Dummy.Value, Fields!Calculated_Address.Value)
CodePudding user response：
My query is created through a program but here it is in simple terms.
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[Customer].[CustID] as [Customer_CustID],
[OrderRel].[OrderNum] as [OrderRel_OrderNum],
[OrderRel].[PartNum] as [OrderRel_PartNum],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
[OrderRel].[LoadNum_c] as [OrderRel_LoadNum_c],
[OrderRel].[DropSeq_c] as [OrderRel_DropSeq_c],
[OrderHed].[PONum] as [OrderHed_PONum],
[JobProd].[JobNum] as [JobProd_JobNum],
[SerialNo].[SerialNumber] as [SerialNo_SerialNumber],
[OrderDtl].[ShipComment] as [OrderDtl_ShipComment],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[OrderRel].[SellingReqQty] as [OrderRel_SellingReqQty],
[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
[Customer].[Name] as [Customer_Name],
[Customer].[Address1] as [Customer_Address1],
[Customer].[Address2] as [Customer_Address2],
[Customer].[Address3] as [Customer_Address3],
[Customer].[City] as [Customer_City],
[Customer].[State] as [Customer_State],
[Customer].[Country] as [Customer_Country],
[Customer].[PhoneNum] as [Customer_PhoneNum],
[UD100].[LoadName_c] as [UD100_LoadName_c],
[UD100].[LoadingMeters_c] as [UD100_LoadingMeters_c],
[UD100].[TrailerType_c] as [UD100_TrailerType_c],
[UD100].[Carrier_c] as [UD100_Carrier_c],
[UD100].[Key1] as [UD100_Key1],
(CONCAT(ShipTo.Name,' ',
ShipTo.Address1,' ',
ShipTo.City,' ',
ShipTo.State,' ',
ShipTo.Zip,' ',
ShipTo.Country) Dummy) as [Calculated_Address],
(COALESCE(CONVERT(nvarchar, UD100.DateCustContact_c, 105), CONVERT(nvarchar, UD100.CollectionDate_c, 105), CONVERT(nvarchar, UD100.AvailableDate_c, 105), (CASE
--Jan
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),1,1) then 'Early January'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),1,14) then 'Mid January'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),1,27) then 'Late January'
--Feb
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),2,1) then 'Early Feburary'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),2,14) then 'Mid Feburary'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),2,27) then 'Late Feburary'
--Mar
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),3,1) then 'Early March'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),3,14) then 'Mid March'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),3,27) then 'Late March'
--Apr
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),4,1) then 'Early April'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),4,14) then 'Mid April'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),4,27) then 'Late April'
--May
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),5,1) then 'Early May'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),5,14) then 'Mid May'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),5,27) then 'Late May'
--Jun
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),6,1) then 'Early June'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),6,14) then 'Mid June'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),6,27) then 'Late June'
--Jul
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),7,1) then 'Early July'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),7,14) then 'Mid July'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),7,27) then 'Late July'
--Aug
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),8,1) then 'Early August'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),8,14) then 'Mid August'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),8,27) then 'Late August'
--Sept
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),9,1) then 'Early September'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),9,14) then 'Mid September'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),9,27) then 'Late September'
--Oct
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),10,1) then 'Early October'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),10,14) then 'Mid October'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),10,27) then 'Late October'
--Nov
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),11,1) then 'Early November'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),11,14) then 'Mid November'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),11,27) then 'Late November'
--Dec
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),12,1) then 'Early December'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),12,14) then 'Mid December'
WHEN UD100.Date01 = DATEFROMPARTS(YEAR(GETDATE()),12,27) then 'Late December'
else
'No date'
end))) as [Calculated_PlannedDate],
[UD100].[LoadDesc_c] as [UD100_LoadDesc_c],
[ShipTo].[Name] as [ShipTo_Name],
[ShipTo].[Address1] as [ShipTo_Address1],
[ShipTo].[Address2] as [ShipTo_Address2],
[ShipTo].[Address3] as [ShipTo_Address3],
[ShipTo].[City] as [ShipTo_City],
[ShipTo].[State] as [ShipTo_State],
[ShipTo].[ZIP] as [ShipTo_ZIP],
[ShipTo].[Country] as [ShipTo_Country],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
('') as [Calculated_Dummy],
('') as [Calculated_Dummy_2]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
and ( not OrderDtl.PartNum like 'CONTRACT%' )
inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
and OrderDtl.OrderNum = OrderRel.OrderNum
and OrderDtl.OrderLine = OrderRel.OrderLine
and ( OrderRel.OpenRelease = 1 or OrderRel.OpenRelease = 0 )
left outer join Erp.JobProd as JobProd on
OrderRel.Company = JobProd.Company
and OrderRel.OrderNum = JobProd.OrderNum
and OrderRel.OrderLine = JobProd.OrderLine
and OrderRel.OrderRelNum = JobProd.OrderRelNum
left outer join Erp.JobHead as JobHead on
JobProd.Company = JobHead.Company
and JobProd.JobNum = JobHead.JobNum
left outer join Erp.SerialNo as SerialNo on
JobHead.Company = SerialNo.Company
and JobHead.JobNum = SerialNo.JobNum
inner join Ice.UD100 as UD100 on
OrderRel.Company = UD100.Company
and OrderRel.LoadNum_c = UD100.Key1
and ( UD100.Key1 = @LoadNum )
inner join Erp.ShipTo as ShipTo on
OrderRel.Company = ShipTo.Company
and OrderRel.ShipToCustNum = ShipTo.CustNum
and OrderRel.ShipToNum = ShipTo.ShipToNum
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
union
select
[Customer_Msc].[CustID] as [Customer_Msc_CustID],
[MscShpDt].[PackNum] as [MscShpDt_PackNum],
[MscShpDt].[PartNum] as [MscShpDt_PartNum],
[MscShpDt].[LineDesc] as [MscShpDt_LineDesc],
[MscShpHd].[LoadNum_c] as [MscShpHd_LoadNum_c],
[MscShpHd].[DropSeq_c] as [MscShpHd_DropSeq_c],
(convert(nvarchar(15), MscShpHd.PONum)) as [Calculated_PONum],
[JobHead_Msc].[JobNum] as [JobHead_Msc_JobNum],
[SerialNo_Msc].[SerialNumber] as [SerialNo_Msc_SerialNumber],
[MscShpHd].[ShipComment] as [MscShpHd_ShipComment],
[MscShpDt].[PackLine] as [MscShpDt_PackLine],
(0) as [Calculated_MiscLine],
[MscShpDt].[Quantity] as [MscShpDt_Quantity],
[MscShpDt].[ShipToNum] as [MscShpDt_ShipToNum],
[MscShpHd].[Name] as [MscShpHd_Name],
[MscShpHd].[Address1] as [MscShpHd_Address1],
[MscShpHd].[Address2] as [MscShpHd_Address2],
[MscShpHd].[Address3] as [MscShpHd_Address3],
[MscShpHd].[City] as [MscShpHd_City],
[MscShpHd].[State] as [MscShpHd_State],
[MscShpHd].[Country] as [MscShpHd_Country],
[Customer_Msc].[PhoneNum] as [Customer_Msc_PhoneNum],
[UD100_Msc].[LoadName_c] as [UD100_Msc_LoadName_c],
[UD100_Msc].[LoadingMeters_c] as [UD100_Msc_LoadingMeters_c],
[UD100_Msc].[TrailerType_c] as [UD100_Msc_TrailerType_c],
[UD100_Msc].[Carrier_c] as [UD100_Msc_Carrier_c],
[UD100_Msc].[Key1] as [UD100_Msc_Key1],
('') as [Calculated_Dummyfield],
('') as [Calculated_DummyField2],
[UD100_Msc].[LoadDesc_c] as [UD100_Msc_LoadDesc_c],
('') as [Calculated_Dummy3],
('') as [Calculated_Dummy4],
('') as [Calculated_Dummy5],
('') as [Calculated_Dummy6],
('') as [Calculated_Dummy7],
('') as [Calculated_Dummy8],
('') as [Calculated_Dummy9],
('') as [Calculated_Dummy10],
(0) as [Calculated_Dummy11],
(0) as [Calculated_Dummy12],
(0) as [Calculated_Dummy13],
(CONCAT(MscShpHd.Name,' ',
MscShpHd.Address1,' ',
MscShpHd.City,' ',
MscShpHd.State,' ',
MscShpHd.Zip,' ',
MscShpHd.Country)) as [Calculated_MiscAddress],
((CASE
WHEN MscShpDt.PackNum LIKE '0%'THEN 'MISC'
WHEN MscShpDt.PackNum LIKE '1%'THEN 'MISC'
WHEN MscShpDt.PackNum LIKE '2%'THEN 'MISC'
ELSE 'N/A'
END)) as [Calculated_Misc_Order]
from Erp.MscShpHd as MscShpHd
inner join Erp.MscShpDt as MscShpDt on
MscShpHd.Company = MscShpDt.Company
and MscShpHd.PackNum = MscShpDt.PackNum
left outer join Erp.JobHead as JobHead_Msc on
MscShpDt.Company = JobHead_Msc.Company
and MscShpDt.JobNum = JobHead_Msc.JobNum
left outer join Erp.SerialNo as SerialNo_Msc on
JobHead_Msc.Company = SerialNo_Msc.Company
and JobHead_Msc.JobNum = SerialNo_Msc.JobNum
inner join Erp.Customer as Customer_Msc on
Customer_Msc.Company = MscShpHd.Company
and Customer_Msc.CustNum = MscShpHd.CustNum
inner join Ice.UD100 as UD100_Msc on
UD100_Msc.Key1 = MscShpHd.LoadNum_c
and UD100_Msc.Company = MscShpHd.Company
and ( UD100_Msc.Key1 = @LoadNum )
where (MscShpHd.ShipDate >= dateadd (year, -2, Constants.Today))
order by OrderRel.OrderNum Desc
CodePudding user response：
I would like to apologise to everyone.
I missed Calculated_Dummy out of my Query in the RDL file.
=IIF(IsNothing(Fields!Calculated_Address.Value) OR Trim(Fields!Calculated_Address.Value)="",Fields!Calculated_Dummy.Value, Fields!Calculated_Address.Value)
Worked perfectly. Thank you :)