| Author |
Message |
jmking
Newbie
Joined: 01 Apr 2009
Location: United States
Online Status: Offline
Posts: 4
|
  
Topic: Filtering and grouping with case statement
Posted: 01 Apr 2009 at 12:06pm |
I'm new to Crystal Reports and I am working with Crystal Reports 11 to create an inventory report for PCs that have not audited within the last 30 days. I have managed to create the report with the information I need, but I need some assitance grouping, filtering, and sorting it.
- I need to filter out the value 'stock' and suppress entries that list it. However due to incosistencies in typing the value may appear as "stock", "STOCK", "Stock" and possible other variances. Short of listing each variant is there a way to filter this out?
- Once the stock PCs are filtered out, I need to group the remainder by the first letters of their computer ID to indicate what location each PC belongs in. The location code can take as many as 4 characters, but some entries only use two characters to code their locations. I want separate groups for "ABCD", "ABC", "EFG", "AB", etc. I have tried to create a case statement, but I keep getting syntax errors and it doesn't sort.
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Apr 2009 at 12:21pm |
Part one gets a little tricky depending on how many variations and what the other possible items are in that field. A select statement would not be case senstive so that should handle the 3 instances you noted or you could use an instr or a LIKE "*stock*" or something similar to widen the scope of the selection process.
For part 2 is there a common item to seperate out the location from the rest of the computerID like a space or a - (ABCD 12312323 or ABCD-12323123)?
|
| IP Logged |
|
jmking
Newbie
Joined: 01 Apr 2009
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 01 Apr 2009 at 12:30pm |
Part 1: I think we may just have to stick to listing each variant as we find it. :)
Part 2: The PC numbers tend to follow the convention ABCD1234 but there are cases that don't fit the rule (these may just have to default to a 'misc' category) and they usually do not have a separator. (We tried to establish a naming convention, and then 'inherited' a different convention when we bought a different company.)
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Apr 2009 at 1:03pm |
if the next item past the location is a number you could do an if then process to extract the location.
if numerictext(mid( {table.idfield},2,1))= True then left( {table.idfield},1) else if numerictext(mid( {table.idfield},3,1))= True then left( {table.idfield},2) else if numerictext(mid( {table.idfield},4,1))= True then left( {table.idfield},3) else if numerictext(mid( {table.idfield},5,1))= True then left( {table.idfield},4) else "Other" |
| IP Logged |
|
jmking
Newbie
Joined: 01 Apr 2009
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 01 Apr 2009 at 1:11pm |
|
Ok. that makes sense. Now is there a way to correlate particular codes to a text value? So if it returns "AB" it displays "Location 1" and "ABC" displays "Location 2"
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Apr 2009 at 1:23pm |
If you want that then I would skip the first suggestion and just do an if then statement looking for your left values and returning the string you want. The last suggestion would just be redundant processing.
Since you have items that have similar first characters you will always have to start with the items that are the longest character set and work backwards from those.
Once a record meets the first condition it will accept it assign the value then stop then move to the next row and check it starting from the beginning of the if-then statement. Therefore you need to get all your ABCD as "location 1" before checking for ABC as "location 2". Otherwise you will never have a "location 1" because it would stop on ABC and assign location 2 then stop.
Here is an example that you will have to play with to meet your exact definitions:
Hope this helps.
You may also want to add a final else "ERROR" to just make sure no records are slipping through. It would assign this to any items that did not get caught by the rest of your if then else statements. Edited by DBlank - 01 Apr 2009 at 1:25pm
|
| IP Logged |
|
jmking
Newbie
Joined: 01 Apr 2009
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 01 Apr 2009 at 1:27pm |
That's what I needed. Thanks for your help!
|
| IP Logged |
|
JohnT
Groupie
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
|
Posted: 02 Apr 2009 at 11:42am |
Couldn't you use the uppercase function for your filter.
UpperCase(database field) = "STOCK"
That way you won't have to worry about how it is entered. As long as it is spelled correctly, the case isn't important.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Apr 2009 at 12:33pm |
The selection criteria should not be case sensitive so the different variations on the case would not be a problem, it is more an issue with the other possible variations not listed.
field <>"stock" would exclude the "Stock", "STOCK", "stock" variations but miss "S tock" or " stock" or "Stck". If you do not want to exclude every different instance I would look for a common variable that is also not shared by any other items in the table and filter on it.
something like:
({table.field} like "st?c*")= false
|
| IP Logged |
|
|
|
