Visible Row Values on IIF Expression from Hidden Row?

Question

Purchase Price Variance

In my specific scenario (screenshot) Report is showing Shipment(step1) and then Invoice(step2). And I want is all this information in one row. I need to hide the row when POP_TYPE=Shipment but how can I get QTY SHIPPED and PURCHASE PRICE Values in POP_TYPE=INVOICE row? The tricky thing is, Purchase Price Value and Invoice Price Value is a same field, which is changing value on POP_TYPE i.e. Shipment or Invoice. Any idea or statement to solve this scenario?

Following is the actual system generated code for the report:

select [ASIEXP28].[receiptdate] AS 'Receipt Date',
[ASIEXP28].[POPRCTNM] AS 'POP Receipt Number',
[ASIEXP28].[BACHNUMB] AS 'Batch Number',
[ASIEXP28].[PONUMBER] AS 'PO Number',
[ASIEXP28].[VNDDOCNM] AS 'Vendor Document Number',
[ASIEXP28].[VENDORID] AS 'Vendor ID',
RA.dbo.DYN_FUNC_POP_Type([ASIEXP28].POPTYPE) AS 'POP Type',
[ASIEXP28].[ITEMNMBR] AS 'Item Number',
[ASIEXP28].[ITEMDESC] AS 'Item Description',
[ASIEXP28].[UOFM] AS 'U Of M',
[ASIV0001].[QTYSHPPD] AS 'QTY Shipped',
[ASIV0001].[QTYINVCD] AS 'QTY Invoiced',
[ASIV0001].[QTYMATCH] AS 'QTY Matched',
[ASIEXP28].[LOCNCODE] AS 'Location Code',
(CASE WHEN [ASIEXP28].[UNITCOST]<0 THEN '-'+(SELECT CASE WHEN (SELECT CYSYMPLC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 0  THEN RTRIM(CRNCYSYM)+(SELECT CASE WHEN (SELECT INCLSPAC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 1  THEN ' ' ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR'))+LTRIM(STR(abs([ASIEXP28].[UNITCOST]),100,(SELECT DECPLCUR-1 FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')))) ELSE(SELECT CASE WHEN (SELECT CYSYMPLC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 0  THEN RTRIM(CRNCYSYM)+(SELECT CASE WHEN (SELECT INCLSPAC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 1  THEN ' ' ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR'))+LTRIM(STR([ASIEXP28].[UNITCOST],100,(SELECT DECPLCUR-1 FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR'))))+(SELECT CASE WHEN (SELECT CYSYMPLC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) <> 0  THEN (SELECT CASE WHEN (SELECT INCLSPAC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 1  THEN ' ' ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR'))+RTRIM(CRNCYSYM) ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) END) AS 'Unit Cost',
(CASE WHEN [ASIEXP28].[EXTDCOST]<0 THEN '-'+(SELECT CASE WHEN (SELECT CYSYMPLC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 0  THEN RTRIM(CRNCYSYM)+(SELECT CASE WHEN (SELECT INCLSPAC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 1  THEN ' ' ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR'))+LTRIM(STR(abs([ASIEXP28].[EXTDCOST]),100,(SELECT DECPLCUR-1 FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')))) ELSE(SELECT CASE WHEN (SELECT CYSYMPLC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 0  THEN RTRIM(CRNCYSYM)+(SELECT CASE WHEN (SELECT INCLSPAC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 1  THEN ' ' ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR'))+LTRIM(STR([ASIEXP28].[EXTDCOST],100,(SELECT DECPLCUR-1 FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR'))))+(SELECT CASE WHEN (SELECT CYSYMPLC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) <> 0  THEN (SELECT CASE WHEN (SELECT INCLSPAC FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) = 1  THEN ' ' ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR'))+RTRIM(CRNCYSYM) ELSE '' END FROM DYNAMICS..MC40200 WHERE CURNCYID = ISNULL(NULLIF([ASIEXP28].[CURNCYID],''),'PKR')) END) AS 'Extended Cost',
[ASIEXP28].[USER2ENT] AS 'User To Enter',
[ASIEXP28].PSTGSTUS AS 'Posting Status',
[ASIEXP28].[PTDUSRID] AS 'Posted User ID' from RA..[ASIEXP28]
left join RA..[ASIV0001] on [ASIEXP28].[POPRCTNM] = [ASIV0001].[POPRCTNM] and 
[ASIEXP28].[RCPTLNNM]=[ASIV0001].[RCPTLNNM]
WHERE ([ASIEXP28].[receiptdate] BETWEEN @DFrom AND @DTo) ORDER BY [ASIEXP28].[PONUMBER]

Answer 1

Considering your question, You need to achieve 2 things, Hide row for POP_TYPE=INVOICE and show quantity shipped and purchase price from same PO number but with POP_TYPE=INVOICE.

Note: There can be typos or syntax error or field name change as I gave your ideas and locally I used different field name for my testing

To hide a row is quite straight forward in SSRS.

Unless I'm missing some nuance, you will simply set the Row Visibility based on the value.

Right click on the detail line and select Row Visiblity...

In the window that pops up, select Show or hide based on an expression

Within that expression, you'll use logic like

=Fields!POP_TYPE=INVOICE

Now comes the tricky part, you want value of same po number but with pop_type=invoice.

Here you should use Lookup function

Go to your cell for qty shipped and right click and choose expression

Lookup(source_expression, destination_expression, result_expression, dataset)

so in your case it shall be

=Lookup(Fields!PONumber.Value & "Shipment", Fields!PONumber.Value & Fields!POPType.Value, Fields!qtyshipped.Value, "DataSet1") 

same for column/cell purchase price

=Lookup(Fields!PONumber.Value & "ship",Fields!PONumber.Value & Fields!POPType.Value,Fields!PurchasePrice.Value,"DataSet1")

I tried locally and below is the result I got