locked
Problem converting C# to RDLC Expression RRS feed

  • 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.

    Thursday, May 25, 2017 9:31 AM

All replies

  • 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

    Monday, May 29, 2017 9:59 AM