I'm looking to create some sort of report, similar to the attached image. I don't mind using either the classic report with a custom layout, or a pl/sql region and manually creating the table. The trouble I'm having is how to correctly arrange and pivot the data to match the required layout. Xml seems like it could be a good way to do it, but I'm a bit confused about all the functions that are available to generate and render the xml.
Apex version is 4.22
Oracle version is 11.2
Required layout:
Sample data:
WITH test_responses AS (
SELECT 1 AS test_id, NULL AS assoc_test_id, 'Category One' AS cat_name, 10 AS question_id, 'Text one' AS text, 3 AS rating FROM dual
UNION ALL
SELECT 1 AS test_id, NULL AS assoc_test_id, 'Category One' AS cat_name, 20 AS question_id, 'Text two' AS text, 2 AS rating FROM dual
UNION ALL
SELECT 1 AS test_id, NULL AS assoc_test_id, 'Category Two' AS cat_name, 30 AS question_id, 'Text three' AS text, 2 AS rating FROM dual
UNION ALL
SELECT 1 AS test_id, NULL AS assoc_test_id, 'Category Two' AS cat_name, 15 AS question_id, 'Text four' AS text, 1 AS rating FROM dual
UNION ALL
SELECT 1 AS test_id, NULL AS assoc_test_id, 'Category Three' AS cat_name, 22 AS question_id, 'Text five' AS text, 2 AS rating FROM dual
UNION ALL
SELECT 2 AS test_id, 1 AS assoc_test_id, 'Category One' AS cat_name, 10 AS question_id, 'Text three' AS text, 1 AS rating FROM dual
UNION ALL
SELECT 2 AS test_id, 1 AS assoc_test_id, 'Category One' AS cat_name, 20 AS question_id, 'Text two' AS text, 2 AS rating FROM dual
UNION ALL
SELECT 2 AS test_id, 1 AS assoc_test_id, 'Category Two' AS cat_name, 30 AS question_id, 'Text one' AS text, 3 AS rating FROM dual
UNION ALL
SELECT 2 AS test_id, 1 AS assoc_test_id, 'Category Two' AS cat_name, 15 AS question_id, 'Text four' AS text, 2 AS rating FROM dual
UNION ALL
SELECT 2 AS test_id, 1 AS assoc_test_id, 'Category Three' AS cat_name, 22 AS question_id, 'Text six' AS text, 1 AS rating FROM dual
)
The tests are linked by test_id -> assoc_test_id. There will only be one current and one previous test per 'link'.
When assoc_test_id is NULL then this is the previous test.
When assoc_test_id is NOT NULL then this is the current test.
The categories and question id's between the associated tests will always match. only the text and rating will change.
Importantly, different tests may have a different amount of categories included.
Is anyone able to help me put this together?. in the future more than one of these may be required on a page, so IRR is not possible.