XQE-PLN-0264 The argument at position 1 of the function 'tuple' is invalid

[harikk]

I am using Cognos 11 and I am dividing two tuples to get a measure. Example, I am trying to assign below calculated expression as Spend per square foot

tuple([StoresOnly],[NonStat_GLAccounts],[Agg_GLAccounts],[Value])/ tuple([StoresOnly],[Stat_GLAccounts],[Agg_StoresOnly],[Value])

Above items are defined as :

[Agg_StoresOnly] - member(aggregate(currentMeasure within set [StoresOnly]))

[Agg_GLAccounts] - member(aggregate(currentMeasure within set [NonStat_GLAccounts]))

[StoresOnly] - filter([children(by Store)], caption([children(by Store)]) < ('109%'))

[NonStat_GLAccounts] - set([Profit_and_Loss].[Profit_and_Loss_Accounts].[Profit_and_Loss_Accounts] -> ?GLAccounts?)

[Stat_GLAccounts] - set([P70100 - Retail Square Footage])

Can you guide me where I did wrong in creating the summarised output for getting cost per sqft of a store?


When I validate the expression, I get below error:


RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0264 The argument at position 1 of the function 'tuple' is invalid in data item. Expected one of the following types : member,measure

[MFGF]

I am using Cognos 11 and I am dividing two tuples to get a measure. Example, I am trying to assign below calculated expression as Spend per square foot

tuple([StoresOnly],[NonStat_GLAccounts],[Agg_GLAccounts],[Value])/ tuple([StoresOnly],[Stat_GLAccounts],[Agg_StoresOnly],[Value])

Above items are defined as :

[Agg_StoresOnly] - member(aggregate(currentMeasure within set [StoresOnly]))

[Agg_GLAccounts] - member(aggregate(currentMeasure within set [NonStat_GLAccounts]))

[StoresOnly] - filter([children(by Store)], caption([children(by Store)]) < ('109%'))

[NonStat_GLAccounts] - set([Profit_and_Loss].[Profit_and_Loss_Accounts].[Profit_and_Loss_Accounts] -> ?GLAccounts?)

[Stat_GLAccounts] - set([P70100 - Retail Square Footage])

Can you guide me where I did wrong in creating the summarised output for getting cost per sqft of a store?


When I validate the expression, I get below error:


RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0264 The argument at position 1 of the function 'tuple' is invalid in data item. Expected one of the following types : member,measure

Hi,

The first argument of your tuple is [StoresOnly], which appears to be a set (of members) derived by a filter() function? You can only use individual members and measures in a tuple, not sets. This appears to be what is causing your error here. You could try wrapping an item() function around the set to bring back a single member - item 0 is the first member in the set.

Cheers!

MF.

[harikk]

Hi MFGF

Thanks for your reply. Shall I wrap item() for all the sets passed into the tuple? All of below items are sets.

[StoresOnly] - filter([children(by Store)], caption([children(by Store)]) < ('109%'))

[NonStat_GLAccounts] - set([Profit_and_Loss].[Profit_and_Loss_Accounts].[Profit_and_Loss_Accounts] -> ?GLAccounts?)

[Stat_GLAccounts] - set([P70100 - Retail Square Footage])

[MFGF]

Hi MFGF

Thanks for your reply. Shall I wrap item() for all the sets passed into the tuple? All of below items are sets.

[StoresOnly] - filter([children(by Store)], caption([children(by Store)]) < ('109%'))

[NonStat_GLAccounts] - set([Profit_and_Loss].[Profit_and_Loss_Accounts].[Profit_and_Loss_Accounts] -> ?GLAccounts?)

[Stat_GLAccounts] - set([P70100 - Retail Square Footage])

The main consideration here is whether there are genuinely multiple members in the sets, or if they resolve to sets of a single member each. If they really are multi-member sets, then the item() function isn't the right solution as it just returns a single member from a set.

It's worth taking a step back and describing what you want to achieve here. It's entirely possible that tuples are not the right approach to take - depending upon what you need to achieve.

Cheers!

MF.

[harikk]

I need to get the cost per sqft for each Nonstat GL Accounts (multiple member set). Nonstat GL Accounts have cost. Stat GL Account (only one) is the sq foot of the store.

So, how do I pass multiple member sets to generate a measure as cost per sqft of not tuple for my scenario?

[MFGF]

I need to get the cost per sqft for each Nonstat GL Accounts (multiple member set). Nonstat GL Accounts have cost. Stat GL Account (only one) is the sq foot of the store.

So, how do I pass multiple member sets to generate a measure as cost per sqft of not tuple for my scenario?

Are you looking for a calculated cost per sq ft for each member in the set, or as a single value for the entire set (and if a single value, is it a total for all members, average, maximum...?)

MF.

[harikk]

Hi MFGF

Thanks again for your invaluable replies..

I am trying to achieve at single member level only for each GL account.. Agg_GLAccounts and Agg_StoresOnly have 1-n relationship.. So, for all sum of each GL per store/Agg_SqftGLAccount which is sqft per store is what I want.. Hope you understand.. If you would prefer to have a google meet that would reduce my stress :)

I am facing error: 'XQE-PLN-0071 The query is not supported. The referenced detail '' for the 'within detail' aggregate 'Data Item1' can not be found on the same edge as a sibling of the given aggregate.'

I am facing above error only when I switch either of them to 'within detail' instead of 'within set'. But I am damn sure that within detail will go at single member level. What do you say on this?? On the other hand when I use within set, I am getting the summirization at set level fine which I don't need.

Tuple is:

tuple([Agg_GLAccounts],[Value])/ tuple([Agg_SqftGLAccount],[Value])

Agg_GLAccounts - aggregate(currentMeasure within detail [NonStat_GLAccounts])

Agg_SqftGLAccount - aggregate(currentMeasure within detail [Stat_GLAccounts])

Agg_StoresOnly - total(currentMeasure within detail [StoresOnly])

Please help

Hari

[MFGF]

Hi MFGF

Thanks again for your invaluable replies..

I am trying to achieve at single member level only for each GL account.. Agg_GLAccounts and Agg_StoresOnly have 1-n relationship.. So, for all sum of each GL per store/Agg_SqftGLAccount which is sqft per store is what I want.. Hope you understand.. If you would prefer to have a google meet that would reduce my stress :)

I am facing error: 'XQE-PLN-0071 The query is not supported. The referenced detail '' for the 'within detail' aggregate 'Data Item1' can not be found on the same edge as a sibling of the given aggregate.'

I am facing above error only when I switch either of them to 'within detail' instead of 'within set'. But I am damn sure that within detail will go at single member level. What do you say on this?? On the other hand when I use within set, I am getting the summirization at set level fine which I don't need.

Tuple is:

tuple([Agg_GLAccounts],[Value])/ tuple([Agg_SqftGLAccount],[Value])

Agg_GLAccounts - aggregate(currentMeasure within detail [NonStat_GLAccounts])

Agg_SqftGLAccount - aggregate(currentMeasure within detail [Stat_GLAccounts])

Agg_StoresOnly - total(currentMeasure within detail [StoresOnly])

Please help

Hari

Hi,

So, in your report, you have a crosstab? What sets or members are displayed in rows and columns? What measures do you have?

From what I can see:
- you have a set of members [StoresOnly] which are your stores
- you have another set of members [NonStat_GLAccounts] which are your GL Accounts
- you have a single member [Stat_GLAccounts] which is square footage description

For each StoresOnly member, you want to see the total of a measure across all NonStat_GLAccounts members in the set, divided by a measure value for the Stat_GLAccounts member? Am I understanding that correctly?

If so, a follow-on question. What measure are you using? I don't see any mention of one here?

Cheers!

MF.

[harikk]

You are absolutely right. My measure is the two tuples which are divided as below which would fetch Spend value of 'a GL account' for each store / sqft foot as below

tuple([Agg_GLAccounts],[Value])/ tuple([Agg_SqftGLAccount],[Value])   (or) value(tuple([Agg_GLAccounts],[Value]))/ value(tuple([Agg_SqftGLAccount],[Value]))

Can't this be the value? I can see the values flooding in crosstab but those are not correctly dividing and are summarizing for the whole lot of set.. Hence, I need to use 'within detail' I suppose.. Which is throwing above error.

So, with 'within set' the output of my crosstab is as below, Mary street is my store which has fixed single sqft value, but cleaning value/ sqft , cleaning additions /sqft is the value I need in each cell here for all periods (in column)

Data Item1                                                        P01           P02           P03           P04           P05           P06           P07            P08            P09           P10           P11          P12
786105 - Cleaning Additions   10001 MARY STREET   0.639   0.822   0.840   0.335   0.710   0.957   0.708   0.509   0.262   0.649   1.042   0.665
786100 - Cleaning                 10001 MARY STREET   0.639   0.822   0.840   0.335   0.710   0.957   0.708   0.509   0.262   0.649   1.042   0.665
787000 - Security Additions   10001 MARY STREET   0.639   0.822   0.840   0.335   0.710   0.957   0.708   0.509   0.262   0.649   1.042   0.665
787100 - Security                  10001 MARY STREET   0.639   0.822   0.840   0.335   0.710   0.957   0.708   0.509   0.262   0.649   1.042   0.665
         

Regards
Hari

[MFGF]

You are absolutely right. My measure is the two tuples which are divided as below which would fetch Spend value of 'a GL account' for each store / sqft foot as below

tuple([Agg_GLAccounts],[Value])/ tuple([Agg_SqftGLAccount],[Value])   (or) value(tuple([Agg_GLAccounts],[Value]))/ value(tuple([Agg_SqftGLAccount],[Value]))

Can't this be the value? I can see the values flooding in crosstab but those are not correctly dividing and are summarizing for the whole lot of set.. Hence, I need to use 'within detail' I suppose.. Which is throwing above error.

So, with 'within set' the output of my crosstab is as below, Mary street is my store which has fixed single sqft value, but cleaning value/ sqft , cleaning additions /sqft is the value I need in each cell here for all periods (in column)

Data Item1                                                        P01           P02           P03           P04           P05           P06           P07            P08            P09           P10           P11          P12
786105 - Cleaning Additions   10001 MARY STREET   0.639   0.822   0.840   0.335   0.710   0.957   0.708   0.509   0.262   0.649   1.042   0.665
786100 - Cleaning                 10001 MARY STREET   0.639   0.822   0.840   0.335   0.710   0.957   0.708   0.509   0.262   0.649   1.042   0.665
787000 - Security Additions   10001 MARY STREET   0.639   0.822   0.840   0.335   0.710   0.957   0.708   0.509   0.262   0.649   1.042   0.665
787100 - Security                  10001 MARY STREET   0.639   0.822   0.840   0.335   0.710   0.957   0.708   0.509   0.262   0.649   1.042   0.665
         

Regards
Hari

Hi,

A tuple is the value of a measure at a specified intersection of members, so you need a measure in the first place to be able to get anywhere. The tuples you included here don't seem to make any sense? What is [Value] - is this a measure?

In your crosstab you have Period members as columns. What are the members in rows? It looks like they are nested - is this [NonStat_GLAccounts] as the outer set and [StoresOnly] as the nested set?

Let's step away from these aggregated expressions for the moment ([Agg_GLAccounts] and [Agg_SqftGLAccount]) and try to describe what the underlying members and measure are, then we'll take it from there.

Apologies, I'm really struggling to follow what you are basing your numbers (measure) on, here.

Cheers!

MF.

[CognosPaul]

Going back to the original post, I think I see what the problem is.

Let's take a look at this part of the expression:

tuple([StoresOnly],[Stat_GLAccounts],[Agg_StoresOnly],[Value])

If we expand the data items we get this:
tuple(
    filter([children(by Store)], caption([children(by Store)]) < ('109%'))
  , set([P70100 - Retail Square Footage])
  , member(aggregate(currentMeasure within set filter([children(by Store)], caption([children(by Store)]) < ('109%'))))
  , [Value]
)

Looking at that, you're calling the store dimension twice, and you have two set expressions. As MF said, tuples only support members and measures.

You really don't need the first item in the tuple, you're already calling that. The second set in there needs to be turned into a member.

Try:
tuple([StoresOnly], member(aggregate(currentMeasure within set [Stat_GLAccounts])),[Agg_StoresOnly],[Value])

Put that in a crosstab, see if that works. If it does, we can move on to the next one:

tuple([StoresOnly],[NonStat_GLAccounts],[Agg_GLAccounts],[Value])/

Again, you're calling a set and GLAccounts twice.

That should look like:
tuple([Agg_StoresOnly],[Agg_GLAccounts],[Value])


Finally we can put the two together:
tuple([Agg_StoresOnly],[Agg_GLAccounts],[Value]) / tuple([StoresOnly], member(aggregate(currentMeasure within set [Stat_GLAccounts])),[Agg_StoresOnly],[Value])