Strange output query

[Former Member]

Dear all, I have created a report which calculates number of Jobs from two different tables and fields. Eventually I would like to add them together to get the total number of jobs.

So far I have created two manual running totals grouped by reason code to check if the running totals are working or not.

The problem I am facing is that when I enter a condition on one of the total jobs formula, the other total jobs formula produces 0s.

For example:

If I enter condition in Total Jobs 2 of Status=2, the report produces the correct results for Total Jobs 2 however produces 0s for the Total Jobs 1. On the other hand if I delete the condition of Status=2, the report produces the correct results for Total Jobs 1 however produces incorrect results for Total Jobs 2.

The Reason Code(Grouped) is common in both however the table fields are totally different, where Total Jobs1 distinct counts the Order numbers from Credit table, Total Jobs 2 distinct Counts the Order numbers from Order Lines table. There are cases when one order from either table might not be same. For example

Reason Code ---Total Jobs1 --- Total Jobs 2

1--

---

3--

---

4

2--

---

0--

---

1

3--

---

1--

---

0

4--

---

2--

---

4

In the above example, if I do not use Status=2 for total Jobs 2, the results are not produced correctly where as if I include status=2 the total jobs 1 produces 0s.

Here is the code for total jobs (Please note: the reset is already setup on group header and working days formula is used to count number of working days).

Total Jobs 1:

Whileprintingrecords;
Numbervar TFOC;
Numbervar TotalJobs=0;
Numbervar CAvg=0;
TotalJobs := DistinctCount({Order_Header_Order_no});  
CAvg:= TotalJobs /{@WorkingDays};
TFOC:=TFOC + CAvg;
CAvg;

Total Jobs2:

Whileprintingrecords;
Numbervar TCR;
Numbervar TotalJobs=0;
Numbervar CAvg=0;
TotalJobs := DistinctCount({Oetrn2_Order_num);  
CAvg:= TotalJobs /{@WorkingDays};
TCR:=TCR + CAvg;
CAvg;

What I am trying to achieve here is to get all the reason code regardless of order numbers however they all should be grouped by reason code.

Like the example given above, where Total Jobs 1 does lie under reason code 2 however it is still showing as zero.

I suspect is it to do with database link however not sure where the problem lies. Here is the setup of Database tables link

Lab_Credit.Order_no--->Lab_Credit2.Order_no

Lab_Credit.Order_no--->Oetrn2.Order_num

Lab_Credit.Credit_no--->Lab_Credit2.Credit_no

Lab_Credit.Reason_code--->Lab_Reasons.Reason_code

Lab_Credit2.line_no---->Oetrn2.Line_no

Lab_Credit2.linetype---->Oetrn2.Linetype

All of them are inner join.

any ideas where am I going wrong?

Regards

Jehanzeb

[Former Member]

no solution found for this however moving on.