Distinct Count

[cognostechie]

We have a situation where the roll-up from the lower level to the higher level is not additive. I will try to explain with an example:

Service      Brands     Products      Customers
--------------------------------------------------------

S1             B1             P1                 C1
S1             B1             P2                 C1
S1             B2             P3                 C1
S1             B1             P1                 C2
S1             B2             P3                 C2
S1             B3             P4                 C1

According to the dimension heirarchy, Service is the highest level, Brands the 2nd highest , Products below Brands. Customers are not part of the dimension (I am putting it above for explaining the problem).  Now...what we have to do is show the no. of customers for each level. In reality , it is:

                                                    No. of Customers

Service                                                                 2
   Brands   - B1                                           2
                 - B2                                           2
                 - B3                                           1        5       

   Products - P1                                           2
                  - P2                                           1
                  - P3                                           2
                  - P4                                           1       6

The problem is , it shows fine at the lowest level (Products) but when we see the no. of customers at higher level, it adds up the lower level, so for Brands it shows 6 customers (currently I have a count as a measure to count no. of records..I know this is whacky). 

The report is a Report Studio report and the data source is a cube. I tried a 'distinct count' in the Report Studio so it would count every time at run time but it says 'not supported for OLAP'. The cube is created thru IQDs from Framework Manager.

What/where I have to do to get this right?   Anybody ran into this problem earlier? Any help will be greatly appreciated.

Thanks
Steve

[tdyoda]

I confess to being one of the 32 viewers and decided to delurk.

I'll throw out any concern about the output - like it has to go into a cross tab to meet the formatting requirement.

That leaves me with a clear challenge, but I'm not sure what the report needs to output.... 

For numbers that don't roll up you end up with an additional query that needs to be run at each level.  Which is definitely isn't convenient from a development perspective, but is a very reasonable question to answer.

[cognostechie]

Well, thanks for participating ...makes me feel better :)

The output would be showing 'Subscriber Count' which is a measure for a Product Line dimension. When I drill down , it should show 'Subscriber Count' for the lower level. Just like showing sales in the Great Outdoors cube by Product Line. Replace SalesÃ,  by Subscriber Count here.

A dif. report for every level is ruled out as then it defeats the purpose of drill down..and this report is part of a Dashboard so cannot change screens. Problem is the lower level added together is more than the count of higher level. I did a category count in Transformer and it works perfectly but only for a small amount of data. Transformer cannot exceed 2 million categories and in this case I have more than 3 million which makes it fail.

So I am looking for an alternate solution..