FM Join/Bridge Tables, Filter, Cross-Join, etc.

[delish]

Was any relief provided by Cognos. I have a similar ticket open with them but no response as of yet.

I have had an issue with Report Studio and FM for weeks now. I have a ticket open with Cognos but I haven' heard back in days. I'm hoping someone can assist. I have what I believe to be a pretty common situation that I cannot get FM and RS to work with.

I have a fact table "Sales" with a dimension table "Customer Service Rep". I also have a table called "Customer Service Rep Group" that contains all groups, and a bridge table between Customer Service Rep and Customer Service Rep Group to capture all groups that a customer service rep belongs to. This table represents the many-to-many relationship between Reps and Groups since a rep can belong to multiple groups.

I have a filter that should limit sales to customer service reps that are in certain user chosen groups. I have been struggling with cross joins and the lack of the ability of cognos to traverse the relationship of a customer service group (bridge table) to the customer service rep to the sale. It acts as if the customer service groups (bridge table) is in no way related to sales even though it is via the customer service rep table.

One of the most frustrating issues here is why RS prompt queries have to be related to the report queries? In this case I simply want an independent query to return a list of values to a prompt. I then want to pass those values to an "In" statement in a query filter. Why does the prompt query have to be at all related to the report/list query? This is a simple thing to do in Reporting Services and Crystal. Cognos seems a bit heavy handed here.

[MFGF]

Hi,

How have you modelled this in FM?  My guess is that if your bridge table is at the many end of relationships, it will be treated as a fact by the query engine, which is of course not desired.

If you can provide more info about your relationships and cardinalities, we may be able to suggest a solution (although in my case this will probably be after the Christmas break, as I've just signed off on my last day in work till the new year).

As for prompt queries, these are entirely independent of the main report query.  Why do you think they need to be linked?  Obviously the filters they feed into are based on the main query, but that's as far as it needs to go.

MF.