Universe level conditions

building semantic layer
1

Hi,

I have 3 tables like country (dimension), sales (measure), revenue (measure). Now my requirement is like

1.if user selects country and sales then sales objects thenparticular region (say a) should be displayed in the report.
2. If user selects country and revenue objects then revenue of particular region (say b) should be displayed.

All the above conditions should be applied at universe level only.

Any suggestions are highly appreciated.

Cheers,
Suresh Aluri.

sureshbabualuri :india: (BOB member since 2008-03-26)

2

If you want Region specific / Sales specific figures you can try using CASE WHEN stmt in the object level in designer.

But the best way is to use Region as a prompt and let user decide what they want to view in the report.

jprasanthram :switzerland: (BOB member since 2010-12-10)

3

If Sales and Revenue are in different Classes, you can add a pre-defined condition in the Sales class to select region-a and enforce it for the Class. Then create a pre-defined condition in Revenue class to select region-b and enforce it for the Class.

Now if you select Country and Sales, region-a will be selected. If you select Country and Revenue, region-b will be selected.

BUT if you select Sales and Revenue in the same query, you will not get any results.

Does this help?

Mohan :us: (BOB member since 2002-08-15)

4

if this requirment is based on underlying business logic/process, then can I conclude that the Sales values for region (B) and the Revenue value for region (A) would perhaps be NULL or 0?

If this holds true, you can put Class level conditions like -
SALES CLASS: Sales > 0 or Sales is not null
REVENUE CLASS: Revenue > 0 or Revenue is not null

However, for class level conditions, as Mohan already mentioned, the two conditions will be combined in the where usign AND. So the result will have only those regions which have both Sales as well as Revenue.
if the two region sets are mutually exclusive then the query will fetch no results!

rig (BOB member since 2010-10-22)

5

Thank you all for your views

Cheers,
Suresh Babu Aluri.

sureshbabualuri :india: (BOB member since 2008-03-26)