Still show record when it doesn't meet criteria?

[tfratzke]

Here is my issue. I am working on a Timberline Job Cost report. This reports shows contract amounts, billed amounts, invoice dates, and calcuates a job cost %. I also want the report to pull in the amount of commission that has been applied to each job. The commission job cost code is "1-100". I used Select Expert and said to show records = to "1-100". That works great, except for the jobs that do not have this cost code. Then it completely hides the group and all of it's record information, even though there is still the rest of the data i want to see. When i remove that condition i get all of the other cost codes on the report which i don't want, but then no groups are hidden.

How might i go about showing only the groups & records that have a cost code of "1-100" and show the ones that don't have it, but also hide the other cost codes assigned to each group?

Another option: Might there be a way to ONLY show groups that do not have a cost code of "1-100" and hide the rest of the groups and other costs codes? I was thinking if i could do this, i could use this as a sub-report to pull in the groups that didn't meet the criteria.

Any help would be appreciated and let me know if you need more info.

Tony

 

[Madawc]

Which Crystal? I use Crystal 8.5, which allows more than the older versions but lacks some of the stuff Crystal 9 & 10 has. I'll give you a solution that will work in 8.5.

If I've understood you correctly, you are mostly interested in the

job cost code is "1-100"

records. But since you want the others, you shouldn't be using your Selection Criteria / Select Expert, which is there to exclude records for which you have no use at all. Take out that command and instead use Suppress to avoid showing detail lines that you don't want.

Suppress is found by right-clicking on a section and choosing [Format Section]. Suppression can be controlled by a formula, that's the x+2 thing. Put your test there, maybe suppressing everything that isn't job cost code "1-100".

You can also use running totals to count selectively for a group, or for the whole report. Or summary totals, which are quicker and easier but not selective.


Madawc Williams (East Anglia)

 

[tfratzke]

Sorry, Crystal 8.5 I actually had tried that already what you said, and justt tried it again. I put this suppress formula in the detail section:
{MASTER_JCM_COST_CODE.Cost_Code} <> "1-100"

Now, all of my groups (jobs) show up, but it still won't display anything when job cost code "1-100" is null.

Here is a screenshot of the report with the problematic record:

http://southviewdesign.com/download/jc.jpg

Tony

 

[Madawc]

Do a formual field
if isnull({value}) then "Null case" else totext({value})

Nulls always stop a command, unless tested for

Madawc Williams (East Anglia)

 

[tfratzke]

I get the same results with that formula, except now, all of my applied commissions are a string and cannot be grand totaled .
Let me ask this...how could i ONLY show records where "1-100" is null and hide all other groups/records?

Tony

 

[Madawc]

Do it by suppression. If you want a count of both types, put a subreport in the header or footer.

Madawc Williams (East Anglia)

 

[Ngolem]

You have not shown us your table linkages

Is this a single table of info or are other tables linked as well to MASTER_JCM_COST_CODE.Cost_Code as well.

If it is a single table then in your record selection formula perhaps this will be ok...(note: is this field nullable or not Null. if it is Not Null then you may have blank spaces instead of a null value to deal with.

...other criteria...
(if isnull({MASTER_JCM_COST_CODE.Cost_Code }} then
True
else if length(trim({MASTER_JCM_COST_CODE.Cost_Code })) = 0 then
True
else if {MASTER_JCM_COST_CODE.Cost_Code } = "1-100" then
True
else
False; )


for a grouping formula try this

@GroupCostCode

if isnull({MASTER_JCM_COST_CODE.Cost_Code }} or
length(trim({MASTER_JCM_COST_CODE.Cost_Code })) = 0 then
"Missing Code"
else
{MASTER_JCM_COST_CODE.Cost_Code };

perhaps this will work.


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[tfratzke]

I am using three Tables:
MASTER_JCM_JOB is equal joined to MASTER_JCM_COST_CODE by the
JOB to JOB field.
MASTER_JCM_JOB is equal joined to MASTER_ARM_CUSTOMER_TOTALS by the AR_CUSTOMER to CUSTOMER field.

The two fields; MASTER_JCM_COST_CODE.Cost_Code (string) & MASTER_JCM_COST_CODE.JTD_Cost (number) are both in the MASTER_JCM_COST_CODE table.

Tony

 

[tfratzke]

If i didn't elaborate enough, the MASTER_JCM_COST_CODE.JTD_Cost is the dollar value to the job cost code in the MASTER_JCM_COST_CODE.Cost_Code field.
Tony