Rank and Count function in Obiee 11g

AbinashBehera

Hi All,

I got a requirement which I am facing issues while implementing, Please find the below details: Suppose I have the below table, where there two groups, group1 and group2, Each group is having three category, A,B, C . and their members business. Shown In First Table

 

Groups	Category	Members	Business
Group1	A	M1	1000
Group1	A	M2	2000
Group1	A	M3	1500
Group1	A	M4	2500
Group1	B	M5	3000
Group1	B	M6	3500
Group1	B	M7	4000
Group1	B	M8	4500
Group1	C	M9	5000
Group1	C	M10	5500
Group1	C	M11	6000
Group2	A	M12	4000
Group2	A	M13	4500
Group2	A	M14	9000
Group2	A	M15	8500
Group2	B	M16	1000
Group2	B	M17	2500
Group2	B	M18	3500
Group2	B	M19	3000
Group2	C	M20	7000

 

-------------------------------------------------------------------

  But The Report should be like this(without Members). But I have to consider the top N(as per user input) members(based on their business) from the first table. till now there is no difficulty

Groups	Category	Business
Group1	A	7000
	B	15000
	C	16500
Group2	A	26000
	B	10000
	C	7000
Total Result		81500

-----------------------------------

Now the difficulty I am facing while adding the count of members in the respective Group and Category after the Top N filter Applied.

So the Actual Report Output should be as below, with Top 10 as input.

 

Groups	Category	Member Count	Business
Group1	B	2	8500
	C	3	16500
Group2	A	4	26000
	C	1	7000
Total Result		10	58000

-------------------------------------------------

Thanks,

Abinash