Relating data from multiple tables

[nextchapter612]

I am a new Cognos user and desparately need help. I have been trying to create a what I think should be a simple cube based on a SQL Server database consisting of multiple tables.

Tables involved with main fields listed are:

tblCase
CaseID
CaseName
CaseCategoryID
CaseTypeID
Settled
DateSettled
SettlementAmount
InceptionDate
AttorneyID
LeadAttorneyID

tblExpense (multiple expense records per case)
ExpenseID
CaseID
RequestDate
FiscalYear
FiscalQtr
AmountRequested
DateRequested
AmountObligated
DateObligated
BudgetCategoryID
CaseCategoryID
CaseTypeID

tblBudget (multiple budget records per case)
BudgetID
CaseID
BudgetDate
FiscalYear
FiscalQtr
BudgetAmount
BudgetDate
BudgetCategoryID
CaseCategoryID
CaseTypeID

Problem:

The user wants to be able to see Case related Budget and Expense totals together based on fiscal year.
If I use separate .iqd for each table I will get the correct total figures but cannot get the Expenses and Budgets related when I look at the scope Each shows the correct information related to the case but not to each other. When I create a single .iqd combining all the fields, I can get all the columns related but the totals are incorrect because both the expenses and the budgets have multiple records per each case and therefore the totals become elevated. I have tried grouping the expense and budget amounts which works to some degree, but not all the totals are correct, I believe this is still related to multiple expense and budget information within a give fiscal year.

What can I do to resolve this.

 

[Recce]

Hi nextchapter612, I am not sure exactly what the problem is but, if you want then you can send me some samples of the data in different spread sheets to sienner@hotmail.com I will see if I can help you with that then

 

[CognosProfessional]

nc612, you didn't mention that you joined the 3 tables in the catalog before you look at scope in the transformer.

Why not build your .imr's with the correct results first all joined up in the catalog, then create the iqd's and use those for the model and your scope should turn out better.

CP