Using Comparison Operators with sets and members

[MrTy]

According to COGNOS documentation, one should be able to use these operators: =, <>, in, and not in. For example: currentMember([Period].[Years].[Years]) in [ThisYearPeriodsToDate] and have it return TRUE if the currentMember is in the set [ThisYearPeriodsToDate].

I am trying the following:

tuple([USD],if (currentMember([PLSummTM1].[Periods].[Periods]) not in firstSibling([ThisCurrentPeriod]) ) THEN ([Budgeted]) ELSE ([Actual]),[Forecast],[[REVENUE]]])

The desired result is if the current member of the periods hierarchy is in the firstSibling of the current fiscal period, then return Actual else return Budgeted.

In context: If Jan not in firstSibling(Apr) then Budgeted else Actual. firstSibling(Apr) should evaluate to Jan and return Actual.

I get a parsing error on this at the firstSibling function on its first bracket.

QE-DEF-0260 Parsing error before or near position: 94 of: "tuple([USD (Current)], if (currentMember([PLSummTM1].[Periods].[Periods]) not in firstSibling"
QE-DEF-0261 QFWP - Parsing text: tuple([USD (Current)], if (currentMember([PLSummTM1].[Periods].[Periods]) not in firstSibling([ThisCurrentPeriod]) ) THEN ([Budgeted]) ELSE ([Actual]),[Forecast],[[REVENUE]]])


My questions are what am I missing here in the parsing and is this a valid construct or is COGNOS documentation slack as usual?

Thanks for any help.

 

[MrTy]

Okay, the parsing problem is resolved! Needed parathesis around the subject of the "in" operand.

Now, however, I get no results, just "--" in the crosstab. Also, the validate on the dataitem itself does not like the if statement. Complains it is looking for a member and the parameter being returned is numeric.

Ty

 

[MrTy]

New update. It is now returning results, but only Budgeted for all rows, regardless of the currentMember([PLSummTM1].[Periods].[Periods]) member. (I had removed currentMember() from the dataitem after my first post in trying different things)

If I swap Actual for Budget in the THEN...ELSE... clauses, I then get Actual reported on all rows.

It seems the IF () clause is always evaluating to TRUE.

Any ideas?

 

[MrTy]

Well, I figured it out. The "in" and "not in" operators do not apparently work or I'm still missing something in their syntax. If anyone has some insight on this, I would appreciate it.

 

[blom0344]

I am no expert with MDX, but I believe -> is used instead of 'in' in many cases. It is also listed as an operator in the function list within Report Studio..

Ties Blom

 

[MrTy]

Thanks, Blom for the response, but I talking about comparison operators. The users guide covers this in the dimension coercion section, but to be fair does not really provide any good examples.

What I'm trying to do is this:

The columns are the twelve months of the fiscal year starting with Jul.

The first row is a calculation that is supposed to return actuals for the Jul column and forecasted numbers for Aug - Jun.

The second row should return actuals for Jul-Aug and forecasted for Sep - Jun.

And so forth.

I have been totally unable to get this to work and I've tried dozens of combinations of things.