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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What did you have to suppress?
mlmcc
mlmcc
ASKER
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.
( https://www.experts-exchange.com/questions/27391409/How-to-Group-strings-in-Crystal-Reports-by-wild-cards.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)
Else
{JobNo}
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:
R10259-1-3
R10259-1-1
R10259-1-12
etc.
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:
R10147-1-1
R10147-1-2
etc.
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!
( https://www.experts-exchange.com/questions/27391409/How-to-Group-strings-in-Crystal-Reports-by-wild-cards.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)
Else
{JobNo}
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:
R10259-1-3
R10259-1-1
R10259-1-12
etc.
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:
R10147-1-1
R10147-1-2
etc.
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!
ASKER