RE: Is it possible to create Matrix FORMs

From: LGE <elkinsl_at_flash.net>
Date: Sun, 17 Sep 2000 17:30:03 -0500
Message-Id: <10622.117209@fatcity.com>

A few more questions.

Are the regions known in advance? For example, will the regions always be regions "A", "B", "C", and "D"? If so, the link pointed to by another respondent illustrates a way to do a matrix when the values are "known" and set in stone. You could create a view, or, if on Forms 5.0 and above you could consider a "From Clause" query, or, a block based on a stored procedure. I have a couple of example forms sitting around here at home.

Regarding the sub-totals, are you talking about on the detail lines rolling up, or, maybe column totals only (Regions A Totals, Region B totals, etc, or both? Just this past week, I was asked to tune the SQL for a Form displaying data in a "matrix" format along with sub-total lines aggregating up through 4 levels. So, there a few ideas and approaches that can be applied from that. If sub-totaling the detail lines, are the number of "levels" known in advance? If so, things are much easier.

Is this for displaying the data only, or, do the users need to be able to update the data, and, see the sub-totals and such change?

Do you need to do DML, actually updating the underlying data?

Sorry for so many questions; but, there are numerous approaches that can be used, and, how you might go about doing things varies based on answers to the above questions. Also, with regards to the layout, can you save that info to a text file that retains the layout and send it to me as an attachment? My mail reader kind of scrambled it around. It looks like it is based on parts, orders for those parts, with columns for region:

        Region A Region B

Part1   Order1 ...   Order6 ...
        Order2 ...   Order7 ...
Part2   Order2 ...   Order8 ...
        Order3 ...   Order9 ...

If you are still working on this and need some ideas, let me know. I have seen some pretty creative approaches to this problem.

Regards,

Larry G. Elkins

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of prashasta gujrati
Sent: Thursday, September 14, 2000 2:11 PM To: Multiple recipients of list ORACLE-L Subject: Re: Is it possible to create Matrix FORMs

Hi Elkins,

Thanks for your interest.

I'll briefly tell about the application for which I'm looking for such a solution.

This is a Demand allocation application where there is demand for certain products from various regions. This demand is in form of Orders placed on manufacturing unit. Allocation is to be done manually keeping in mind the proper distribution of products to various regions.

Table structures are like this :

region_codes

---

region_code
description

part_master

---

part_no
description

oeorder_release

---

order_no
part_no
region_code
demand_qty
allocated_qty
(order_no + part_no is the primary key)

The screen layout which I have in mind is :

                                   region1
region2                                     region3

region4

                     Order1    demand   allocation      Order4    demand
allocation      Order7    demand   allocation     .......
Part_no1       Order2    demand   allocation      Order5    demand
allocation      Order8    demand   allocation     .......
                     Order3    demand   allocation      Order6    demand
allocation      Order9    demand   allocation     .......

                     Order11  demand   allocation      Order14  demand
allocation      Order17  demand   allocation     .......
Part_no2       Order12  demand   allocation      Order15  demand
allocation      Order18  demand   allocation     .......
                     Order13  demand   allocation      Order16  demand
allocation      Order19  demand   allocation     .......

                     Order21  demand   allocation      Order24  demand
allocation      Order27  demand   allocation     .......
Part_no3       Order22  demand   allocation      Order25  demand
allocation      Order28  demand   allocation     .......
                     Order23  demand   allocation      Order26  demand
allocation      Order29  demand   allocation     .......

and so on...

Then there are totals as well which have to be shown at various levels.

It is as if we are trying to get Excel spreadsheet kind of functionality out of

forms.

Any ideas will be greatly appreciated. I've tried checking metalink but it Received on Sun Sep 17 2000 - 17:30:03 CDT