[RBG7-L] - Re: Counting records in a different table in areport

[A. Razzak Memon]

At 05:01 AM 7/20/2005, Javier Valencia wrote:

I have a report where I have to show the equipment code and then on the
same line show the number of related records (work orders) in a different
table linked by the equipment code.

Javier,

Here's how:

*** Using R:BASE 7.5 for Windows ***:

01. Add the slave table to the report

    Report Designer | Main Menu | Tables | Add/Remove Tables...

02. Place the "DB Calc" control on the [Detail] band and associate that
    DB Control with the "slave" table and the matching column, such as
    EquipmentCode.

   Now change the "Calculations..." property of DB Calc to Cal Type
   as "Count".

*** Using R:BASE 7.1 for Windows ***:

01. Define a VIEW with the following two columns, such as:

    EquipmentType and WorkOrderCount

    CREATE VIEW EquipmentCountView +
    (EquipmentType, WorkOrderCount) AS SELECT +
    EQuipmentType, (COUNT(*)) +
    FROM WorkOrders GROUP BY EquipmentType

    This dynamic view will include the EquipmentType and the total count
    by each EquipmentType.

02. Now use the WorkOrderCount column in EquipmentCountView as a
    lookup expression to display the results you need.

    vWorkOrderCount = WorkOrderCount IN EquipmentCountView WHERE +
    EquipmentType = EquipmentType

That's all there is to it!

Very Best R:egards,

Razzak.