0

I'm using Business Objects to construct a simple report on whether a unit is on or off for a given day. When constructing a vertical table, the data is correct and looks like such:

Unit ID | Status | Date
1       | On     | 2016-09-10
1       | On     | 2016-09-11
1       | Off    | 2016-09-12
2       | Off    | 2016-09-10
2       | Off    | 2016-09-11
2       | On     | 2016-09-12

However the cross table I've created, with columns of "date" and rows of "Unit ID" is duplicating Unit ID and having an entire row of 'On' followed by an entire row of 'Off' like:

____| 2016-09-10 | 2016-09-11 | 2016-09-12
1   | On         | On         | On
1   | Off        | Off        | Off
2   | On         | On         | On
2   | Off        | Off        | Off

instead of what it should be as:

____| 2016-09-10 | 2016-09-11 | 2016-09-12
1   | On         | On         | Off
2   | Off        | Off        | On

Any suggestions as to why it's doing this?

The table isn't particularly useful if it has these duplicate rows and I can't understand why it's resulting in this odd table.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
D. Stewart
  • 471
  • 4
  • 15
  • Could you update the question with the three objects' definitions? It would be helpful to know what type (dimension, measure) as well as datatype they are and what formula is behind them (if any). – DocZerø Sep 27 '16 at 05:50
  • It depends on what the key is (primary/natural or the pivot) and how you are constructing the query, but the first result looks like a cartesian "cross"/"full outer" join - which you did name it as ("cross table"). Similar scenario here http://stackoverflow.com/q/21845564/16391 – StingyJack Sep 27 '16 at 11:05
  • I've posted an answer as to what happened. The Status field needed to be a measure – D. Stewart Sep 27 '16 at 15:22

1 Answers1

1

Turns out what happened is the "Status" field was a dimension type, but the cross table requires the data field to be a measure type. Simply making a new variable that was a measure equal to "Status" solved the issue.

D. Stewart
  • 471
  • 4
  • 15