Using WHERE clause in Formula/Variable

building reports desktopintelligence
1

Hello,

I am facing a problem where i need to calculate count of id depending upon the status of a column. e.g. I am using a formula in my variable like this:

=Count() Where (=“RED”)

Now i have to count the IDs for the status RED/BLUE/GREEN and YELLOW. So when I try to give something like this

=Count() Where (in (“RED”,“BLUE”,“GREEN”,“YELLOW”)) then it shows me syntax error DMB0007.

I am using BO 5i.

Thanks to please help me on this.

sanbatra :india: (BOB member since 2006-09-29)

2

sanbatra,

Your ‘error’ is very understandable…
The correct syntax is:

=Count(<ID>) Where (<Status>="RED") 
+Count(<ID>) Where (<Status>="BLUE") 
+Count(<ID>) Where (<Status>="GREEN") 
+Count(<ID>) Where (<Status>="YELLOW")

Nobody knows why BO doesn’t accept: ‘<’, ‘<=’, ‘>’, ‘=>’, ‘<>’, ‘inlist’, etc… only ‘=’ is accepted :wah:

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)

3

I tend to use If…Then…Else instead of Where…just works better for me.

Nick Daniels :uk: (BOB member since 2002-08-15)

4

Nick,

You mean something like

=Sum(If (<Status> InList ("RED","BLUE","GREEN","YELLOW")) Then 1 Else 0)

This works, but now the object must be somewhere (hidden) in the table else an error will occur :hb:

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)