[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

How to filter in SQL query in Crystal Reports where something = true but does not start with 'r'

Basically I have a list of jobs. In my query statement I want to only show jobs that are open unless they start with an 'R', in which case I want to show all of the R job.

In case the first sentence didn't make since..

I want to see jobcomplete = 0  but do not filter out the R-jobs, I want to see the R-jobs whether or not they are jobcomplete = 0 or jobcomplete = 1

Thanks in advance for the help!
  • 2
1 Solution
Try this

({table.JobNo} like 'R*' or {table.JobComplete} = 0)
SeyerITAuthor Commented:
Well that did what I wanted with some unexpected results.. I ended up filtering out the unexpected results by suppressing them in the report.. Other than that though, it did the trick!
What did you have to suppress?

SeyerITAuthor Commented:
They way I have the data setup makes filtering kind of difficult turns out. I'll try and explain the situation as best I can.. i didn't want to get into it in depth on this question since it really is based on another group formula.  

( http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27391409.html )
for reference on the formula being used. And here is the formula grouping my job numbers:

Local StringVar strTemp := Left({JobNo},Len({JobNo}) - Instr(strReverse({JobNo}), '-'));

If strTemp[1] = 'R' Then
    Right(strTemp, Len(StrTemp)-1)

Basically I was originally trying to group together all of the core jobs in the shop with their corresponding sub-jobs. This is really where the problem comes in as the core jobs and the sub jobs both come from Jobhead.JobNum and the only way to distinguish them is suffixes or -#'s at the end of the jobs. e.g. A core job # of 10259-1 may have the following sub jobs:


The formula above, which was provided by in another topic will group all of the Core Jobs with there corresponding sub jobs so I can perform calculations on the sums, etc. on the group.

What I was trying to accomplish with this question here was to filter out all of the CORE jobs that are closed. (Jobhead.JobComplete = 1) What happened when I tried to do this is whenever I tell it to filter out all of the Closed jobs, I was also losing most of my Sub-Job because they get completed and closed before they are received to the Core Jobs. Since those were getting filtered out, I was losing a lot of data to the group. I was thinking that if I would set up the filter to remove closed jobs unless they started with an 'R' then I wouldn't lose those jobs and still get the rest of the closed CORE jobs filtered out. So what happens when I get the filter properly set up is I end up with erroneous jobs showing up at my group level because I may be getting sub jobs returned for CORE jobs that are closed and filtered out.

e.g. Job 10147-1 is closed but that grouping shows up in the report anyways. So when I drilled down to see why I was getting that group even though 10147-1 is closed I would see all the sub jobs showing up:

So I sorted the details by Jobnum descending to get the last job in the list to represent the core jobs for my good records (this made the R jobs show up first in the list, or last if they were the only records which would only happen if the Core job was closed) Since the R jobs show up at the end of the list, that would push the last records' information to the Group footer where I could filter the group to remove all of the closed jobs. This fixed the problem I had rather easily.

Clear as mud right? Anyhow, it was a simple fix without having to try and create a complex question on here that I doubt anyone else is running into.. Although I guess you really never know.

Let me know if you want any more clarification on any of this as it's rather difficult to explain on here :)

Thanks again for all the help!

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now