Force expressions to skip blank lines in dimensions

[Constantine1]

Hi,

I have one curious situation. In a report of sales and expenses, there are several dimensions - order type, client type, region and so forth, for which there are sales and expenses allocated to each.

My issue is when I try to put it in a straight table and add the whole dimensions, I get for example by client type one line with the client type and another one that is blank, both being under the same order type. Now I can't suppress the blank values in client type because for some reason I have the expenses allocated on the blank line and not the other one.

Is there a way to force the blank lines to no show and make the expenses calculate correctly?  

[Marijn]

Hi Constantine,

It sounds like certain dimension fields are in the fact table or certain fact fields are in the dimension table. For example, if client type is in the fact table, it will display the two rows separately. By putting the client specifics in a separate dimension table, you could do something like this:

Sales:           sum({$<ExpenseOrSales={'Sales'}>}Amount)
Expenses:  sum({$<ExpenseOrSales={'Expenses'}>}Amount)

Datamodel could look something like this:

Hope this helps!

Marijn