Text Measure Query Calculation

[fkli222]

Trying to put in a query calculation that returns a text measure in my report but it doesn't work. I have many other float measures which work fine but with the text I get a SQL 28 error. See the query calculation for my text measure below. If I bring in text measure into a report by itself and filter Salary Header to 'Level' it does work but not in a query calc for some reason.

if([People Model].[Salary Header].[Salary Header]='Level')
then([People Model].[Measures].[Salary Text Measure])
else(0)

Anyone have an idea?

Thanks!

Frank

[tjohnson3050]

I think your statement is trying to return two different data types.  The text measure I assume is a text value.  Try either casting the salary text measure to a numeric data type, or putting the zero in single quotes.

[fkli222]

Thanks that worked but it duplicated my data lines, one for text one for float. See attached!

[tjohnson3050]

Set aggregation properties on the data items (ie: sum for the numeric measures and maximum for the non numeric)

[fkli222]

Thanks tjohnson! I normally just use automatic but don't usually combine text with numeric.

One last quick question, right now I have the report in a list but when converting to a crosstab non-numeric values do not show. Any idea?

[tjohnson3050]

The intersection cells on a crosstab are designed to handle numeric measures.  If you want to show a text measure, it's best to put that on the rows, not in an intersection cell.