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

Posted on 2011-10-13
Last Modified: 2012-05-12
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!
Question by:SeyerIT
    LVL 19

    Accepted Solution

    Try this

    ({table.JobNo} like 'R*' or {table.JobComplete} = 0)
    LVL 2

    Author Comment

    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!
    LVL 100

    Expert Comment

    What did you have to suppress?

    LVL 2

    Author Comment

    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.  

    ( )
    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video discusses moving either the default database or any database to a new volume.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now