-------------- Original message --------------
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.
I have tried creating a report variable vcount_gen defined as:
COUNT equip_code FROM wo_file WHERE equip_code = equip_code
I have also tried:
COUNT equip_code FROM wo_file WHERE equip_code = .vequip_code The _expression_ builder will not allow parentheses, but the result always shows as 0.
At the R> prompt, the _expression_:
SET VAR vcount_gen = COUNT equip_code FROM wo_file WHERE equip_code = .vequip_code
Works correctly
I have also tried a variety of SELECT and COMPUTE statements with no luck. I know I have used this before but right now I am drawing a blank. A cross-tab report would work but it would show only the equipment for which there are work orders and I need to show all the equipment records with 0 if nor work orders are available. Any help will be greatly appreciated.
Javier,
Javier Valencia, PE
President
Valencia Technology Group, L.L.C.
14315 S. Twilight Ln, Suite #14
Olathe, Kansas 66062-4578
Office (913)829-0888
Fax (913)649-2904
Cell (913)915-3137
================================================
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
delete the material from all system and destroy all copies.
======================================================
Javier,
I just tried the following and it worked fine. The report was based on
a table named "ShipLoc" and had a column named ORDNUMBER.
The table ORDERTAB also had a column ORDNUMBER with multiple
records of each order. (Line items). The following variable was placed on
the DETAIL line (and marked to calculate on DETAIL). It returned the
number of line items for each order.
(count(ORDNUMBER)) in ORDERTAB where ORDNUMBER = ORDNUMBER
-Bob
