Author Topic: Help with Detail Filter at the Query level  (Read 132 times)

Offline rajua99

  • Full Member
  • ***
  • Join Date: Jun 2009
  • Posts: 33
  • Forum Citizenship: +0/-0
Help with Detail Filter at the Query level
« on: 14 Dec 2017 01:26:45 pm »
Hello,

As of right now the i am using ([Job Type] in ('14','16','17','18','21'))  in my Detail filter.
Now the requirement is if the customer runs the report for Company Number 1 then it should bring ([Job Type] in ('14','16','17','18','21')) else for all the companies it should bring ([Job Type] in ('14','15','16','17','18','21'))..How to create this type of filter in the Detail filters...

When i am trying to use case statement at Detail filters with the below logic it is giving me error message..Syntax error near "in".

[Job Type]= CASE when [Company Number]=1 THEN ([Job Type] in ('14','16','17','18','21')) ELSE ([Job Type] in ('14','16','17','18','21')) END



Offline BigChris

  • Statesman
  • ******
  • Join Date: Apr 2013
  • Posts: 1,086
  • Forum Citizenship: +83/-0
Re: Help with Detail Filter at the Query level
« Reply #1 on: 15 Dec 2017 02:23:54 am »
Ok - you basically want your filter to return either True or False, so you need to slightly rethink the way you format your filter statement. What you need is something like:

Code: [Select]
([Company Number] = 1 and [Job Type] in ('14', '16', '17', '18', '21')) or ([Company Number] <> 1 and [Job Type] in ('14','15','16','17','18','21'))
{don't know if you noticed the missing '15' in your original post}

 


       
Twittear