BusinessObjects Board

Getting no data in a category to show up as zero.

building reports desktopintelligence
#1

Good day all,

I have two dimensions State and Channel. The measure I am using is target.

For some Channels in States there is no target. What I want is for these to still come through the report (using a break on state) as a value of zero.

Unfortunately because there is no target for the channel the state is truncated. E.g.

StateA ChannelA $100
ChannelB $50
ChannelC $50
StateA Sum $200

StateB ChannelA $50
ChannelC $25
StateB Sum $75

What I want StateB to look like is as follows:

StateB ChannelA $50
ChannelB $0
ChannelC $25
StateB Sum $75

Rampage (BOB member since 2007-11-16)

#2

It’s not possible to display data that doesn’t exist … period. The trick of course is how to “create” the missing data. A few possibilities:

  1. At the universe level, a creative outer join to a table (maybe an alias or derived table) that has all possibilities.
  2. At the query panel level, a union that concatenates all possibilities (with zero amounts for measures) to the “real” answer set.
  3. At the report level, link to a personal data provider (Excel or similar) that has all possibilities … similar to this technique.

Hopefully that will nudge you in the right direction.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)