Filter on Fact Not in Query

[VizWiz]

Hello,

Let's say we have three tables: dim_project, fact_budget, fact_project_type. The first two are self-explanatory, but fact_project_type is not really a fact in the sense that it does not measure anything--it contains the 0 or many different types a single project can be. Budgets are on a project level, and not project type level.

When joined correctly, some example rows might look like this:
Project ID    Budget Amt    Project Type
1                 $150               IT
1                 $150               Infrastructure
2                 $500               Marketing

My question is: how do I narrow down the list of projects by project type without displaying project type to the user? Whenever I have a list with just Project ID and Budget Amt, and a filter for project type, I receive this error:

XQE-PLN-0084 SQL cannot be generated, because the filter condition [Project].[Project Type] in ('IT', 'Marketing') could not be applied to the following fact stream which does not contain a fact projection: (FACT_PROJECT_TYPE).

One suggestion I've tried is adding project_type to the properties property of my list, but this causes duplicate rows (e.g. 1 - $150 shows twice because it has two project types. The budget amount is correct, but I only want one row to display).

I have a workaround for this by performing the join to fact_project_type in the report instead of the framework model, but I don't like this solution.
Any thoughts on correctly modeling this in the framework? Many thanks for any suggestions!

Please let me know if any of this is unclear, I may not be explaining this very well.

Thanks!
VizWiz

[Francis aka khayman]

you can create new dimension containing project and project type information

[Cape Cod Gunny]

You should be able to use a sub-query of the main query and limit the results to Project ID and Budget Amt.

1. Drop a new query on the query designer (this will be your sub-query)
2. Drag the main query to the right of the new query and let it go when the arrow appears
3. Pull in only Project ID and Budget Amt
4. Change the report to use the Sub-Query

You can see a picture of this type of query here:
https://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html

[VizWiz]

Sorry for the late response and thank you for the replies.

Francis--I'm not sure how making a new dimension helps with this? At any rate, this would change the granularity of that table and I have several fact tables already linked to the project dimension, so I don't think this is a good solution.

Cape Cod Gunny--Thanks, I'm familiar with this technique, but I was trying to resolve this from framework manager instead of a report-basis so it is available to self-service users. I have a workaround whereby I specify a join in the report, but really am trying to get this working in FM.