I have a straight table in my application with a drill down group dimension. I have mapped in target values using a mapping load to my data set based on each of the dimensions in the group. I then have a variable that grabs the target value associated with whatever dimension is drilled in to. However, during the mapping load the Total targets don't come in since there is no "Total" in my dimension. I still need to display the total target on the total line so I am trying to use an IF statement to bring in the static total target if the row is 0 but I'm getting an error.
The variable works fine on its own in the expression but once I add the "If" it errors out saying its expecting a ')'. On the flip side if I use the If without the variable it also works fine. What am I missing??
The If I'm using is: =If(RowNo()=0, .87, $(vChgTarg_Org))
=If(GetSelectedCount(OrgUnit1) >0 and GetSelectedCount(OrgUnit2)>0, ChgOrg3, If(GetSelectedCount(OrgUnit1)>0,ChgOrg2, If(GetSelectedCount(OrgUnit1)=0,ChgOrg1,'')))
My dimension group contains OrgUnit1, OrgUnit2, OrgUnit3, OrgUnit4 and so on, up to OrgUnit10. the ChgOrg# is the target associated with that level in the dimension. So ChgOrg3 will match up with OrgUnit3, ChgOrg2 will match up with OrgUnit2, etc.
This variable works fine in the expression but I obviously get a blank/Null on the total row. I want .87 to come in on the total row, but the If(RowNo expression doesn't seem to be working.
It will show the text of the expression of the variable in the straight table if I remove the dollar expansion from the variable in the straight table's expression or if I remove the leading = from the variable definition. Otherwise the whole straight table shows blank with that error message.
I added a sample QVW to the original message. The column "Chg Tgt" is where I'm using the expression. the table on the left shows that column working when I use just the variable and the column on the right shows it not working when I add the If(RowNo statement. I need to get .87 into the total line