How to handle an 'exlusive or' situation??? 1

[SergeS]

My mind is tired...

Let's say I have 4 database tables: Entity, BusinessGroup Division and Department.

Conceptually, there are two possible hierarchies:
Entity
BusinessGroup
Division
Department

and

Entity
Division
Department

so there are divisions which relate directly to an Entity and divisions which relate via a BusinessGroup to an Entity.

The following relationships exist:
Entity - BusinessGroup
Entity - Division
BusinessGroup - Division
Division - Department

In my reports I want to report all Entities along with the Divisions that belong (either directly or indirectly) to those Entities and vice versa.

I don't know how to solve the loop between Entity - BusinessGroup and Division. Using contexts in a way that requires user interaction at report level is not an option in my project situation.

Should I make hierachies, should I use aliases, should I generate database views...I've tried a lot but I wasn't able to create THE solution.

I think my biggest problem is that the two relationships Division - Entity and Division - BusinessGroup is an exlusive or and I don't know how to handle that!

Suggestions?

 

[promero]

If by "User interaction" you refer to selecting a context, note that the context selection is only required when the report is in development process. Context selection is only required on individual data provider editing or refreshing. Futher full document refreshing does not requiere to select the context.

I have not THE solution, but i can help you to make queries where context selection is not required. In fact, my solution does not use contexts, but alieses.

I assume you have the following structure (a loop):
[tt]
Entity-------------Division----Department
| |
+---BusinessGroup---+
[/tt]
[ol][li]First, change the structure to open the loop like this:
[tt]
Entity2(Alias)-----Division----Department
|
Entity----------BusinessGroup
[/tt][/li]
[li]Then, change the definition of all universe objects that uses the Entity table the following way:
If a SELECT o WHERE textbox is
[tt][...Entity.X...][/tt]
Chage it to the following form:
[tt]@Aggregate_Aware([...Entity2.X...], [...Entity.X...])[/tt]
Example: change [tt]Entity.ID[/tt] to [tt]@Aggregate_Aware(Entity2.ID, Entity.ID)[/tt]

Only one [tt]@Aggregate_Aware[/tt] call can be used in a SELECT or WHERE textbox. This function is not restricted to measure objects. It can be used with dimension and detail objects too without aggregate functions.

The [tt]@Aggregate_Aware[/tt] function tells BO to put in the SQL query the first of its parameters that is compatible with the rest of the objects in the query.

When constructing a query, if you do not include objects of BusinessGroup, the fist form will be used (the one with the Entity2 alias). But if you include objects of BusinessGroup, the second form will be used (the one with the real Entity table).
[/li]
[li]Finally, you can make two data providers: one with objects of BusinessGroup and another without them.

Alternatelly, If you want the two queries to be in the same data provider, you must make a union of them. The first one will use BusinessGroup objects, and the second one will use fake objects (objects specially created in the universe with default constant values).
[/li][/ol]

 

[promero]

If by "User interaction" you refer to selecting a context, note that the context selection is only required when the report is in development process. Context selection is only required on individual data provider editing or refreshing. Futher full document refreshing does not requiere to select the context.

I have not THE solution, but i can help you to make queries where context selection is not required. In fact, my solution does not use contexts, but alieses.

I assume you have the following structure (a loop):
[tt]
Entity-------------Division----Department
| |
+---BusinessGroup---+
[/tt]
My Solution is the following:
[ol][li]First, change the structure to open the loop like this:
[tt]
Entity2(Alias)-----Division----Department
|
Entity----------BusinessGroup
[/tt][/li]
[li]Then, change the definition of all universe objects that uses the Entity table the following way:
If a SELECT o WHERE textbox is
[tt][...Entity.X...][/tt]
Chage it to the following form:
[tt]@Aggregate_Aware([...Entity2.X...], [...Entity.X...])[/tt]
Example: change [tt]Entity.ID[/tt] to [tt]@Aggregate_Aware(Entity2.ID, Entity.ID)[/tt]

Only one [tt]@Aggregate_Aware[/tt] call can be used in a SELECT or WHERE textbox. This function is not restricted to measure objects. It can be used with dimension and detail objects too without aggregate functions.

The [tt]@Aggregate_Aware[/tt] function tells BO to put in the SQL query the first of its parameters that is compatible with the rest of the objects in the query.

When constructing a query, if you do not include objects of BusinessGroup, the fist form will be used (the one with the Entity2 alias). But if you include objects of BusinessGroup, the second form will be used (the one with the real Entity table).
[/li]
[li]Finally, you can make two data providers: one with objects of BusinessGroup and another without them.

Alternatelly, If you want the two queries to be in the same data provider, you must make a union of them. The first one will use BusinessGroup objects, and the second one will use fake objects (objects specially created in the universe with default constant values).
[/li][/ol]

 

[promero]

Sorry for multiple posts, but the oprtal game me an error, althoug it post the reply.

 

[SergeS]

Promero,

thanks for your explanation. If you don't mind I have two questions about your tip:

1) In my universe I have the dimension object Entity.
The select-clause is: Entity.description. I've changed it to @Aggregate_Aware(Entity2.description, Entity.description)

The where-clause is:
Entity.effectivedate <= @Prompt('Enter reference date','D',,MONO,FREE) AND (Entity.enddate > @Prompt('Enter reference date','D',,MONO,FREE) OR Entity.enddate IS NULL)

However, the where-clause should now be dependent of the table used in the select clause, shouldn't it? Ether Entity2 or Entity should be used in the where-clause.

Is there a way to handle this in your solution (using @Aggragate_Aware)?

2)Can you please further explain ,,The first one will use BusinessGroup objects and the second one will use fake objects (objects specially created in the universe with default constant values)&quot;?

Imagine I want a report with the 2 columns Entity and Division. One query uses BusinessGroups the other doesn't, right?

I think one negative aspect of this solution is that the user should know something about the universe structure, because he has to know that to relate Entity and Division together he should use to different join-paths. Do you agree?


Greetings,
Serge

 

[promero]

1) The where shoud be like this:

@Aggregate_Aware(Entity2.effectivedate <= @Prompt('Enter reference date','D',,MONO,FREE) AND (Entity2.enddate > @Prompt('Enter reference date','D',,MONO,FREE) OR Entity2.enddate IS NULL), Entity.effectivedate <= @Prompt('Enter reference date','D',,MONO,FREE) AND (Entity.enddate > @Prompt('Enter reference date','D',,MONO,FREE) OR Entity.enddate IS NULL))

2) If you make two data providers, then no problem. One will have some BusinessGroup object and the other no.

If you make one data provider with an union of two quries, the first will have some BusinessGroup, and the other will have a fake object for each BusinessGroup object in the first query. you shold have one fake object in the universe for each BusinessGroup object. The definition of this fake objects will be a contatan value (0 for number, '' for text, etc.). This solution is a bit bad, because the user must know to use the fake objects properly. I prefer making two data providers, where fake objects are not necessary.

> I think one negative aspect of this solution is that the
> user should know something about the universe structure,
> because he has to know that to relate Entity and Division
> together he should use to different join-paths. Do you
> agree?

I agree. And I can't find a way to solve it. Yet another solution is to make two classes of objects, one for Entity and the other for Entity2. The user should know one is for divisions with BusinessGroup and the other for ones without it. But i think this is worst, because now you have two Entity.

 

[SergeS]

Promero, I am encountering an error following your advise:

Like I said, I have an dimension object Entity. The select clause is @Aggregate_Aware(Entity2.description, Entity.description).

I showed you the where-clause and you showed me how the rebuild the where-clause. Now I am getting an error from BO that I am using multiple @aggregate_aware calls in one selection.

Looking in the Error reference manual this situation doesn't look too good, because BO simple doesn't support it.

I hope you can also help me out with this one.

Serge

 

[promero]

I was not sure if this will happen. Now I know it happen . A quick solution is to put the Where in a separate condition object (those with a &quot;filter&quot; icon). This should fix the problem.

I remember you again that all this could be avoided if you use contexts and context selection in the query. Futher full document refreshing does not requiere to select the context.

 

[SergeS]

Promero, thanks for all your support. I think I will opt for the solution with contexts because putting the Aggregate_Aware in a condition object results in some other conflicts in my situation.

Thanks,

Serge