Hi!

I have a dataset like the one listed below. As you can see there is one
acount number, which has 3 customers connected to it (103,113 and 172). This
dataset is based on a cube (using SSAS) and presented in a report (SSRS). In
the report I want to sum the distinct itemcount. In other words I would like
to sum 50+27+22 and present that sum in the report. As you can see the
itemcount is grouped by Account and CustNum. How di I do this? I would prefer
to do this in the cube.

The field Amount should be added as normal (0+1100+0+594+0+0).

My dataset:

Account CustNum ItemCount Amount
81016000 103 50 0
81016000 103 50 1100
81016000 113 27 0
81016000 113 27 594
81016000 172 22 0
81016000 172 22 0


I am using the sql server 2005 platform.

I don't have many experience with 2005 AS. But you could filter out the
duplicate Item Count in a view like:
select Account, CustNum, Max(ItemCount)
from your table
group by Account, CustNum

Then build your measure of ItemCount using the view as your fact table.

"Billy" wrote:

It's could be that you have a granularity issue here. Can you store the
ItemCount and Amount fields in separate fact tables?

eg.

Account CustNum ItemCount
81016000 103 50
81016000 113 27
81016000 172 22

Account CustNum Amount
81016000 103 0
81016000 103 1100
81016000 113 0
81016000 113 594
81016000 172 0
81016000 172 0


This way AS 2005 will sum ItemCount and Amount correctly.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <9ADEA4BA-710F-4196-9DD1-7A96D539EC38 (AT) microsoft (DOT) com>,
Billy (AT) discussions (DOT) microsoft.com says...