I have these 3 tables. The item master itself will have master and child records in it (item_id=m_item_id).

ITEMS_MASTER
------------
item_id
category
m_item_id
upc_ean
sale_unit
case_pack
sell_size
description

ITEM_COST
---------
m_item_id
cost_zone
start_date
end_date
net_cost

ITEM_PRICE
----------
m_item_id
cost_zone
start_date
end_date
retail
margin

I need to create a report in the following format.
_____________________________________________________________________
upc item_id description (for master item)
sale_unit case_pack sell_size

(display all the child items if it has any.)
(Then display the price and cost information as follows with titles order by cost_zone for the current m_item_id - The number of rows in item_price and item_cost can be different for the same item.)

Start Date End Date Net Cost | Start Date End Date Retail Margin
---------- -------- -------- | ---------- -------- ------ --------
01/01/2005 01/20/2005 $1.04 | 01/01/2005 01/20/2005 $1.27 24.8%
01/21/2005 02/15/2005 $0.97 | 01/21/2005 02/15/2005 $1.17 22.4%
02/16/2005 03/01/2005 $1.04 | 02/16/2005 $1.27 24.8%
03/02/2005 $1.03 |
_____________________________________________________________________
Does anyone have any ideas or suggestion on how to do this? Any help will be greatly appreciated.

Could anyone give me some ideas on how to create this report please?

How are you trying to create these reports? (ie MRE or Dev Studio) Do you know what type of database back-end you have? If you do could you just simple write an SQL pass-through? This maybe easier for you since you will only need to use FOCUS to make the report look pretty.

If you don't want to write SQL the one thing that comes to mind on how to get the information you want is to do a multi verb request. This would show a summarized and detail report all on one report.

Thanks for your reply Curtis. The backend is Oracle 9i and needs to create it on dev studio. This report will be called (included) as the part of a main compound report. I recently started working with Webfocus and have got very little experience on it. Could you please provide me with any examples of SQL-pass through and multi verb request?

Here is some sample code to do an SQL pass-through:

SQL SQLORA<br />SELECT<br />'X' HI<br />FROM DUAL<br />;<br />TABLE<br />END

Here is some sample code for a multi verb request.

TABLE FILE CAR
SUM
RETAIL_COST
BY
COUNTRY
SUM
RETAIL_COST
BY
COUNTRY
BY
CAR
PRINT
RETAIL_COST
BY
COUNTRY
BY
CAR
BY
MODEL
END

This message has been edited. Last edited by: <Mabel>,

Thanks Curtis! I will try this. Have a nice afternoon!