Displaying distinct/single values from list with repetitive values

27 Views

Hi all,

My BW report displays 'n list of (cProjects) tasks with severity as key figure for each task. I would like to display the total per severity by task, i.e.: Task 1: 2 x Sev 1, 3 x Sev 2, 0 x Sev 3.

My challenge lies in the restrictive way that the query can be displayed. In the query I cannot hide repeated key figures to get a total of the tasks with the respective severity. Reason for this is that the underlying key for my task is unique for all tasks (even though it may be on the same level / phase.). Because of this I get a number of repeats in the report for Task 1, Task 2, Task 3 etc.

Below is a text representation of the current report layout.

Offending Task | Sev 1 | Sev 2 | Sev 3

Task 1 | 1 | |

Task 1 | | | 1

Task 2 | | | 1

Task 2 | | 1 |

Task 3 | | 1 |

And here is what I would like to see.

Offending Task | Sev 1 | Sev 2 | Sev 3

Task 1 | 1 | | 2

Task 2 | | 1 | 2

Task 3 | | 1 |

I have come across a website ([Pearson Software Consulting|http://www.cpearson.com/excel/distinctvalues.aspx]) that shows how to get distinct values in Excel but this method is not supported by Xcelsius.

Does anybody know of a way that I can group my tasks and totals together in Excel to be displayed in Xcelsius? Or maybe a way that I can overcome the repetition in Xcelsius?

Thanks!