I'm working with dimensional data and I'm using the 'topcount' function to retrieve the top 5 customers; I'm using the 'except' function for the remaining customers. 'Top 5 Customer' and 'All Others' both work fine in the crosstab. Now I have a request to take a specific customer from the 'All Others' and roll that customers sales into one of the top 5 customers.
(ex.)
if ( customer in ( Store 1, School 5 ) then 'Store 1' else (customer))
Top 5 Customers include =
Store 1, Store 2, Store 3,Store 4 ,Store 5 ,
All Others include =
School 1, School 2, School 3, School 4, School 5, School 6, School 7, School 8
I've tried creating data items, query calculations, member sets..and I get the 'level to string' error or the like. I can't get it work.
Any help would be appreciated.
Sounds either like manipulation of the results or something that should be handled in the ETL.
Agreed or at least before I started on the report