How to display all dimension values?

building reports webintelligence
1

Hi experts,
I have webi XI R3. I am using interval dimension. In my report I know theer is data only for 1st, 2nd and 3rd minutes. I am getting these perfectly.
But my user wants to see all the minutes even if there is no data for corresponding minute and display as 0.
as follows:

Minute Count
1 5
2 10
3 50
…

10 0
11 0

Actually theer is no data for 10th and 11th minutes.
Can anybody hepp me out on this?

Kalyani (BOB member since 2007-02-26)

2

Unfortunately Business Objects does not like to display data when a field is empty.

I have tried both in DeskI and WebI to get this to work with little success. There are some work around suggestions when working in DeskI that require a separate data source (like an Excel spreadsheet) which is joined to your Business Objects data. Even that is not 100% reliable.

REB01 :us: (BOB member since 2004-11-29)

3

I have a couple of blog posts that provide some details on how to do this.

The first involves using personal data providers and the other involves universe data with a union technique.

Dave Rathbun :us: (BOB member since 2002-06-06)

4

I typically do this through multiple data providers also - one to gather the list (mintues in this case) and one to get the measurement.

Just to be complete… another option if the data is in 2 table (list in one table and measure in another) the join between the tables could be set to an outer join that will show all minutes regardless of transactions to count.

bzenk (BOB member since 2008-07-30)

5

Fair comment :).

However, the first method would be my preference as it will mean that you will not be using an outerjoin for all queries, against this universe, utilising those two tables, which could lead to performance issues.

Preferably, you wouldn’t have nulls in your DB sttructure, but a meaningful descriptor, such as “None”, or in your case a zero…

Mak 1 :uk: (BOB member since 2005-01-06)

6

Outer joins can have problems with conditions.

Suppose you have a calendar table outer joined to a transaction table. You put a condition on the transaction table to show only orders for a specific product. The outer join behavior depends on the database engine, but in most cases it’s converted to an inner join to avoid ambiguous results. In those cases, it does not solve the problem outlined by the original poster.

Dave Rathbun :us: (BOB member since 2002-06-06)

7

Makes complete sense. I’ll be honest, I’ve managed to avoid outer joins for the last few years.

Using the above “extra” data provider to do the job for me… 8) .

Mak 1 :uk: (BOB member since 2005-01-06)

8

Dave,
At the end of your second blog post you promised a third post with an example where data is missing from more than 1 dimension. Did you ever write and post that? (I haven’t been able to find it.)

I’m facing that exact situation. My data includes 6 dimensions, all of which could have missing data in any given month. There are 22,440 possible combinations of dimension values, so a derived table is out of the question, and universe data doesn’t return all the rows I need. Is my only option to build it in Webi Rich Client and use a spreadsheet for the “missing” data?

Norm Rosen :us: (BOB member since 2002-07-10)

9

No, I don’t think so. I will have to check and see if I even started a draft post that I can easily complete.

Dave Rathbun :us: (BOB member since 2002-06-06)