Distinct Count Problem

  • I have a system where I need to quickly count individuals based on many criteria. I build cubes with the person's ID and flags for each of the relevant dimensions. This is pretty much my primary use of multidimensional analysis. I am using SQL Server 2000 (basic edition?), Service Pack 2.

    However, the IDs are CHAR-based (alphanumeric) and "DISTINCT COUNT" is not given as an option when I am building the count measure. I have noticed that if the ID has a numeric data type (for example, an INTEGER autonumbered field), then the "DISTINCT COUNT" option is available. This baffles me, as I would think one would be *more* likely to want to use a distinct count on textual data rather than a numeric measure.

    Does anyone here have any insights into this problem?

    Thanks,

    Matthew

  • This drives me crazy as well.

    To use DISTINCT COUNT the column must have a numeric data type. I don't understand why, I just know it is the only way it works so I convert my IDs/Account #'s, etc. when I want to use it.

  • Okay, additional research on this topic and your response leads me to the answer "that's just the way it is."

    Oh, well. One of the must useful aggregation functions in Analysis Services and it's crippled...

    Thanks,

    Matthew

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply