help needed for formula 3

[SeeBee]

I have job numbers, some involving different 'tasks'. I am wanting to pick out only those job numbers with just task '13' associated. Some could have other tasks than just 13 (i.e. 41,10,12,13)-but I don't want these job number's.
Can someone help me with a formula for this? If I used the Select Expert and picked 13, I would still get this job number returned in the results, as there is a 13 within. Thanks in advance.

SeeBee

 

[dgillz]

I am having a bad day, having trouble reading. One last try:

keep the following formula is the details section, you can suppress it if you want:
@count13s
If {task}=13 the 1 else 0. Summarize this formula by {Job Detail (History).JobNo}

Then in the GROUP selection formula:
DistinctCount ({Job Detail (History).Operation},{Job Detail (History).JobNo}) =1 and Sum({@Count13s},
{Job Detail (History).Operation}>0

let me know if this works


Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

OK, my cheat wasn't correct anyway...

What you need to do is evaluate the records per group for the existence of a 13 PRIOR to showing anything. Simply put, you want groups that only have 13's in them.

Create a job number and cost center grouping, and suppress the detail. In the costcenter grouping, place your fields and a running total which counts the number of costcenters <> 13 (as the evaluate use a formula).

Now use the suppress on this group based on that running total being greater than 1.

If you want the count of 13's by job number, create another running total which will conditionally count those that = 13 and place it in the job number footer

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ooops, that should have read:

&quot;Now use the suppress on this group based on that running total being greater than 0.&quot;

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[SeeBee]

Okay, I'm back to working on this again - and I think I have it narrowed down.

As you suggested I subtotaled all the CostCenters and counted all the results of the 'test' (CostCenter 13 = 1 all others = zero). Then the subtotal of the CostCenters divided by the subtotal of the 'test' total = 13.

Results of each job number (group)are either true or false.

What is my next step for only seeing those true?

Thanks again.

 

[synapsevampire]

&quot;Now use the suppress on this group based on that running total being greater than 1.&quot;

Right click the grayed area next to the group, select format section, and click the X-2 alongside the Suppress(No Drill Down), place the formula in there, whatever results in true will be suppressed, so code accordingly.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

SeeBee,

Can you email me the report, with saved data? Maybe that will work. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[SeeBee]

The file is too large to send - I am working with 89,000 records out of 1,200,000, and I'm sure you don't want it either.

Here is an example of what I have showing in the group footer (details are hidden):

JobNum CostCenter(summed) task test(13=1,else 0, summed)
107414 247 5 49.40
118962 108 8 13.00
119218 13 1 13.00
120263 39 3 13.00

Then I created a group selection formula that divided the CostCenter sum by the task test sum.
As you can see, not everything is cut and dry. What formula can I use and where do I place it (group selection, etc) just to show me those equal to 13?
Thanks.

 

[dgillz]

{Fieldname}=13 will evaluate to true. you should be done at this point. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[SeeBee]

This is what I have for a formula for @CritMet, which returns either true or false:

Sum ({Job Detail (History).Cost Center}, {Job Detail (History).JobNo})/Sum
({@task test}, {Job Detail (History).JobNo})=13

How do I specify that I only want to see the true?

Thanks.

 

[synapsevampire]

(Sum ({Job Detail (History).Cost Center}, {Job Detail (History).JobNo})/Sum
({@task test}, {Job Detail (History).JobNo})=13) = False

So it will return true if it isn't 13...

Use this in the suppression formula area of the group or detail band where you're displaying only 13's.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[SeeBee]

Yahoo! - it worked.​

Thank you for all your help.

 

[synapsevampire]

That's TEK-Tips! - it worked!

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

SeeBee,

I have noticed this thread as ongoing for quite awhile.

It is obvious to me that Dgilz and SynapseVampire both put some time in to thinking about your problem and helping you.

It is customary on these forums to award a star to those that are helpful or who provide an expert tip. I just thought I would let you know as you seemed appreciative of their help.

( I have awarded Dgilz and SynapseVampire a Star each myself in recognition of their hanging in there to help someone over the longhaul!)

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

And I awarded Ro one just cuz I wanted to

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com