Dynamic filter cognos BI/TM1

[vinimantoan]

Hi,

I´m using cognos Bi with an TM1 source.

I´m trying to filter some indicators in my report.
I create some attributes in TM1 that should help me.

The parameter value, should be the string to filter my indicators.
But I cannot get it.

My sintax works well when static:
#[Bsc Indicadores].[Indicadores].[Objetivo].['+'Direx'+'] # = 'Direx'

I need to replace both "DIREX" by a dinamic command.

I tried to use
#[Bsc Indicadores].[Indicadores].[Objetivo].['+Prompt('Direcao')+'] # = Prompt('Direcao')
#[Bsc Indicadores].[Indicadores].[Objetivo].['+?Direcao?+'] # = ?Direcao?
#[Bsc Indicadores].[Indicadores].[Objetivo].['+caption(direcao)+'] # = caption(direcao)

And many other, but none is working.

Any tip will be helpful.

TIA
Vinícius Mantoan

[MFGF]

Hi,

I´m using cognos Bi with an TM1 source.

I´m trying to filter some indicators in my report.
I create some attributes in TM1 that should help me.

The parameter value, should be the string to filter my indicators.
But I cannot get it.

My sintax works well when static:
#[Bsc Indicadores].[Indicadores].[Objetivo].['+'Direx'+'] # = 'Direx'

I need to replace both "DIREX" by a dinamic command.

I tried to use
#[Bsc Indicadores].[Indicadores].[Objetivo].['+Prompt('Direcao')+'] # = Prompt('Direcao')
#[Bsc Indicadores].[Indicadores].[Objetivo].['+?Direcao?+'] # = ?Direcao?
#[Bsc Indicadores].[Indicadores].[Objetivo].['+caption(direcao)+'] # = caption(direcao)

And many other, but none is working.

Any tip will be helpful.

TIA
Vinícius Mantoan

Hi,

Can you explain what you're trying to achieve? The "static" syntax you are showing here - where are you using it? In a detail filter? Do you need separate prompts for each attribute? How is this supposed to work?

MF.

[vinimantoan]

Hi MF,

Yes, I am using these syntax in a detail filter.
Direcao parameter is my menu filter in a previous page.

It should filter in my detail filter as I choose a new direcao.

If choose Direx, my detail filter shoud be like #[Bsc Indicadores].[Indicadores].[Objetivo].['+'Direx'+'] # = 'Direx'
If choose DirD my detail filter shoud be like #[Bsc Indicadores].[Indicadores].[Objetivo].['+'DirD'+'] # = 'DirD'
If choose DirRH my detail filter shoud be like #[Bsc Indicadores].[Indicadores].[Objetivo].['+'DirRH'+'] # = 'DirRH'

and it goes on.

thanks
Vinicius Mantoan

[CognosPaul]

You've almost got the filter correct, but the thing to remember is that without supplying a datatype, the prompt function will default to string. This means that:

#'[Bsc Indicadores].[Indicadores].[Objetivo].['+Prompt('Direcao')+']'# = #Prompt('Direcao')# will turn into:

[Bsc Indicadores].[Indicadores].[Objetivo].['Direcao'] = 'Direcao'

Notice the quotes around Direcao. Instead, you can specify the datatype, "token", and it won't wrap the selection in quotes.

#'[Bsc Indicadores].[Indicadores].[Objetivo].['+Prompt('Direcao','token')+']'# = #sq(Prompt('Direcao','token'))# will turn into:
[Bsc Indicadores].[Indicadores].[Objetivo].[Direcao] = 'Direcao'


That being said, if the source is dimensional, you should not being using detail filters. Is Objetivo being used in your report? What is it accomplishing?

[vinimantoan]

Hi,

I tried this syntax, but cognos brings me the full name of the member, not only the caption.

Query:
#'[(pt) BSC - Indicadores].[Indicadores].[Indicadores].[Objectivo].['+ Prompt('P_Direcao','token') +']'# = #sq(prompt ('P_Direcao','token'))#

Error:
V5 syntax error found in expression "[(pt) BSC - Indicadores].[Indicadores].[Indicadores].[Objectivo].[[BSC - Indicadores].[Direcção].[Direcção].[Direcções]->:[TM].[Direcção].[Direcção].[@MEMBER].[DirEx]] = '[BSC - Indicadores].[Direcção].[Direcção].[Direcções]->:[TM].[Direcção].[Direcção].[@MEMBER].[DirEx]'", invalid token "->" found after "[(pt) BSC - Indicadores].[Indicadores].[Indicadores].[Objectivo].[[BSC - Indicadores].[Direcção].[Direcção].[Direcções]->:[TM].[Direcção].[Direcção].[@MEMBER].[DirEx]] = '[BSC - Indicadores].[Direcção].[Direcção].[Direcções]".


Objetivo is one of my columns in the report.
It has children, and the children may belong to one or more objetivos.
So I have to filter them individually.

[vinimantoan]

Hi.

I reached the expression that I need.
It is something like this:

'[(pt) BSC - Indicadores].[Indicadores].[Indicadores].[Objectivo].['+  cast( roleValue ( '_businessKey'; [Direcção]  ); VARCHAR(6) )+']' = cast( roleValue ( '_businessKey'; [Direcção]  ); VARCHAR(6))

It concat the string as I want to.

But when I set in my detail filter, it does not show anything.

I got the result of the first part of the query, and set directly in my detail filter, it works, but using dynamic (only the first part) does not show any.

Ideas?

[MFGF]

Hi.

I reached the expression that I need.
It is something like this:

'[(pt) BSC - Indicadores].[Indicadores].[Indicadores].[Objectivo].['+  cast( roleValue ( '_businessKey'; [Direcção]  ); VARCHAR(6) )+']' = cast( roleValue ( '_businessKey'; [Direcção]  ); VARCHAR(6))

It concat the string as I want to.

But when I set in my detail filter, it does not show anything.

I got the result of the first part of the query, and set directly in my detail filter, it works, but using dynamic (only the first part) does not show any.

Ideas?

Paul wrote in his post above:

...if the source is dimensional, you should not being using detail filters...

That's a fundamental, important, critical thing you should consider. You are using a TM1 cube, so your source is dimensional. Detail filters are *absolutely* the wrong approach to use here. At best, they might give you correct results under certain circumstances. Usually, though, they don't deliver the results you require if they work at all. I can't stress stongly enough that you should not use detail filters with OLAP sources.

We really need to know what your objectives are to be able to advise on how to achieve the result you require. Is Objectivo being used in your report as an item being displayed? Is it not displayed but needs to filter the meaure values? What are you trying to accomplish?

Cheers!

MF.

[vinimantoan]

Hi MF.

Let me try to explain to you my report.

I have the following dimension


Dimension      SecurityGroup
Objetivo_1      DirEx
   Indicador_1   DirEx, DirD
   Indicador_2   DirEx
Objetivo_2      DirD
   Indicador_1   DirEx, DirD
   Indicador_3   DirD

My user Usr_A has SecurityGroup DirEx and DirD.

My menu page has:

DirEx
DirD

My report has:

Objetivos | Indicadores| Values


If I click DirEx, i need to see only DirEx members, and if I click DirD I should see only DirD.

It work most of the times, but my user_A who belongs to two security groups see:

Objetivos    Indicadores| Values
Objetivo_1   Indicador_1
Objetivo_1   Indicador_2
Objetivo_2   Indicador_1

Indicador_1 haw two fathers, and how User_a has access for both groups he sees indicador_1 for both objetivo.

Could you get it?

[MFGF]

Hi MF.

Let me try to explain to you my report.

I have the following dimension


Dimension      SecurityGroup
Objetivo_1      DirEx
   Indicador_1   DirEx, DirD
   Indicador_2   DirEx
Objetivo_2      DirD
   Indicador_1   DirEx, DirD
   Indicador_3   DirD

My user Usr_A has SecurityGroup DirEx and DirD.

My menu page has:

DirEx
DirD

My report has:

Objetivos | Indicadores| Values


If I click DirEx, i need to see only DirEx members, and if I click DirD I should see only DirD.

It work most of the times, but my user_A who belongs to two security groups see:

Objetivos    Indicadores| Values
Objetivo_1   Indicador_1
Objetivo_1   Indicador_2
Objetivo_2   Indicador_1

Indicador_1 haw two fathers, and how User_a has access for both groups he sees indicador_1 for both objetivo.

Could you get it?

Hi,

Can you tell us what types of objects Objetivo, Indicador and SecurityGroup are? Are they levels? Is SecurityGroup an attribute of the members on the Indicator level? Can you advise what these structures are in your cube?

If I'm understanding, Objetivo and Indicador are two levels in the same hierarchy? What about SecurityGroup? Are DirEx and DirD members? captions? attributes?

MF.

[vinimantoan]

Yes,
Objetivo and level are levels of the same hierarchy
Security group are the groups for acess itself. Created in TM1. The user belong to two.
Direx and Dird are members of a different dimensions. It has the same name as the groups created in tm1 to host the users.

[MFGF]

Objetivo and level are levels of the same hierarchy

Ok - great!

Security group are the groups for acess itself. Created in TM1. The user belong to two.

How does this relate to data in your cube? I'm not understanding this piece.

Direx and Dird are members of a different dimensions. It has the same name as the groups created in tm1 to host the users.

Ok. So how do we know which Indicador members are DirEx or DirD? Unless this information is held for the Indicador members, where is the link? It sounds like you want to retrieve only Indicador members for DirEx (for example) but if DirEx is a member of an entirely different dimension, how can we identify the Indicador members you require?

Is there an attribute for the Indicador members that holds this information?

MF.

[vinimantoan]

About the groups:

When you set the security in TM1, you create users, and groups! Right?
Then you set each user the group it belongs.
Then you set your members security to each group. Right?

Thats the group i am talking. The security group from TM1. The roles of access.

About indicators:
Yes, I created attibutes in the dimension.
It has an attribute for each direcao (DirEx, DirD). And if it is filled up, it belongs to it.

But I can change it if we run over a new idea.

[MFGF]

About the groups:

When you set the security in TM1, you create users, and groups! Right?
Then you set each user the group it belongs.
Then you set your members security to each group. Right?

Thats the group i am talking. The security group from TM1. The roles of access.

Understood - I don't see how you can filter in your report for this though? I'm assuming a user's group will control which members TM1 permits them access to? Wouldn't that mean two users running the same unfiltered report would see different results? How do the groups apply to what you are trying to do here?

About indicators:
Yes, I created attibutes in the dimension.
It has an attribute for each direcao (DirEx, DirD). And if it is filled up, it belongs to it.

But I can change it if we run over a new idea.

If you have attributes, you can probably reference these in a filter() function for the Indicador members

Instead of bringing in the Indicador level (for example) to your crosstab, bring in a query calculation that uses a filter() function on the Indicador level

eg

filter( [your Indicador level], [DirEx attribute] <> '')

If you had a single SecurityGroup attribute that contained a value of either DirEx or DirD, it might be easier - then you could create the expression as

filter( [your Indicador level], [your SecurityGroup attribute] = 'DirEx')

If you wanted a prompt for the value, you could set up a value prompt with two static choices - DirEx and DirD - and use a parameter instead of the literal value.

Does this get you any closer to what you need?

MF.

[vinimantoan]

Unfortunately I have one attribute for each.
Because I can have more that um Dir for each indicador.

So if I use the following expression:

filter( [your Indicador level], [DirEx attribute] <> '')

is possible to use it dynamic?

because if I choose Direx in my previous page, it should be:
filter( [your Indicador level], [DirEx attribute] <> '')

If I choose DirD, will be:
filter( [your Indicador level], [DirD attribute] <> '')

If it´s possible, will probably solve my problem.

Thanks again.

[vinimantoan]

Sorry,

I tried this static filter:

filter ( [Objectivo] ; [DirEx] = 'DirEx' )

And it works fine.

But I am having the same problem as before.
[DirEx] needs to be dynamic, depending on my parameter.
But none of my previous formulas make it happens.

Any idea to change it as my parameter receive differents values?

Thanks!

[MFGF]

Sorry,

I tried this static filter:

filter ( [Objectivo] ; [DirEx] = 'DirEx' )

And it works fine.

But I am having the same problem as before.
[DirEx] needs to be dynamic, depending on my parameter.
But none of my previous formulas make it happens.

Any idea to change it as my parameter receive differents values?

Thanks!

We need to know the path to your attributes in the cube to be able to do this. Can you post an example up for us? Expand your package tree, expand your Indicador level, and right-click on the DirEx attribute and select "Properties". Copy the 'Ref' entry from the Properties page and post it up for us.

We can then (hopefully) tell you how to use a macro to construct this with the relevant closing part and a prompt() function.

MF.

[vinimantoan]

Hi,

Case solved.
I change my parameter configuration and created in TM1 one single columns with my information.
Seems to be working fine now.

Thanks a lot.