Custom sorting inner leaf nodes in hierarchy in OBIEE report

Rahul Huilgol

Hi everyone,

We have a requirement of showing the same hierarchy in different ways in different reports. To illustrate, the hierarchy is defined in the RPD as follows:

	LEVEL-1
	A
	A1
	A2
	A3
	B
	B1
	B2
	LEVEL-2
	C
	C1
	C2
	D
	D1
	D2
	D3

We need the hierarchy to be shown in one report with sequence numbers beside as follows:

	LEVEL-1
1	A
[i]	A1
[ii]	A2
[iii]	A3
2	B
[i]	B1
[ii]	B2
	LEVEL-2
3	C
[i]	C1
[ii]	C2
4	D
[i]	D1
[ii]	D2
[iii]	D3

And in the second report, as follows:

	LEVEL-1
1	A
[i]	A3
[ii]	A1
[iii]	A2
2	B
[i]	B1
[ii]	B2
	LEVEL-2
3	C
[i]	C2
[ii]	C1
4	D
[i]	D1
[ii]	D3
[iii]	D2

We are trying the CASE statement method of sorting by another column for the sequence number and we have also tried creating a GROUP in the report to set the sequence of our choice. Neither of these have succeeded.

To write a CASE statement based on a Hierarchy object doesn’t seem possible as no hierarchy objects appear in the left panel in the formula editor.

Please advise.

Thank you!