Question about the DistinctCount function

[MattH]

I am apparently having a mental block with regards to the various
workarounds for the DistinctCount function that have been suggested in this
group. For the sake of comparison, let's say that I'm in the Sales cube of
FoodMart 2000 and I need to know exactly how many different products were
sold in each of the stores for any selected time period. Toward that end, I
create a calculated member called Product Count using
DistinctCount({[Product Name].Members}) .

Using PTS, with only Store Name on the row axis, the Product Count results
for the entire year of 1997 come out perfectly. They also come out correctly
when I restrict it to a single month of that year. But when I want to select
a range of dates, say Q1 to Q2, I only get zeros. This behaves slightly
differently when I filter based on Promotion instead of Time. There, if I
select a single promotion, the numbers are correct, but when I select any
two of the promotion items, I get the total, unfiltered Product Count. I
mention both of these behaviors because in my real-world situation, I will
need to filter the distinctly-counted items by time and another dimension,
such as Promotion.

I also tried creating a new product_id based measure with the Aggregation
Function set to Distinct Count, but as documented, you can't later include
that measure in aggregations. I thought that the approach I followed above
would allow me to later aggregate my distinctly-counted results. I apologize
if I am missing something very basic, but would appreciate any pointers that
any of you might have.

thanks,
matt

[George Spofford]

The DistinctCount() function doesn't respect any calculated members or
VisualTotals() calculations along the way, so it doesn't work with Excel. It
also can't let you get a single dimension out of the tuples.

The only way to get what you want is to write the query yourself, and you can't
use DistinctCount(). Something like

NonEmptyCrossJoin (
{[Product Name].Members},
{[Time].[1997].[Q1], [Time].[1997].[Q2] },
{[Store].CurrentMember},
{[Customer].CurrentMember},
... // other dimensions' current members
,1 // return just the product dimension
).Count

HTH

MattH wrote:

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com
geo...@dsslab.com
ISVs & IT organizations: Find out how DSS Lab can speed your development!

[MattH]

Thanks, George. I hear you loud and clear that the query below is the only
way to do this, but I work in a very small shop and honestly can't see
myself going in and manually changing the variables everytime an end-user
wants a new report. That's why I wanted to use the PTS approach, because I
could create the pivot tables, publish them to an in-house Web server, and
allow my users to change the query filters on the fly. Is there another
approach (or tool) that would allow me to give my end-users the ability to
use such queries without having to change MDX code each time?

thanks again,
matt

"George Spofford" <geo...@dsslab.com> wrote in message
news:3D6E2CC7...@dsslab.com...

[George Spofford]

One virtue of Proclarity and BusinessObjects' BusinessQueryMD for Excel is that
they provide developers with a query object model that allows you to grab
UI-defined selections and formulate your own MDX. If the rest of the UI meets
your requirements, then it may be cheaper for you to buy and deploy one of these
than build your own ASP code to provide selections and MDX building- I think you
can still plug the resulting MDX into the Pivot object in OWC to provide display
and charting capabilities. BusinessQueryMD for Excel would need to be deployed
on desktops, but then OWC components end up on everyone's computers as well.

HTH

[MattH]

Such a solution will probably work very well for me. Thanks for the
pointers. I'll keep reading your book and get my hands on one of those toys
until it all sinks in.

thanks again!

"George Spofford" <geo...@dsslab.com> wrote in message

news:3D6E5039...@dsslab.com...