I have 7 columns in my report
Country State Type1 Type 2 Type3 Total Score
USA IN 30 40 50 90
USA IL 20 10 10 20
I have two facts tables at different levels
Country State Type Amount
US IL Type1 20
US IL Type2 10
& second fact is
Country State Total Score
US IL 90
US IN 20
Is it possible to design the above report?
Also the query splits and is joined through a full outer join. Is there a way to combine it into one query?
There is two ways You can do in Two Levels...
1.) At Framework Manager Level You have to use Galaxy Schema
2.) At Report Studio Level You have to apply Join in Between two Query's on Common Column.
Regards
Sateesh
If i join them then total score would get repeated for each record in the first query as below
US IL Type1 20 90
US IL Type2 10 90
but, on the report i want it to be displayed as
Country State Type Score
US IL 20 10 90
If i dont do a stitched query then due to double counting Score would show as 180 and not 90
Hi,
If Ur using Galaxy Schema, No need to join..
for example..
You Have 2 Fact Tables
Fact1
Data Items: -> Country, State, Type, Amount(measure)
Fact2
Data Items: -> Country, State, Total Score(measure)
So As per above
Country and State Dimensions are Confirmed Dimensions means These are linked to two fact tables.
so There is a relationship between two fact tables...
Take One Query
Drop the Items below
From Country Dim ---> Country
From State Dim --> State
From Type Dim --> Type
From Fact1 --> Amount
From Fact2 --> Total Score
Assign this Query to Crosstab
And drag Country and State and it in to Rows
Drag Type and Drop in to Columns
Drag Amount and drop in to Measure
Drag Total Score drop in to column beside Type (Not Under type)
Then it will com
try this.....