Got a data block ranked - top 10 customers based on number of calls (measure).
I want to have a second block showing detailed information for the top 10 customers identified in the first block. How do I display/filter the data in the second block by the rank in the first block? I can’t drop the measure into the second block as by the time the detail is added, it’s counting 1 per row so the rank is meaningless.
I tried creating a rank variable rank([customer];([count calls])) which validated. But when I drop into a block, I get a context error.
Anybody got any ideas? I’ve never used ranking before…
I am not sure I understand what you are saying about not being able to put the measure in the second block. Can you not also rank the second block? Can you explain a little further?
Just a sketch/try:
Create one query/data provisder giving you the top 10 customers (using database ranking). Create a second query/data popovider using the using the query on query feature, passing the customer IDs from query 1 as input paramaters (Condition/SQL WHERE clause) to query 2.
There’s an idea. But I’ve a feeling that query on query is an XIr3 feature? Or is it available in R2 which is what we are still on?
I’m actually running this with custom SQL at the moment, using any old Bob objects as placeholders for the code.
What I’m trying to achieve is this:
Find all people (based on name - yes, I know that’s not the best way, but it’s all I have) who have called more than once in the last month.
Block 1: Show the names/number of calls of the ten people who have called the most number of times
Block 2: Show the full call details for each of these people.
So I’d have approx 10 rows in block 1 and lots of rows in block 2
The SQL is grabbing a list of names of callers in last month (with count(*)>1), then using this list as an inlist for an outer query which grabs the call details.
If there’s a better way of doing this, I’d be grateful to know!
Argggh! Any idea how I apply the ranking in the SQL code? I can do it in webi in the report afterwards, but I’m also trying to do it in the code so I can use it to popluate xcelsius. If I apply the ranking (using oracle’s RANK() function) in the outer query, it just gives me 1 for everything. If I apply it on the inner query I’ve now got too many objects selected to use an inlist…
Debbie
edited to add: this is what I’ve got to work, but it’s slow and looks clunky. is there a better way?
select [some columns]
from [table]
where [date] between trunc(add_months(sysdate,-1),'month') and last_day(add_months(sysdate,-1)) and [name] in
(select [name] from
(select [name],rank()over(order by count(*) desc) as rank from [table] where [date] between
trunc(add_months(sysdate,-1),'month') and last_day(add_months(sysdate,-1)) group by [name] having count(*)>1)
where rank <=10)
group by [some columns as above]
order by [name]
Apologies - I’m now off-topic, but it’s related to start of topic as it’s all the same thing really - trying to do summary data in xcelsius and link to detail in webi.
(BOB member since 2005-03-01)
(BOB member since 2009-08-28)
(BOB member since 2002-06-20)