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
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.
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])'.
I'm not sure case logic generally works well in dimensional reporting. What are you trying to achieve?
You are not trying to hard code your totals are you?