keep empty column in a crosstab

[system]

Hello,

I have a cross tab that looks like this

         Jan  Feb  Mar
Ferrari   12   20
Porche    08   15
Cadillac  07   32   5

I want to apply a filter, to keep Ferrari only in my cross tab, but i would like to preserv the march column (wich would be left empty or set to 0), like this :

         Jan  Feb  Mar
Ferrari   12   20

But what i get instead is just a cross tab with two columns only, March is gone. How could i avoid this ?

---

tiberghv (BOB member since 2009-02-04)

[system]

Hi,

This Reporter FAQ will 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]

Thank you very much, but this solution doesn’t seem to work.

I have created a second data provider with just the month in the query (i can check that this query returns all the month values). Then the month objects from my two queries are linked, i replace the month object of my cross tab with the month object from my new query. But the cross tab still only displays the month that have data for the selected (filtered) values.

In my example, if i filter on Ferrari, the March month is still gone.

Note: i have no sections in my report.

---

tiberghv (BOB member since 2009-02-04)

[system]

Does the “forcing of months” work if you do not apply a filter?

In my work, I’ve always found creating a few dummy values (just selecting 0 from the database) and unioning the query to a version where all of the metrics were swapped out for the dummy values, always seemed to be the least amount of work.

---

digpen (BOB member since 2002-08-15)

[system]

@digpen

Does the “forcing of months” work if you do not apply a filter?

Yes, it works as long as i don’t apply a local filter on the crosstab. But that doesn’t solve my problem.

---

tiberghv (BOB member since 2009-02-04)

[system]

And… what if you wrap a NoFilter() around the month from your second data provider?

Otherwise… a unioned set eliminates quite a bit of the headache involved with multiple data providers just to “invent” data intersects. Business Objects just manipulates what the database returns, so to force the rows, I’ve always made them “exist” in the query.

---

digpen (BOB member since 2002-08-15)

[system]

@digpen

And… what if you wrap a NoFilter() around the month from your second data provider?

Otherwise… a unioned set eliminates quite a bit of the headache involved with multiple data providers just to “invent” data intersects. Business Objects just manipulates what the database returns, so to force the rows, I’ve always made them “exist” in the query.

NoFitler doesn’t work.

I don’t really understand your other solution.
Where to you put the dummy (0) values ? inside the database ? or do you generate them in the deski query ? With this method, do you need to hardcode the values of the months that will be used in the crosstab, or can it stays dynamic ?

---

tiberghv (BOB member since 2009-02-04)

[system]

Dummy objects would be created at the universe level and in the query panel, when you create your UNION query, you’d swap them out for all of your measure objects.

They would be “dynamic” because they would return the same months that the original query returned. If you want future months, you’d be better off creating a calendar table and using that.

---

digpen (BOB member since 2002-08-15)

[system]

i am really sorry i don’t understand.
Could you give an example with the crosstab example i gave ?

i already have three objects: car, month and sales. What object do i add in the universe ?

---

tiberghv (BOB member since 2009-02-04)

[system]

You would add a dummy value to the universe with the select statement as:

SUM( 0 )

So… your potential new universe query would look like:
select car, mont, sum( sales ) as sales from table group by car, month
UNION
select car, mont, sum( 0 ) as sales from table group by car, month

---

digpen (BOB member since 2002-08-15)

[system]

@digpen

select car, mont, sum( sales ) as sales from table group by car, month
UNION
select car, mont, sum( 0 ) as sales from table group by car, month

Sorry, but to me, this is not working.
The second part of this query should return a line for the missing months with zero as sales, but it is not.
In my example, it should return a line with the values

Ferrari   March   0 
Porche    March   0

But it doesn’t.
The second query only adds a line with a 0 values to the car/months that already exist in the result of the first query.

---

tiberghv (BOB member since 2009-02-04)

[system]

What if instead all this you have a prompt in place of filter. Does it retain the March column with 0 values.
Though this will require the report to be run everytime for new prompt value(s), but it might help you get round it

.

---

haider (BOB member since 2005-07-18)

[system]

@haider

What if instead all this you have a prompt in place of filter. Does it retain the March column with 0 values.

No it doesn’t.

---

tiberghv (BOB member since 2009-02-04)

[system]

Do you have a calendar table you are outerjoining against for the dates you want to include? If so, that would have returned the 0 for every month.

---

digpen (BOB member since 2002-08-15)

[system]

@tiberghv

@haider

What if instead all this you have a prompt in place of filter. Does it retain the March column with 0 values.

No it doesn’t.

Check this again because it should work with prompts if not with report filters.
See that the month object on the cross tab is from the second query(having just the month object with no prompt) and not from the first one

.

---

haider (BOB member since 2005-07-18)