Problem converting C# to RDLC Expression

Question

User1182587605 posted

I have C# code which I need to put into an RDLC expression to calculate percentage. Please help me know how to do that. I have the query which fetches qty, price etc.,  but I do not have an idea on how to make this percentage possible.

select distinct Store_Number,Location,MIN(WkEnd_Date) WkEnd_Date,ItemNumber,MIN(Qty) [Qty_Cw],MAX(Qty) [Qty_YTD],
MAX(WkEnd_Date) WkEnd_Date_Max,
SellingPrice,MIN(total_price) total_price,MIN(total_retail_price) total_retail_price,RetailPrice from
(
 select PJ_RetStore_Sales_T.Store_Number, PJ_Location_M.Location, PJ_RetStore_Sales_T.WkEnd_Date, 
 PJ_RetStore_Sales_T.ItemNumber,SUM(PJ_RetStore_Sales_T.Qty) as Qty, PJ_Item_M.SellingPrice, 
 sum(PJ_RetStore_Sales_T.Qty)*PJ_Item_M.SellingPrice as total_price,
 sum(PJ_RetStore_Sales_T.Qty)*PJ_Item_M.RetailPrice as total_retail_price, 
 PJ_Item_M.RetailPrice 
 from PJ_RetStore_Sales_T, PJ_Location_M , PJ_Item_M
 where PJ_RetStore_Sales_T.Store_Number = PJ_Location_M.Store_Number 
 and PJ_RetStore_Sales_T.ItemNumber = PJ_Item_M.ItemNumber 
 and PJ_Item_M.variation = 'Std' and WkEnd_Date >= '2014-06-06' and WkEnd_Date <= '2015-06-06' 
 and PJ_RetStore_Sales_T.ItemNumber = 'C123'
 group by PJ_RetStore_Sales_T.Store_Number, PJ_Location_M.Location, 
 PJ_RetStore_Sales_T.WkEnd_Date, PJ_RetStore_Sales_T.ItemNumber,
 PJ_Item_M.SellingPrice, PJ_Item_M.RetailPrice 
 --order by PJ_RetStore_Sales_T.Store_Number, PJ_RetStore_Sales_T.WkEnd_Date
 ) t
 group by Store_Number,Location,ItemNumber,SellingPrice,RetailPrice 

//Curr Year Curr Week Qty
			If (CDate(WkEnd_Date) >= CY_CW_FrmDt) and (CDate(WkEnd_Date) <= CY_ToDate) then
				CY_CW_RtlVal = CY_CW_RtlVal + round((CSng(Qty) * CSng(Retail_Price)),2)
				T_CY_CW_RtlVal = T_CY_CW_RtlVal + round((CSng(Qty) * CSng(Retail_Price)),2)
			End If
		
			//Last Year Curr Week Qty
			If (CDate(WkEnd_Date) >= LY_CW_FrmDt) and (CDate(WkEnd_Date) <= LY_ToDate) then
				LY_CW_RtlVal = LY_CW_RtlVal + round((CSng(ResRep(Qty)) * CSng(Retail_Price)),2)
				T_LY_CW_RtlVal = T_LY_CW_RtlVal + round((CSng(ResRep(Qty)) * CSng(Retail_Price)),2)
			End If
		
			//Curr Year YTD Qty
			If (CDate(WkEnd_Date) >= CY_FromDate) and (CDate(WkEnd_Date) <= CY_ToDate) then
				CY_YTD_RtlVal = CY_YTD_RtlVal + round((CSng(ResRep(Qty)) * CSng(Retail_Price)),2)
				T_CY_YTD_RtlVal = T_CY_YTD_RtlVal + round((CSng(ResRep(Qty)) * CSng(Retail_Price)),2)
			End If
		
			//Last Year YTD Qty
			If (CDate(WkEnd_Date) >= LY_FromDate) and (CDate(WkEnd_Date) <= LY_ToDate) then
				LY_YTD_RtlVal = LY_YTD_RtlVal + round((CSng(ResRep(Qty)) * CSng(Retail_Price)),2)
				T_LY_YTD_RtlVal = T_LY_YTD_RtlVal + round((CSng(ResRep(Qty)) * CSng(Retail_Price)),2)
			End If
			

bT_Week_Perc = ((T_CY_CW_RtlVal / T_LY_CW_RtlVal) - 1) * 100
    bT_YTD_Perc = ((T_CY_YTD_RtlVal / T_LY_YTD_RtlVal) - 1) * 100

 I need to show the bT_Week_Perc and bT_YTD_Perc as separate columns in my RDLC. I need this as Soon as possible please help.

Answer 1

User1967761114 posted

Hi acmedeepak,

  According to your description and SQL statement which you provide ,that’ very difficult to understand what’s your mean.

  If you can, you could provide more details ,such as table scheme ,more detail descriptions….

 I tried to write the SQL statement which you want (I'm not sure what you really want), it might help you.

select distinct Store_Number,Location,MIN(WkEnd_Date) WkEnd_Date,ItemNumber,MIN(Qty) [Qty_Cw],MAX(Qty) [Qty_YTD],
MAX(WkEnd_Date) WkEnd_Date_Max,
SellingPrice,MIN(total_price) total_price,MIN(total_retail_price) total_retail_price,RetailPrice,
((SUM(T_CY_CW_RtlVal)/(CASE SUM(T_LY_CW_RtlVal) WHEN 0 THEN 1 ELSE SUM(T_LY_CW_RtlVal) END))-1)*100 AS bT_Week_Perc ,
((SUM(T_CY_YTD_RtlVal )/(CASE SUM(T_LY_YTD_RtlVal) WHEN 0 THEN 1 ELSE SUM(T_LY_YTD_RtlVal) END))-1)*100 AS bT_YTD_Perc
from
(
select PJ_RetStore_Sales_T.Store_Number, PJ_Location_M.Location, PJ_RetStore_Sales_T.WkEnd_Date,
PJ_RetStore_Sales_T.ItemNumber,SUM(PJ_RetStore_Sales_T.Qty) as Qty, PJ_Item_M.SellingPrice,
sum(PJ_RetStore_Sales_T.Qty)*PJ_Item_M.SellingPrice as total_price,
sum(PJ_RetStore_Sales_T.Qty)*PJ_Item_M.RetailPrice as total_retail_price,
PJ_Item_M.RetailPrice,
(CASE WHEN (CDate(WkEnd_Date) >= CY_CW_FrmDt) and (CDate(WkEnd_Date) <= CY_ToDate)
THEN round((CSng(Qty) * CSng(Retail_Price)),2)
ELSE 0
END) AS T_CY_CW_RtlVal ,
(CASE WHEN (CDate(WkEnd_Date) >= LY_CW_FrmDt) and (CDate(WkEnd_Date) <= LY_ToDate)
THEN round((CSng(ResRep(Qty)) * CSng(Retail_Price)),2)
ELSE 0
END) AS T_LY_CW_RtlVal,
(CASE WHEN (CDate(WkEnd_Date) >= CY_FromDate) and (CDate(WkEnd_Date) <= CY_ToDate)
THEN round((CSng(ResRep(Qty)) * CSng(Retail_Price)),2)
ELSE 0
END) AS T_CY_YTD_RtlVal,
(CASE WHEN (CDate(WkEnd_Date) >= LY_FromDate) and (CDate(WkEnd_Date) <= LY_ToDate)
THEN round((CSng(ResRep(Qty)) * CSng(Retail_Price)),2)
ELSE 0
END) AS T_LY_YTD_RtlVal
from PJ_RetStore_Sales_T, PJ_Location_M , PJ_Item_M
where PJ_RetStore_Sales_T.Store_Number = PJ_Location_M.Store_Number
and PJ_RetStore_Sales_T.ItemNumber = PJ_Item_M.ItemNumber
and PJ_Item_M.variation = 'Std' and WkEnd_Date >= '2014-06-06' and WkEnd_Date <= '2015-06-06'
and PJ_RetStore_Sales_T.ItemNumber = 'C123'
group by PJ_RetStore_Sales_T.Store_Number, PJ_Location_M.Location,
PJ_RetStore_Sales_T.WkEnd_Date, PJ_RetStore_Sales_T.ItemNumber,
PJ_Item_M.SellingPrice, PJ_Item_M.RetailPrice
--order by PJ_RetStore_Sales_T.Store_Number, PJ_RetStore_Sales_T.WkEnd_Date
) t
group by Store_Number,Location,ItemNumber,SellingPrice,RetailPrice