Show all months & customers even if they don't have sales

[Former Member]

Hi,

I'm trying to show all months and customers in a table even if they don't have sales/amount in a month. I've tried to create a new queries with only months and no key figures and I'm able to see all months but when I try to do the same for customers the table gets filtered.

The month and customer come from the other queries, where I don't have any key figures and the amount from the main query and I also merged the dimensions.

Existing data:

Month/Customer/Amount

Jan/1/10

Feb/1/50

May/1/10

Aug/1/10

What I'm trying to accomplish:

Jan/1/10

Feb/1/50

Mar/1/0

Apr/1/0

May/1/10

Jun/1/0

Jul/1/0

Aug/1/10

Sep/1/0

Oct/1/0

Nov/1/0

Dec/1/0

And this is how I tried to do but it doesn't work.

Queries

Q1......Month/Customer/Amount

Q2......Month

Q3......Customer

Webi table

Q2.Month/Q3.Customer/Q1.Amount

The Webi version is 4.1 with a BEx query 7.3 as my source.

Regards,

Ara

[Former Member]

Hi,

The second query must be created with [Month] and [Customer] and then merged with the first one in [Month] and [Customer]

Regards,

Rogerio

[Former Member]

Hi Rogerio,

Thanks for the info but it didn't change anything, I still only see Months where I have Amount.

I've also changed the table properties to show rows even if they are empty or equal to 0.

Regards,

Ara

[amitrathi239]

Hi,

Try to checked the option "Avoid  duplicate row aggregation" option in Right click->Format table.

Amit

[Former Member]

Hi, did you use the merged dimensionl in your table?

[Former Member]

Hi Amit,

That didn't work either... thanks for your response!!

[Former Member]

No, I used the ones from the separate query. Should I use the ones that I merged?!

[amitrathi239]

Hi,

in this case you needs to use the merged dimension objects.otherwise there is no use of separate queries.

Amit

[Former Member]

Hi,

In order to show the nulls you must use the merged dimmensions instead of the individual ones.

Regards,

Rogerio

[Former Member]

Hi,

I get the same results if I use the Merged dimensions or from the separate query.

Regards,

Ara

[Former Member]

One question, should I change the properties of the merged dimensions to have as my source dimension the one from the separate query or the original?! I have it to use the separate query.

Regards,

Ara