CASE/Switch Crystal Reports function

[Former Member]

Let's assume column XYZ has both numbers (1), and letters (any alphabet).

I have a case statement on SQL to turn any value that's not 1 into 0, then I am getting a sum of that column.

I am also grouping by Row A, B etc to get aggregated sum of column XYZ for those group.

Now on Crystal Reports function, I need to sum up values under column XYZ for all the groups.

If I try using sum function like below, I get an error stating:

"A number field or currency amount field is required here"

(sum({Command.XYZ}))

So I thought if I can use a case statement to change the non-numbers to 0 prior to sum that will probably resolve it. But I could not get the below case statement to work either (Error: A string is required). 

SELECT {Command.XYZ}

   Case = 1:

      1 

   Default:

      0;

If I try using SWITCH, I receive an error for underlined below "A string is required here"

Switch(

  {Command.XYZ} = 1, 1,

  true, 0

)

Originally discussed on http://scn.sap.com/thread/1050589

[abhilash_kumar · Accepted solution]

Hi Ashish,

You said "I have a case statement on SQL to turn any value that's not 1 into 0, then I am getting a sum of that column." and  "Now on Crystal Reports function, I need to sum up values under column XYZ for all the groups"

So, are you trying to create a similar function in CR or are you trying to just sum up the values that you bring in from the database?

-Abhilash

[abhilash_kumar · Accepted solution]

If you're simply trying to 'sum' up the XYZ column that already shows 1 or zero, create a formula in the report with this code:

tonumber({XYZ})

Then, go to Insert > Summart > Choose this formula field > Place this summary on all levels.

-Abhilash

[Former Member · Accepted solution]

Converting using tonumber, then inserting that function as summary worked. Thank you for all of your help!

[DellSC]

This is telling me that the data in the {Command.XYZ} field is a string.  So, you have to compare it to a string.

In Switch, it will look like this:

Switch(

  {Command.XYZ} = "1", 1,

  true, 0

)

-Dell

[ido_millet]

Convert the column to a number (1 or 0) rather than a string.  This would allow you to do the SUM.