filtering distinct values for leaf level elements.

[cognos4321]

Hello experts,

I have developed a simple crosstab report where on the row side I had to put an expression to filter the leaf levels in the 'Accounts' Dimension.

The expression is:
filter( members ([Billing].[Account].[Account] ),count(1 within set children(currentMember([Billing].[Account].[Account] )  )  ) = 0 )

Now, as expected it is giving me several duplicate values of account numbers since the same account number could fall under many parent levels in the dimension.
Please help in filtering the distinct values in the above expression.

I am using cognos 10.2 and the datasource is TM1 cubes.


Thanks a lot.

[cognos4321]

Please let me know if I need to provide any further explanation to make you all understand my problem here.

Still awaiting a response .

Thanks in advance

[bdbits]

This is not a help desk. Please see item #2 at http://www.cognoise.com/index.php/topic,24030.0.html.

[cognos4321]

Hi bdbits,

I apologize, just got a little restless thinking my question might not be clear or if someone needs more explanation on the issue.

I would wait without bothering anyone untill hear back from some expert on how do we filter distinct values for leaf levels in a dimension.


Sorry!

[MFGF]

Hello experts,

I have developed a simple crosstab report where on the row side I had to put an expression to filter the leaf levels in the 'Accounts' Dimension.

The expression is:
filter( members ([Billing].[Account].[Account] ),count(1 within set children(currentMember([Billing].[Account].[Account] )  )  ) = 0 )

Now, as expected it is giving me several duplicate values of account numbers since the same account number could fall under many parent levels in the dimension.
Please help in filtering the distinct values in the above expression.

I am using cognos 10.2 and the datasource is TM1 cubes.


Thanks a lot.

Hi,

The major problem you have here is that you are not currently getting duplicate account members being returned. Although the members might have the same caption (the account number), behind the scenes, they are physically different members with different Member Unique Names (MUNs). You can see this if you expand the member tree in your package view, right-click on a member and select "Properties". When you are reporting over an OLAP source such as TM1, you're not dealing with values, you're dealing with members (and levels and hierarchies and attributes). Every member has a unique MUN. If your result set comprises five members, you will see all five members, even if they have the same caption. You're going to need to figure out how to adapt your expression so it returns only the members you require here...

Cheers!

MF.

[cognos4321]

Thanks a lot MFGF for the reply.


Yes I can understand that the MUN for accounts having same name is different because they are getting pulled from a differenr parent levels under the same dimension.

Actually, it's a crosstab report with Accounts on the rows, Year and Unit on the columns.
It has a prompt only for the Unit and the Year
The business doesn't need any prompt for the Accounts dimension but wants all the leaf level accounts to be there.

The only option I had to filter the leaf levels from the Accounts Dimension was to add this expression to a data item and drag it over the rows pane
The expression is:
filter( members ([Billing].[Account].[Account] ),count(1 within set children(currentMember([Billing].[Account].[Account] )  )  ) = 0 )


Now, this expression did gave all the leaf levels in the entire dimension but as mentioned earlier I am getting several leaf level account numbers repeated in the result (different MUN's ofcourse)
I tried several expressions where I can make it distinct but all of them gave me errors and now I undersatand why that happened.


MFGF, can you please suggest how can I develop this report without an Account prompt.


Thanks again!

[bdbits]

Looking at it now cognos4321, I sounded a bit cranky, sorry. :o

One consideration might be to split your hierarchy into multiple dims. For example, an account dim and a customer dim. Or at least an additional ungrouped customer dim, if that is a frequent reporting need. And actually, it sounds like your users think of them as two separate things.

Note that splitting things in two like this does not eliminate any implied relationships. Thinking along relational lines, it would be as if you currently had a table with both account and customer information and could only access the table by a single surrogate key. If you split them, it will be like having two separate tables. And in your fact table, you will have two foreign keys instead of just the one foreign key.

Hope that helps rather than muddies the water.

[cognos4321]

Thanks for the suggestion , bdbits.

Actually, I can't modify the cube since it is the source for other reports too.
It's just that in this particular report the user doesn't need the Accounts prompt but rather want all the distinct leaf levels to appear on the rows of the crosstab.


Thank You.