Record Selection

[BradCustom]

I need some help with a record selection formula. I'd like to select only the negative values from the formula below.

Total Job Cost:
{Job.Unit_Price}-({@Pass_Component}+{@Act Cost})/(if {Job.Completed_Quantity}=0 then {Job.Order_Quantity} else {Job.Completed_Quantity})

I've tried creating a variable to achieve this but I can't seem to get it to work.

The report is grouped by Job and Part and I'm calculating the total cost against a job and passing the cost of any sub-component jobs to the main report. The formula shown above calculates the accumulated cost on a job vs. the unit price.

Thanks for your help!

 

[lbass]

This is unclear. You should the content of your nested formulas, to begin with. This appears to be a subreport, but I am unsure whether you are trying to use the formula to link to the main report or whether you want to limit what displays in the subreport via the record selection formula.

-LB

 

[lbass]

You should show the content...

-LB

 

[BradCustom]

Sorry I'll try and explain the report.

The formula from the previous post is on the main report but there is a passed value from the subreport {@Pass_Component} which is the total cost for Sub-Component jobs that are linked to the Top Level Job.

The purpose of the report is to calculate the total cost against the job. Some jobs are single level and others are multi-level.

I calculate the job cost in the following way:

Actual Job Cost:
({Job.Act_Labor}+{Job.Act_Material}+{Job.Act_Service})

The formula above works fine for single level jobs but not for multi-level jobs. On multi-level jobs I calculate their cost on the subreport and pass the total of the lower level jobs to the main report using the "Pass Component" formula.

Subreport Pass:
whileprintingrecords;
shared Currencyvar ComponentCost := Sum ({@Act Cost}, {Job.Top_Lvl_Job})

Main report Pass:
whileprintingrecords;
shared currencyvar ComponentCost;
ComponentCost

With the component job cost passed to the main report I then calculate the total cost for the top level job and the lower level jobs using the formula "Total Cost".

Total Cost:
{@Pass_Component}+{@Act Cost}

The next calculation is to determine if the job cost is more than the unit price.

Total Job Cost:
{Job.Unit_Price}-({@Pass_Component}+{@Act Cost})/(if {Job.Completed_Quantity}=0 then {Job.Order_Quantity} else {Job.Completed_Quantity})

The main report is grouped by Job.Job and Job.Part_Number and the subreport is linked to the main report by Job.Job to Job.Top_Lvl_Job and by a date range parameter "Status Date".

What I'd like to do is show all the jobs whose "Total Job Cost" is less than 0.

I hope this helps; please let me know if you need anymore information.
Thanks

 

[BradCustom]

Below is some sample data from the report.

Status Status_Date Unit_Price Cost Ea Total Loss Ea Component Cost Job Total Job
Closed 01/13/2009 $ 349.74 $ 84.94 $ 264.81 $4,971.30 $ 5,096.10 3002737
Closed 01/13/2009 $ 233.04 $ 91.79 $ 141.25 $26,641.25 $ 26,987.45 3002744
Complete 01/13/2009 $ 37.59 $ 154.00 ($116.41) $ 53,282.50 3002744-1
Closed 01/08/2009 $ 46.90 $ 172.35 ($125.45) $ 37,055.65 3003113
Closed 01/13/2009 $ 47.30 $ 199.05 ($151.75) $ 34,635.25 3003662

 

[IanWaterman]

You could simply use row suppression.

In section expert for relevant section simply enter the following in the suppress formula box.

@totalLossFormula > 0

If you want total any of the data you will need to modify running totals or variables to exclude results which also meet the above condition.

Ian

 

[lbass]

In your sample, none of the columns is labeled "Total Job Cost" and the only negatives I see are the in the total loss column, so I cannot really tell what you intend. Ian's suggestion may fit the bill.

-LB

 

[BradCustom]

Thanks!
That worked fine.

 

[BradCustom]

On this report I can't seem to get the Count funtion to work correctly. I've used the suppression formula that Ian suggested and it works great for the diaplay and the other formulas I'm using but I'm having trouble geting it to work on the Count formula. The formula either counts all or none of the records.

I'm trying to count only the records where {@Job Loss} is less than 0.

Here is my last attempt.

Total Jobs:
if {@Job Loss}<0 then
Count ({Job.Status})

This counts all of the records; negative and positive.



Thanks for your help!

 

[lbass]

Please ALWAYS show the content of your formulas and any formulas nested within, as it affects the available solutions.

-LB

 

[BradCustom]

Here is a sample of the data

[COLOR=blue]3003113                 Closed 1/8/09 $ 46.90 $ 10,083.50 $ 10,414.40 $0.00 $ 0.00 $ 10,414.40 ($330.90)[/color]

[COLOR=red]Comp Qty 43 Make Qty 42[/color] Status Date  [b]Unit_Price[/b] Revenue  [b]Labor[/b]   Mat'l Cost  [b]Service[/b] Comp Cost [b]Total Cost[/b]   Total Revenue
3003875 32-62315-4018-5 Complete 1/7/09 [b]$ 112.00[/b]  $ 4,816.00 [b]$ 2,439.55[/b] $232.73[b]$ 0.00[/b]         [b]$ 2,672.28[/b] $ 2,143.72

[COLOR=red]Comp Qty 0 Make Qty70[/color] Status Date [b]Unit_Price[/b] Revenue [b]Labor[/b] Mat'l Cost [b]Service[/b] Comp Cost [b]Total Cost[/b] Total Revenue
3004201 35-62315-2574-3 Complete 1/8/09 [b]$ 97.00[/b] $ 6,790.00 [b]$ 4,133.90[/b] $196.05 [b]$ 68.00[/b]   [b]$ 4,397.95[/b] $ 2,392.05

[COLOR=red]Comp Qty 320 Make Qty 310[/color] Status Date [b]Unit_Price[/b] Revenue [b]Labor[/b] Mat'l Cost [b]Service[/b] Comp Cost [b]Total Cost[/b] Total Revenue
3004244 A20AT-000-09 Complete 1/7/09 [b]$ 6.48[/b] $ 2,073.60 [b]$ 88.20[/b] $249.88 [b]$ 0.00[/b]             [b]$ 338.08[/b] $ 1,735.52

[COLOR=red]Comp Qty 0 Make Qty30[/color] Status Date [b]Unit_Price[/b] Revenue [b]Labor[/b] Mat'l Cost [b]Service[/b] Comp Cost [b]Total Cost[/b] Total Revenue
500001 TS01232-0315-01 Closed 1/7/09 [b]$ 219.00[/b] $ 6,570.00 [b]$ 34.00[/b] $0.00 [b]$ 0.00[/b]           [b]$ 34.00[/b] $ 6,536.00

[COLOR=red]Number of Loss Jobs: 5.00 Total Profit: $12,807.29[/color]

In the sample above the blue line is being counted even though it's a negative result. This line is normally surpressed but I un-surpressed it for clearity. The red line at the bottom is the report total. The bold field headers and bold fields go together.

The formulas are as follows:

Revenue:
{Job.Unit_Price}* (if {Job.Completed_Quantity}=0 then {Job.Make_Quantity} else {Job.Completed_Quantity})

Comp Cost: Passed from Subreport
whileprintingrecords;
shared currencyvar ComponentCost;
ComponentCost

Total Cost:
({Job.Act_Labor}+{Job.Act_Material}+{Job.Act_Service})+ (if {Job.Type}="assembly" then {@Pass_Component} else 0)

Total Revenue:
{@Revenue}-{@Total Cost}

Job Cost Var:
whileprintingrecords;
currencyvar Cost := cost + (if{@Total Revenue}>0 then {@Total Revenue})

Total Profit:
whileprintingrecords;
currencyvar Cost;
Cost

Total Jobs: Number of Loss Jobs
if{@Total Revenue}<0 then {@Total Revenue};
Count ({Job.Act_Labor})

The section surppression formula is listed below and is used on the Job group, Part Group and Detail Section.

{@Total Revenue}<0

If you need any more information please let me know.

Thanks for your help!

 

[lbass]

I don't see {@Job Loss} there. Please use the formula names consistently.

-LB

 

[IanWaterman]

A quick solution is to change your formula

Total Jobs:
whileprintingrecords;

global numbervar jobcount;

if {@Job Loss}<0 then
jobcount:= jobcount+1;

In report footer
@display
whileprintingrecords;

global numbervar jobcount;


If you want to show count by a group, place @display in group footer. And add another formula to group header.

@reset
whileoprintingrecords;

global numbervar jobcount:=0;

Ian

 

[BradCustom]

{@Job Loss} has been changed to {@Total Revenue} because I'm using this report twice. One for negatives and one for positives.

I've found another problem:
With the currencyvar it seems if I run the report for a date range less than 14 days the first line doubles. If the date range is longer than 14 days it works fine. I thought it might be the subreport but none of the Groups are time based. The groups are Job.Top_Lvl_Job ; Job.Job ; Job.Part_Number. There is only one date field used in the subreport {status_date} which I have a date range parameter.

Below is the Subreport record selection formula:
{Job.Status} in ["Closed", "Complete"] and
{Job.Unit_Price} > 0.00 and
{Job.Status_Date} = {?Status Date}


Below is the main report currencyvar:
Job Cost Var:
whileprintingrecords;
currencyvar Cost := cost + (if{@Total Revenue}>0 then {@Total Revenue})

If the first record found is $50 then the Job Cost Var is $100

Thanks for your help!

 

[BradCustom]

Ian,

Thanks the Global numbervar works great!!


Thanks for your help!