×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PowerPlay Transformer

Row count measure for grouped items by Nunina
Posted: 8 Jul 04



In PowerPlay Transformer, is there a way to add a measure to a cube that counts the number of rows for each field, but only the first occurrence of each field, not subsequent fields?

An Impromptu report has been created with a grouping by field, and for each group a '1' is appended as a calculated column. The grand total for the Impromptu report matches perfectly with the database numbers. However, the cube numbers when rolled up are much higher, especially for dimensions with a large number of categories.


Solution:

Impromptu grouping is not carried over into Transformer, so the calculation will be for all categories, inflating the rolled up measures seen in PowerPlay. Use the following technique to work around this problem:

1. In Impromptu, group by the field to be counted.
2. Add a running-count column for each grouping, so that the first occurrence is 1, the second 2, etc. When the next unique value for the field occurs, the count is restarted.
3. Add another calculated column that adds a 1 for the minimum value in the grouping, zeroing out all the others. This way Impromptu is only counting the first occurrence within the group and inserting a zero for all others.
4. Bring this into Transformer using the latter column as the count measure with the Default(Sum) roll up.


Back to Cognos solutions FAQ Index
Back to Cognos solutions Forum


My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close