Rows with zero or Null Values

building reports webintelligence
1

Hi,

I am using BOXI R2 with sql server as the backend DB.
I have build a report in which I want to display those rows also which has either zero value or Null value in the Database.
However if there is no data for a particular subject area that row doesn’t show at all.
Can I show this row somehow with zeroes?

Thanks

shukla :india: (BOB member since 2009-08-06)

2

Truely speaking your requirement is not very clear. If the row contains null or zero measure value , you can display them in the report by setting the property of that table.

And if this is not what you are looking for, can you please explain the same in detail?

aniketp :uk: (BOB member since 2007-10-05)

3

First, have you built it out with an outer join?

Second, have you checked to make sure the various “Show when empty” options on the Properties tab in WebI are set appropriately?

Lugh (BOB member since 2009-07-16)

4

Hi Lugh,

Its bulid with an inner join.
Is there a way to show the rows without changing the join…
cause my project is in uat phase…
these tables are used in other reports too…
and someone else has build the universe…
so am not sure what impact it will have on the other reports…
neither i have time to evaluate it… :nonod:
The table properties are checked to show empty rows and zeroes…
It works when there are few values zero and few non zeroes…
whereas when all the values for a row are zero that row doesn’t come up at all.

Thanks

shukla :india: (BOB member since 2009-08-06)

5

Could this be solved with 2 data providers?

One data provider containing just “subject area”, the other containing “subject area” plus all the required measures (columns).

The “subject area” dimensions should be merged automatically (if not, you can do this manually).

Then place “subject area” from data provider 1 into table, and then append a column for each of your measures from data provider 2.

This should see to it that ALL “subject areas” are listed regardless of whether there is data for them or not.

Pete

Peter Hughes :uk: (BOB member since 2005-11-21)

6

Hi

Yes, Peter, your solution will work and I have used it before. However in this new case I am using a Dimension that is created in the report.

To explain more …
I have a call centre environment where I need to calculate Age buckets depending on the age (in seconds) of the call. This is calculated at report time as a dimension.

I then have a measure counting the number of calls that fall into each age bucket.

I need to be able to have a table with ALL age buckets even if the no of calls is 0.

I then also need to chart this in a pie chart.

Any ideas how this could work as the Show when empty seems to have NO effect what so ever. :hb: in fact i can’t see it working at all???

Thanks

SarahG :australia: (BOB member since 2005-08-22)

7

Hi Sarah,

I think the “Show When Empty” refers to the entire block (table or chart). You can’t use “Show When Empty” to get a table or chart to show rows/data where there is no data.

Sorry that’s the only help I can offer though at the moment.

Pete

Peter Hughes :uk: (BOB member since 2005-11-21)

8

Never mind - I eventually got the universe administrator to capitulate and create me a new object to take care of this :slight_smile:
Thanks anyway

SarahG :australia: (BOB member since 2005-08-22)