BusinessObjects Board

BOR 6.5.2 Show empty Columns in a crosstab

building reports desktopintelligence
#1

Hi Guys and Girls,

I’ve searched and haven’t seen anything that works as an answer.

I have a crosstab that has dates running across the page
and sales agents running down the page.
On a couple of days there are no sales.
I still need these days to show in the columns, but with empty values.
Is there a way to do this in the crosstab?

Any help is substantially appreciated.

Cheers

Tim

tim_BO (BOB member since 2007-05-31)

Show Blank Coulmns in Cross Tabs
extra colom is displaying in crosstab report
#2

Hi,

You can apply outer join on tables in designer

regards
srinu

srinud81 :india: (BOB member since 2006-07-04)

#3

srinud81 :

Thanks for the reply, but the data is already being retrieved succesfully.
I’m using a cross tab to show data, not a table.

tim_BO (BOB member since 2007-05-31)

#4

srinud81 means to modify your universe and apply outer join for the Date column.
If you have access to Designer and know about the database tables, is the Date field coming from a lookup dimension table or a fact table.
If its lookup table it should have all the required dates to be shown in the report even if there is no associated records in the fact table.
Hope you got it.

Cheers

haider :es: (BOB member since 2005-07-18)

#5

Thanks Haider.

Modifying the universe is not an option at all.

tim_BO (BOB member since 2007-05-31)

#6

Then talk to the person working on Designer, who knows about those objects.
If he can answer your queries then your requirement is achievable.
If its confirmed that the Date object is from a lookup table and has all required data then you can show those empty columns on the cross tab.

You can also create a separate query taking only the date field and see if its returning all the dates needed on the crosstab.

Cheers

haider :es: (BOB member since 2005-07-18)

#7

Hi Haider, there will be no changes made to the universe at all.

It’s not going to happen in this organisation.
I’ve been brought in to simplify a lot of reporting and currently they use excel to compile the BOR data for different sales people.

All dates exist because all dates have some sales.
But some of the filtered tables might not have all of the sales agents and therefore don’t show all of the dates.

I can see all of the dates and use them as is in a table showing all sales agents.

All I’m looking to do is a way to get the crosstab to show me columns with 0 values.

You’ve said
"If its confirmed that the Date object is from a lookup table and has all required data then you can show those empty columns on the cross tab. "

It does have all available dates in it, which brings me right back to the original question.

tim_BO (BOB member since 2007-05-31)

#8

Which means that though all the dates are there in the microcube (raw data from DataManager) but doesnt get mapped on the cross tab across.
Then do this.
Create another query and bring in the same object. Link both the queries, though it gets linked on its own.
On the cross tab replace the Date object across with the Date object from second query.
See if it helps.

Cheers

haider :es: (BOB member since 2005-07-18)

#9

Tim,

Just a long shot, but try putting NoFilter() round your date object on the crosstab. It may not work, but without access to the universe, it’s one of the things that you could try.

Cheers,
Mark

#10

You have a crosstab, and you have three linked Data Providers, is that right? You have one Data Provider to try to give yourself all of the “Across” values, and one Data Provider to try to give yourself al of the “Down” values?

If so, then when I tried to do that, I found that I couldn’t have it both ways. I could either have all of the “Across” values OR all of the “Down” values, but not both. We had to pick which was most important.

Anita Craig :us: (BOB member since 2002-06-17)

#11

Markp: No luck there I’m afraid.

Anita : Thanks for the reply, but Alas, there is only one data provider.
I’ll break the news to the troops that the reports will now be formatted differently.

Cheers

T

tim_BO (BOB member since 2007-05-31)

#12

Have you tried this

haider :es: (BOB member since 2005-07-18)

#13

Tim:
This is the best way to show the dates when there are no sales… Create a second DP (this DP must bring all the dates without a single day missing in between), use this date object in your report.

You can bring this date object from a Calendar Table(or somewhat similar) from your universe if you have it.

This is possible.

BO_Chief :us: (BOB member since 2004-06-06)

#14

Well, if you want to force a particular dimension – you need a separate data provider for it from a dimension table, which has an entry for every possible value.

While you might not be able to force both the across’es and the down’s – you could get one if you can do a separate data provider for it from a source that has a row for every value that you want.

Anita Craig :us: (BOB member since 2002-06-17)