BusinessObjects Board

Cross tab display empty columns

building reports webintelligence
#1

BO XI V3 webi. I have a cross tab with financial week and finacial year looking at years 2011 and 2012 and 8 weeks, weeks 32-39. Week is at the top of the cross tab then year underneath both as the headers and its by item down the left. We are currently in week 34, so some weeks in week 2012 habe no data yet, but I want those weeks to appear anyway, but with 0. A variable is used in the cross tab to set all blank cells to 0 if no data is present but the column for those weeks just dosent show up at all. Ive played around with all the display settings i.e show column with empty etc but it dosent make any difference. Can anyone help please.

dave282 (BOB member since 2012-02-06)

#2

Hi Dave,

This Reporter’s FAQ can get you started:

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

#3

I have a somewhat more detailed explanation (more detailed than the FAQ) on my blog. The first example (titled “Making Up Date Part I: Personal Data Providers”) shows you how to fill out your crosstab using a source like Excel. The second article (“Making Up Data Part II: Using Universe Data”) is possibly a better answer because you use data you already have in your universe. In your case, create a second data provider that returns just the calendar data and merge it with your current data provider that returns your measures. That way you have all of the calendar data even for zero values.

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

#4

Great thanks all, that has worked and I now have the blank weeks, but it appears to have put all the blank weeks at the end, rather than putting them in the right order which is 2011/2012, 2011/2012 etc. Is there anything I can do to get them into the correct order?

dave282 (BOB member since 2012-02-06)

#5

Can anyone help with thus bit please?

appears to have put all the blank weeks at the end, rather than putting them in the right order which is 2011/2012, 2011/2012 etc. Is there anything I can do to get them into the correct order?

dave282 (BOB member since 2012-02-06)

#6

Please, create a second query on the universe, where you only include the week.

Use this week in your crosstab, instead of the week you got from your initial query.

Next, add 0 to your Measure, in the crosstab.

Adding the 0 will force a 0 to be shown for all cells without data.

Peter

pderop (BOB member since 2010-10-27)

#7

Have done all that and the columns are showing zeros, the problem is all the zero populated columns are showing at the end of the cross tab instead of correctly being in week/year order, do you know how to correct this please?

dave282 (BOB member since 2012-02-06)

#8

Heres the problem, please see attached
crosstab.png

dave282 (BOB member since 2012-02-06)

#9

I think you’re also going to need to also include the year in your second query.

Make sure both are merged with the year and week dimensions.

Works fine for me tbh…

Peter

pderop (BOB member since 2010-10-27)

#10

Managed to sort this, I had four providers in total, so i got rid of the addtional two, so was just left with the main provider and a provider for the week/year and dit worked. Dont know why that was causing a problem with the order but it was. Thanks

dave282 (BOB member since 2012-02-06)