top 10 accounts for every day with summary

hi,

i am new to mdx and stuck on a problem on analysis services 2005.

there is an orders cube with calendar and accounts dimension.

the report i want to do is something like this

date account quantity top10percentage

01.01.2007 All 1000 85

bnm 230 23

abc 150 15

bbc 140 14

aaa 100 10

wer 60 6

rrr 40 4

xyz 35 3,5

xtv 35 3,5

tyu 30 3

tbh 30 3

02.01.2007 All 1230 75

abc 150 15

bnm 150 15

bbc 140 14

aaa 100 10

txp 60 6

rrr 40 4

xyz 30 3

xtv 30 3

tyu 25 2,5

tbh 25 2,5

i have written a calculated member for the percentage of the account for that day's total order amount. but when i do a filter on accounts dimension to get top 10 records for that day in excel 2007 the total value of the percentage for that day remains 100. not the sum of the top 10 records percentage values.

i am looking a way to write a report like this and appreciate any suggestions.

and also any workaround to solve this excel behaviour.

thanks in advance

This query helps explain what's going on with your query and maybe gives you some ideas on how to address the problem. Before digging in, let me break it down.

First, I need to know the top products for a given day. Because Adventure Works is so sparsely populated, I limit myself to the top 2 products on a given day as determined by Internet Sales Amount. This is the TOPCOUNT() stuff you see all over the place.

To keep my cellset small, I just grab two days. This is the HEAD() stuff. I use NONEMPTY in the HEAD function just to make sure the days I get have sales associated with them.

The GENERATE function in the middle of all this takes a set of days and then does the TOPCOUNT on products for each day in that set. This generates a set of Day-Product combinations. I union this set with another set that is those same days coupled with the ALL Products member of the Product attribute hierarchy. I also union this set with another set that is those same days coupled with a calculated member defined at the top.

The calculated member is the aggregation of the top 2 products for a day. This is different from the ALL Products member that represents all products regardless of whether or not they are in my top 2.

In your Excel results, I believe you are getting the ALL member for your dimension. This would have all members of the dimension regardless of what you are displaying below. Like my calculated member, you need to aggregate just the set of members you are returning on your axis to give you the results you are looking for. Not sure the best approach for your situation, but hopefully this gets you on the right path.

Good luck,
Bryan

thanks Bryan. it really helps a lot