• Leistungsspektrum
    Die passenden Lösungen für Ihre Anforderungen.
    Mehr Details unter der Rubrik: Leistungen
  • Professionelle Anwendungen
    Holen Sie mehr aus Ihren APEX Applikationen heraus.
  • Neueste Designs
    Nutzung von erweiterten Web 2.0 Möglichkeiten und Entwicklung von Corporate Designs
  • Individuelle Lösungen
    Entwicklung von Business-Anwendungen genau nach Ihren Wünschen.
  • Upgrade Lösungen
    Weiterentwicklung bestehender Anwendungen.
  • Schulungen und Vorträge
    Die passenden Schulungen genau für Sie zugeschnitten. Beispiel: Navigationsbeispiele
  • Plugins und Third Party Erweiterungen
    Erweitern Sie die Funktionalität ihrer Anwendungen mit einem modularen Entwicklungsansatz.

16 August, 2013

Connect a grouped report to an ungrouped report with virtual ID column

Seems to be a simple problem. I have and grouped report where I want to see all facilities on a address.

For example:
Grouped View
EditAddressAmount of facilities
xGermany, Dresden, Dresdner Strasse 12
xGermany, Frankfurt, Frankfurter Strasse 13

Detail View
AddressFacility
Germany, Dresden, Dresdner Strasse 1Computer System EXAXY
Germany, Dresden, Dresdner Strasse 1Computer System KI
Germany, Frankfurt, Frankfurter Strasse 1Manufactoring System 007
Germany, Frankfurt, Frankfurter Strasse 1Manufactoring System 009
Germany, Frankfurt, Frankfurter Strasse 1Manufactoring System 028

How to achieve this when we do not have a primary key and our key column includes commas which breaks the link. We easily generate an own ID column via an analytical function. In both reports we use an Oracle view to get the data from and our view looks like that:
select   facility_address,
         facility_name,
         dense_rank() over (order by facility_address) as facility_id
from     facilities
Via the function dense_rank and the partition by facility_address we get an unique ID for all facilities based on the address.
The grouped report looks like that:
select   facility_id,
         facility_address,
         count(facility_name) as amount_of_facilities
from     facilities
The detail report looks like that
select   facility_address,
         facility_name
from     facilities
where    facility_id = :P2_FACILITY_ID
Now you need to add a link in the report attributes section inside the grouped report. We set the item :P2_FACILITY_ID with our column #FACILITY_ID#.
That's it.

1 comment:

http://majesticessay.com/ said...

Generating an ID column seemed to help a lot.