Problem in Displaying Low granularity data at Higher level

building reports webintelligence
1

I have End of Month Stock Volume calculated at Market level. Requirement is to display pie charts at region, sub region and market level with count of records that matches the selection criteria(drill filter)…

There is a drill filter in the report to say show me only stocks greater than or equal to 10, 15,20…100 etc. Drill filter acts like a slider… Based on the Drill filter, matching records have to be counted and the count should be displayed at Region, Sub Region and Market level…

This is bit complex to explain… Here is an example…

Region SubRegion Market Brand Stock
Europe - Area1 - UK – A – 10
Europe - Area1 - France A – 22
Europe - Area1 - Germany A – 30
Europe - Area1 - Swiss A – 40
AP&Japan - AP - China A – 5
AP&Japan -AP - Austalia A – 15
AP&Japan -AP - Malaysia A – 25
AP&Japan -AP - Sigapore A – 30

When I select 20 in the drill filter… I have to show

Europe 3
AP&Japan 2

When I select 10 in the drill filter I have to show

Europe 4
AP&Japan 3

I managed to get the count but it works when I have Stock volume in the bolck… Also the Region name is repeating becos the granularity of the stock volume is at Market … So I unable to turn it to a pie chart…

I am struggling here with this report…Any help will be deeply appreciated…

Thanks a lot…

If the explanation is not clear please let me know… I will try it with different examples…

dhayanithij (BOB member since 2007-06-26)

2

Try creating a variable like this …

=Sum(If(sum([Stock])>20;1;0)ForEach([Market Brand]))

rimpa :india: (BOB member since 2008-04-14)

3

Great thanks for your reply…

I tried as per your advice… I am getting Multi Value error…

With Region, Stock Vol and new variable (Sum(If(sum([Stock])>20;1;0)ForEach([Market Brand]))) In [Region]) … It works fine… But when I remove the stock volume it gives multi value error…I cannot have stock volume in the block as it has to be a pie chart with just count for each region…

Forgot to mention before; Stock Volume is non aggregate measure…Get multiple records for each region…

Thanks again for your time… Spent a day on this trying all combinations of Input/Output contexts but no luck:(…

BO Should come with report as a datasource feature… that will be an easy solution for this requirement…

Hope you will come up with some fix for this…

Cheers

dhayanithij (BOB member since 2007-06-26)

4

if stock is non agrregate measure , try taking out sum() from within the IF() statement…
a slight variation of this variable should work for you… I tried replicating it on my end… it worked for me…

rimpa :india: (BOB member since 2008-04-14)

5

It works fine now… Great thanks for your help… I made the measure aggregatable one and same formula worked…

Thanks again…

Cheers

dhayanithij (BOB member since 2007-06-26)