Forcing Colums to appear

[system]

Have searched for this one but did not find an answer so help appreciated. I have a report that is to be used for forcasting. The output is something like this

LIST PLAN INVC
AAA 1500 1600
BBB 2000 1500
CCC 3000 2500
DDD 1200 2000
EEE 4000 3500

However, my LIST from AAA too EEE may not appear each year for each value and so there would not be any values for PLAN and INVC. So, if the situation for a certain year arose where CCC and EEE had no data then the output in BO would be

LIST PLAN INVC
AAA 1500 1600
BBB 2000 1500
DDD 1200 2000

but in actual fact what I want to see is

LIST PLAN INVC
AAA 1500 1600
BBB 2000 1500
CCC
DDD 1200 2000
EEE

Is there a way to force or hardcode the appearanc of my LIST values from AAA to EEE as there is a known exhausable list of them, so that if there is no data in the db bo can display null values. I have tried setting the “Show rows with empty measure/dimension values” but as not data is present for CCC or EEE in the db this makes no difference. I want to creat is a table that has a fixed LIST whether there is data in the db or not. This may be a long winded explanation, hope it make sense.

---

bencouve (BOB member since 2006-05-19)

[system]

Hi,

This reporter’s FAQ can help:

FAQ: Reporter

How can I include data for all months in a crosstab, even if a particular month has no data? One way to do this is to create a second data provider using only the months. Then link (or “merge”) the second data provider (query) with the first data provider. Use the month column from the second data provider in the cross tab. This should bring in all months even if there were no data for that month. However this technique does not work if you have sections in your report. Dave Rathb…

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks for the response. I had a read however, my report contains sections, is there another way of doing this?

---

bencouve (BOB member since 2006-05-19)

[system]

Hi bencouve,
From the data provided you I could say that use the concept of multiple data providers with merged dimenssions.
Take first data provider with columns LIST,PLAN,INVC .
Then have second data provider with column LIST where it will have all the values in the same column(irrespective of data in 1st DP).
So in this case all values i.e from AAA to EEE(in this case) will display in 2nd DP.

Now you can megre these two DPs with common dim i.e. LIST and then in vertical table you can have rest of the columns like PLAN,INVC.

---

swap_l (BOB member since 2009-04-09)

[system]

Marek and swap_l thanks for your responses as I have got this to work.

---

bencouve (BOB member since 2006-05-19)