Nope,
you can't escape from CASE structure if you want to get this from your result set. Otherwise if to manage when print has to be printed in your CR:

SELECT ....
       p.sCommodity_CD,
       t.decTrade_Qty * p.decProduct_Ozconv AS MyField
....

Then in CR you should have MyField placed on four different locations and add logic when if should be printed.


>I am converting some R&R reports to Crystal.Net. One of the reports has this logic on the detail line:
>
>
>

>IIF(PRODS->COMCODE = "G",OUNCES,0)
>IIF(PRODS->COMCODE = "S",OUNCES,0)
>IIF(PRODS->COMCODE = "P",OUNCES,0)
>IIF(PRODS->COMCODE = "L",OUNCES,0)
>

>
>
>I want to build this logic into my query, but somethig isn't quite right. I'm not entirely sure this is the way to do this.
>
>

>DECLARE @CPosition int		-- Needs to be explained by the customer
>SET @CPosition = 0
>
>SELECT	t.dtTrade_DT,
>		sTrade_Amark_Trader,
>		iTrade_ID,
>		iOrder_Hdr_ID,
>		sTrade_Type_CD,
>		t.sProduct_CD,
>		decTrade_Qty,
>		@CPosition AS CPosition,
>		decTrade_Unit_Price,
>		decTrade_Spot_Price,
>		CASE p.sCommodity_CD
>			WHEN 'G' THEN t.decTrade_Qty * p.decProduct_Ozconv
>			ELSE 0
>		END AS Goz
>		CASE p.sCommodity_CD
>			WHEN 'S' THEN t.decTrade_Qty * p.decProduct_Ozconv
>			ELSE 0
>		END AS Soz
>		CASE p.sCommodity_CD
>			WHEN 'P' THEN t.decTrade_Qty * p.decProduct_Ozconv
>			ELSE 0
>		END AS Poz
>		CASE p.sCommodity_CD
>			WHEN 'L' THEN t.decTrade_Qty * p.decProduct_Ozconv
>			ELSE 0
>		END AS Loz
>	FROM Trade t
>	JOIN Product p on p.sProduct_CD = t.sProduct_CD
>	WHERE t.sTrade_Type_Pay_Rec = 'PAY' OR
>		  t.sTrade_Type_Pay_Rec = 'REC'
>	ORDER BY dtTrade_DT
>

>
>Anyone know a better way to do this?