| Author |
Message |
looeej
Newbie
Joined: 25 Jun 2015
Location: United States
Online Status: Offline
Posts: 8
|
   Topic: Filtering report
Posted: 30 Jan 2016 at 4:25am |
|
I'm using a basic (I think) report I've structured. We're a manufacturing firm and I'm creating this report to filter jobs. The database has tons of info for job tracking. on the details section I filter for only jobs with active operations to start. Then I'm filtering for material required. This is where I'm failing to get the results I need. The report displays the material line if it has been filled in. I need the blanks most importantly. They just won't show and I've tried many things.
Here's the formula used from select expert:
{Job.Status} = "Active" and
isnull({Material_Req.Material}) or
{Material_Req.Material} startswith ["an", "ap", "ar", "at", "cn", "cp", "cr", "ct", "sn", "sp", "sr", "st1", "st2", "st3", "st4", "st5", "st6", "st7", "st8", "st9"]
The quoted prefixes allows me to filter out the info I don't need which is a large amount. I tried using quotes with a space between to detect the blanks as well. Just won't display blank material lines. I've utilized the suppress in various ways with no success. Challenging for sure!
Suggestions PLEASE?
|
|
LooeeJ
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Feb 2016 at 2:52am |
|
If I understand you correctly you need to parenthesis your OR
If these are not null but an empty string you would also want to a add that as another condition inside your OR portion
{Job.Status} = "Active" and
(
isnull({Material_Req.Material}) or
{Material_Req.Material}="" or
{Material_Req.Material} startswith ["an", "ap", "ar", "at", "cn", "cp", "cr", "ct", "sn", "sp", "sr", "st1", "st2", "st3", "st4", "st5", "st6", "st7", "st8", "st9"]
)
|
|
IP Logged |
|
looeej
Newbie
Joined: 25 Jun 2015
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 01 Feb 2016 at 3:58am |
|
I appreciate the effort. Still get the same result and so perplexing why a blank material line won't display on the report...I will keep trying and any new suggestions so appreciated!
|
|
LooeeJ
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Feb 2016 at 4:04am |
|
for clarity sake, you are seeing these records in the report with no filter at all?
|
|
IP Logged |
|
looeej
Newbie
Joined: 25 Jun 2015
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 01 Feb 2016 at 4:25am |
|
I'll generate new report with no filtering to be sure...I've tried many ideas to this point and anything is possible! Appreciate you making that note to me!
|
|
LooeeJ
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Feb 2016 at 7:21am |
|
while doing this I would make sure you are using at least one field from each table in your data set.
Table joins are not "enforced" automatically. You can set them to enforced or once you use a field from both end nodes of the joined tables those joins become enforced.
Sometimes what is really happening is that a select statement is the only place a table is being used (say maybe job.satus). The select statement is then not the real issue, but rather the table join.
You can see this in action when you drag and the first field from a table onto the report canvas and suddenly your record selection jumps to an enormous number or a lot of records suddenly disappear.
|
|
IP Logged |
|
looeej
Newbie
Joined: 25 Jun 2015
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 02 Feb 2016 at 3:45am |
|
Generated new report with only 2 tables and the material field still doesn't display blanks...so perplexing!
Edited by looeej - 02 Feb 2016 at 3:46am
|
|
LooeeJ
|
|
IP Logged |
|
|
|
