I then join the past and present queries on unique ID.
I then join the Union and the join again on unique ID. I assign this to a list.
The output is fine If I only use the Unique ID and measures. When I start to add dimensions like Make, Model, Engine size etc, there's gaps in the data.
Can anyone help me approach this problem in a better way or fix the current method I'm using?
Thanks
What is the cardinality you use in your join ?
If you use Model as Unique Please make a join with unique id (1..1) relationship.
My first join of the 2 periods is 0.n and 0.n, outer join.
My second join of the union and the first join is 1.1 (union) and 1.n (first join)
Just to make my situation a bit more clear, I googled and found someone with a very similar problem to mines.
http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos8-l/how-to-filter-in-cognos-report-studio-4122409 (http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos8-l/how-to-filter-in-cognos-report-studio-4122409)
Someone sugested doing the whole thing in one query but using if fucntions in the data items. That's what I am currently using right now but the data set is large and the If fucntion slows it down.
Can someone help me do this with queries instead of if fucntions. One possible problem though, the unique ID's in one of the periods may not be in the other. Will this cause any problems?