String Measures

[ravws]

Hi All,

Does anyone know an easy way to return a string value as a measure?

I created a dimension from the string values as a work around, but this seems unnecessary (and impeded performance) as I simply want the user to to view the string values when the measure count = 1.

Creating a calculated member from the string dimension currentmemeber.name also works, but returns empty cells as well. I tried incarnations of NON EMPTY and IFF(ISEMPTY(),<<empty expression>>,<string>) in the calculated member all with syntax errors.

Thanks in advance,

RA

 

[MDXer]

you have to do it from a dimension. In analysis services you can only aggregate values and you can't sum text. I do have on another machine a mdx statement that will assign dimension names to a measure, but it is on a different machine so I'll look for it and post it later. if you could give an example using foodmart it would be a great help in seeing what your trying to do.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ravws]

A brief description of the fact table I'm using...It contains measurement values, with measurement site and time as PKs. Each measurement value is associated with multiple Flags (basically text comment fields). We don't make the flags dimensions (although we could) because they contain attributes of each fact value. My dilema... I want to display the flags for any site,time pair when site,time are at their lowest level, or the measure count = 1.

FoodMart could be used as an example. From HR cube, I made a new calculated member [My employee] = employees.currentmember.datamember

Now filter the time dimension down the lowest level, e.g. Time.1997.Q1.1 and filter the position dimension to Position.[senior management].president

In the cases where mesures.count = 1 measures.[My employee]contains one name so I would like measures.[My employee] to return the name (not [All employees]). If measures.count > 1 return NULL or some filtered expression, if measures.count =<<empty>> don't return anything.

You may be wondering why I don't just query the fact table with TSQL... Well, that would be easier, but I'd like to keep the look of the flat table (numeric + text) at the lowest drilldown level, AND be able to aggreagete numeric values (and create custom text) across higher dimension levels.

Thanks Again!!