Crosstab with columns at different grain

[dssd]

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?

[dssd]

Also the query splits and is joined through a full outer join. Is there a way to combine it into one query?

[Satheesh]

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

[dssd]

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

[Satheesh]

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.....