Invalid coercion from levels to string

[Carl Goodwin]

Hi - I want to use an expression something like this to add specific numeric values to the last row of a crosstab that I've inserted using Query Calculation.  Year and Quarter are levels in a hierarchy.  The following producing an "invalid coercion from levels to string" error.  What's the best way to solve this?

case
when [Revenue Year] = '2014' and [Revenue Quarter] = 'Q1' then 250
when [Revenue Year] = '2014' and [Revenue Quarter] = 'Q2' then 260
when [Revenue Year] = '2014' and [Revenue Quarter] = 'Q3' then 230
when [Revenue Year] = '2014' and [Revenue Quarter] = 'Q4' then 240
when [Revenue Year] = '2015' and [Revenue Quarter] = 'Q1' then 200
when [Revenue Year] = '2015' and [Revenue Quarter] = 'Q2' then 210
when [Revenue Year] = '2015' and [Revenue Quarter] = 'Q3' then 260
when [Revenue Year] = '2015' and [Revenue Quarter] = 'Q4' then 210
end

[MFGF]

Coercion errors are an attempt by the expression editor to indicate you are using an object in an illegal way. Often the message will give you a good hint. In this case it's telling you that you are trying to treat a level as a string value. One good thing to bear in mind when dealing with dimensional data is that generally it comprises members and sets of members. A level is a variant on a set, and contains a series of members, each of which is identified by a MUN, and each of which is presented as a caption. While the 2014 member may appear to be '2014', in fact it isn't. If you want to use the caption in an expression, you often need to isolate it using a function - either the roleValue() function or the caption() function. The same is true of the quarter members.

Cheers!

MF.

[Carl Goodwin]

Thanks MF.  I tried caption() per below and also roleValue('_memberCaption', ), but then get a "level to value" coercion error as pasted further down:

case
when caption ([Revenue Year]) = '2014' and caption ([Revenue Quarter]) = 'Q1' then 250
when caption ([Revenue Year]) = '2014' and caption ([Revenue Quarter]) = 'Q2' then 260
when caption ([Revenue Year]) = '2014' and caption ([Revenue Quarter]) = 'Q3' then 230
when caption ([Revenue Year]) = '2014' and caption ([Revenue Quarter]) = 'Q4' then 240
when caption ([Revenue Year]) = '2015' and caption ([Revenue Quarter]) = 'Q1' then 200
when caption ([Revenue Year]) = '2015' and caption ([Revenue Quarter]) = 'Q2' then 210
when caption ([Revenue Year]) = '2015' and caption ([Revenue Quarter]) = 'Q3' then 260
when caption ([Revenue Year]) = '2015' and caption ([Revenue Quarter]) = 'Q4' then 210
end


QE-DEF-0478 Invalid coercion from 'level' to 'value' for '[__ns_0].[case when Presentation Layer_Revenue Period_Revenue Year_Revenue Year_Revenue Year = 2014 and Presen_dim].[case when Presentation Layer_Revenue Period_Revenue Year_Revenue Year_Revenue Year = 2014 and Presen_dim].[level]' in 'member([__ns_0].[case when Presentation Layer_Revenue Period_Revenue Year_Revenue Year_Revenue Year = 2014 and Presen_dim].[case when Presentation Layer_Revenue Period_Revenue Year_Revenue Year_Revenue Year = 2014 and Presen_dim].[level],'','Backlog',[__ns_0].[MEASURES])'.

[Lynn]

I'm not sure case logic generally works well in dimensional reporting. What are you trying to achieve?

[Francis aka khayman]

You are not trying to hard code your totals are you?