Need to convert Prior() function in Brio to Cognos framework manager

[neelimakoppu]

Hi All,

I'm working on a brio query migrating it to cognos. In that I came across a calculated column (Unique Dealer), where it is using "Prior(columnname)" function. Can anyone let me know what is the equivalent function of Prior in framework manager? Thanks in advance.

[Prerna Chaudhary]

Hi,

You may try using Lag functions in Cognos.

It returns the sibling member that is a specified number of positions prior to a specified member.

You can find it in under common functions in Cognos Framework Manager.

Also I have attached a snapshot for your reference.

Hope that helps.

Regards,
Prerna Chaudhary

[neelimakoppu]

Thanks Prema Chaudary..

I have tried using Lag function. But I'm getting an error as below.

Formula I wrote for Unique Dealer is

" lag (([Logical layer].[Dealer].[Unique Key]  ),1) "

Error Message:
QE-DEF-0478 Invalid coercion from 'level' to 'member' for '[__ns_0].[Unique Key_dim].[Unique Key_dim].[Unique Key]' in 'lag([__ns_0].[Unique Key_dim].[Unique Key_dim].[Unique Key],1)'.

So I have change Unique key and Unique dealer usage properties to fact.  Even though I'm getting the below error.

QE-DEF-0478 Invalid coercion from 'measure' to 'member' for '[__ns_0].[MEASURES].[Unique Key]' in 'lag([__ns_0].[MEASURES].[Unique Key],1)'.
Can any one please tell me why this error comes and how to resolve it. thanks.

[bus_pass_man]

Your first attempt is failing because of an invalid coercion from 'level' to 'member' (although levels have 4-part names so the 3-part name suggests you're using a query item or attribute).  The second is failing because of an invalid coercion from 'measure' to 'member'.  No matter what, the key thing to realize is that you're using the wrong thing in your expression as lag takes a member as its first parameter. Levels and measures are not members.

I couldn't find much documentation of the prior function so I can't point you to something which is directly swapable. Your original request indicates that prior takes a column (query item) as a parameter.  The only documentation I could find says "Returns the prior row value of the referenced item".  Presumably the referenced item is the column.  How the function knows what the prior row value is isn't quite a clear as I would like.

You, presumably, have a good understanding about the prior function and what you are attempting to achieve in the Brio application.  With that, you may be able to find some function or combination of functions.  You may need to re-engineer stuff, though.

To achieve that you will need, in addition to needing to know what your original application was trying to achieve, to have a clearer understanding about what is available to you.

Lag is a dimension function.  It is a member of a set of functions which are called member relative functions.  One major use of the function is to find prior members of a designated member. In DMR, because there is no such thing as relative time, you need to fake out current members by doing stuff like filtering your dimension so that the member which you want to be the current member is the last one generated.  For stuff like dynamic cubes etc., where there is such a concept you can feed in the current period member and you're off to the races.
                                 
Just as a observation, responding to an error complaining about invalid coercion from 'level' to 'member' by changing the usage of the query item suggests a fairly sub-optimal troubleshooting strategy, not the least of which is a somewhat less-than-optimal use of the little crumbs of information that the usual Cognos error message does deem to drop on us peons.