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
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.
Thanks that worked but it duplicated my data lines, one for text one for float. See attached!
Set aggregation properties on the data items (ie: sum for the numeric measures and maximum for the non numeric)
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?
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.