|Edit||Address||Amount of facilities|
|x||Germany, Dresden, Dresdner Strasse 1||2|
|x||Germany, Frankfurt, Frankfurter Strasse 1||3|
|Germany, Dresden, Dresdner Strasse 1||Computer System EXAXY|
|Germany, Dresden, Dresdner Strasse 1||Computer System KI|
|Germany, Frankfurt, Frankfurter Strasse 1||Manufactoring System 007|
|Germany, Frankfurt, Frankfurter Strasse 1||Manufactoring System 009|
|Germany, Frankfurt, Frankfurter Strasse 1||Manufactoring 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 facilitiesVia 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 facilitiesThe detail report looks like that
select facility_address, facility_name from facilities where facility_id = :P2_FACILITY_IDNow 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#.