Forum Discussion

pankajj's avatar
pankajj
Icon for Helper III rankHelper III
6 years ago

Convert Crystal Report Formula to use in Power BI query

Dear community,
I have an existing report done with Crystal Report.  While converting it to Power BI, I am stuck at the following Formula:

 

local stringvar warehouse := "";

if {Command.ABD_ORDER_POINT} - {Command.ABD_QIS} + {Command.ABD_QID} > {Command.ABDNetQty} then
(IF warehouse = "" then warehouse := warehouse + "*ABD*" else warehouse := warehouse + ", *ABD*")
else if {Command.ABD_ORDER_POINT} > 0 then
(if warehouse = "" then warehouse := warehouse + "ABD" else warehouse := warehouse + ", ABD");

if {Command.ALS_ORDER_POINT} - {Command.ALS_QIS} + {Command.ALS_QID} > {Command.ALSNetQty} then
(IF warehouse = "" then warehouse := warehouse + "*ALS*" else warehouse := warehouse + ", *ALS*")
else if {Command.ALS_ORDER_POINT} > 0 then
(IF warehouse = "" then warehouse := warehouse + "ALS" else warehouse := warehouse + ", ALS");

if {Command.ALSVC_ORDER_POINT} - {Command.ALSVC_QIS} + {Command.ALSVC_QID} > {Command.ALSVCNetQty} then
(IF warehouse = "" then warehouse := warehouse + "*ALS-VC*" else warehouse := warehouse + ", *ALS-VC*")
else if {Command.ALSVC_ORDER_POINT} > 0 then
(IF warehouse = "" then warehouse := warehouse + "ALS-VC" else warehouse := warehouse + ", ALS-VC");

if isnull({Command.NEXT_ORDER_REQUIRED_BY}) = false then
(IF warehouse = "" then warehouse := warehouse + "*" + (IF {Command.DEMAND_WHSE} = "UNIVERSAL" THEN "C2" ELSE {Command.DEMAND_WHSE}) + "*" else warehouse := warehouse + ", *" + (IF {Command.DEMAND_WHSE} = "UNIVERSAL" THEN "C2" ELSE {Command.DEMAND_WHSE}) + "*");

warehouse

 

We have so many great talent in the community, looking forward for a quick help.

 

Thank you very much in advance.

Need Help

2 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi pankajj,

    I am not professional in crystal report, in your scenario, it looks like the conditional column, so if possible, could you please inform me more detailed information(such as your sample data and your expected output)? Then I will help you more correctly.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • pankajj's avatar
      pankajj
      Icon for Helper III rankHelper III
      6 years ago

      Hi Zoe,

       

      Thanks so much for your response. and sorry for the late reply. I did not receive any email alert on your response.

       

      The Crystal query defines the 'Warehouse' based on various conditions. I was able to convert the query to run in Power BI as below however Power BI is not able to recognize 'warehouse'

       

      if ([ABD_ORDER_POINT] - [ABD_QIS] + [ABD_QID]) > [ABDNetQty] then
      if warehouse = "" then warehouse & "*ABD*" else warehouse & " *ABD*"
      else if [ABD_ORDER_POINT] > 0 then
      if warehouse = "" then warehouse & "ABD" else warehouse & ", ABD" else

      if ([ALS_ORDER_POINT] - [ALS_QIS] + [ALS_QID]) > [ALSNetQty] then
      if warehouse = "" then warehouse & "*ALS*" else warehouse & ", *ALS*"
      else if [ALS_ORDER_POINT] > 0 then
      if warehouse = "" then warehouse & "ALS" else warehouse & ", ALS" else

      if ([ALSVC_ORDER_POINT] - [ALSVC_QIS] + [ALSVC_QID]) > [ALSVCNetQty] then
      if warehouse = "" then warehouse & "*ALS-VC*" else warehouse & ", *ALS-VC*"
      else if [ALSVC_ORDER_POINT] > 0 then
      if warehouse = "" then warehouse & "ALS-VC" else warehouse & ", ALS-VC" else

      if [NEXT_ORDER_REQUIRED_BY] <> null then
      if warehouse = "" then warehouse & "*" & (if [DEMAND_WHSE] = "UNIVERSAL" then "C2" else [DEMAND_WHSE]) & "*" else warehouse & ", *" & (if [DEMAND_WHSE] = "UNIVERSAL" then "C2" else [DEMAND_WHSE]) & "*"
      else
      warehouse

       

      Can you help.

       

      Thank you very much for your support.

       

      Best regards,

       

      Pankajj