Simple aggregation problem - many2many rel. - help needed

building reports webintelligence
1

Hi all,

I have stated working with WebI about two months ago, and after a rough start, I am now beginning to feel comfortable with the basic reporting mechnaics.

However I have encountered a certain problem multiple times which I was not able to overcome so far. As soon as it comes to many2many relations I get stuck.

Imagine the following query result:



Database ID	   Database CI Attribute	  Host ID
Database A	    Attribute A		          Host A
Database B	    Attribute A		          Host A
Database C	    Attribute B		          Host A
Database D	    Attribute A		          Host B
Database E       Attribute B		          Host B
Database F	    Attribute C		          Host C
Database G	    Attribute C		          Host C

Now using simple aggregation formulas will result in the following table.



Database CI Attribute 	Database Count(=Count([Database ID]))	Server Count(=Count([Host ID]))
Attribute A	           3	                                    3
Attribute B	           2	                                    2
Attribute C	           2	                                    2

For each of the database related attributes, I want to count the number of “pure” host servers per category, while aggregating the remaining servers (those that host databases of multple attribute categories) into a new category .

The result table I want to achieve looks like this:



Database CI Attribute(Probably needs a new Var) 	Database Count(=Count([Database ID]))	Server Count(?)
Attribute A	                                     3	                                    0
Attribute B	                                     2	                                    0
Attribute C	                                     2	                                    1
Multiple Attributes	                             0					                        2

This is obviously a simplified version of my real Problem, but it emphazises exactly the main issue: many-to-many relations.
Ive played around with context operators but had no luck so far.
Is there a way to solve this?

Cheers

Hendrik

Enigmatus (BOB member since 2017-11-14)

2

Hendrik,

Simplified versions of real problems often don’t help the real problem. :slight_smile:

With that as a caveat, please can you define “pure”?

3

Hi Mark.

Thanks for responding, despite your caveats :wink:

A “pure” host server would be a host server that is only hosting databases of a single attribute category.

In my example, only Host C would be a “pure” one, as it only hosts databases of category C.

Enigmatus (BOB member since 2017-11-14)

4

Hmmm. This feels very much like a circular reference in Excel but you could get near it.

Your 0 in the Database Count for Multiple Attributes seems incorrect but I think that’s because you’ve hit a circular reference equivalent.

I’d try and put the logic into an IF statement so that it evaluates to a host id or null and then do a count. You’d need a ForEach ([Host ID]) in there somewhere I’d expect, but I haven’t got access to BO at the moment to test it.