how to do equivalent of an outer join

In the db, I have two tables - testcases and testresults. Test results have a field called testcaseid that corresponds to the id field in the testcases table. Not all test cases have results in the testresults table. Tests results have a field called "resultvalue" that can be "pass" or "fail". Test cases have a field called "area" that categories the feature the case belongs to.

I want a pivot that shows, per area, per test case, how many failing results, how many passing results, or if the case does not have any results. I am used to doing this with an outer join in SQL.

I cannot figure out how to do this with a cube. I either get the same number of results for every row (the total number of results in the testresults table), or I get only test cases that have corresponding results. Does anybody have the step by step on this? It seems like a pretty basic problem, but I just cannot figure it out from the docs.

thanks!!

"or I get only test cases that have corresponding results". How are you checking that only test cases with results are included in the cube (assuming that you created a cube with a TestCase dimension and TestResults fact table)? And are you using AS 2000 or AS 2005?|||

AS 2005

how I am checking that only test cases with results are included in the cube:

1. process cube

2. browse cube

3. drag the "area" field from the testcases dimension (indicates what feature the test case is for) into left column

4. drag the "testresults count" measure into the data area

I created a TestCase dimension, a TestResult dimension, and a Measure Group from the TestResult dimension that has "testresults count" in it. Best I recall, I checked the "fact table" box next to the "TestResult" dimension when building my cube.

I am still new to the concepts here.

|||Did you enable visibility of empty cells (right-click in browser pivot table and check "Show Empty Cells")?|||

I did not try that - I will take a look again and see if that does it.

I had tried setting "Visible" on the unknown member property of both the testcases and testresults dimensions. Is that a different setting/behavior?

|||The testcases table is a source for [Test Cases] dimension. According to your task definition you can have test cases without results, but not results without test cases. Therefore you should not worry about unknown member for this dimension.
The test results table is a source for the measure group [Test Results].
The [Test Cases] dimension should have regular relationship with [Test Results] measure group. The relationship is based on TestCaseId column, which is present in both tables.
One problem is to find out how to define the measure. In my small modeling application i had Test Results table to have Outcome column, which i made of Text type. Your cases are limited by "Passed" by "Failed". For experiment, I also added results containing "Deadlocked".
So i created a measure, which is bound to rows in [Test Results] table and has aggregation function set to Count. I also had the same [Test Results] table to be a source of Outcome dimension. That dimension contained just one attribute Outcome bound to Outcome column from [Test Results] table.
The relationship between Outcome dimension and [Test Results] measure group is Fact relationship.
When i browse the cube and put just [Test Cases] dimension on axis, the measure shows the count of all runs per area (Passed, Failed and Deadlocked). But when i put Outcome dimension an another axis or on filter axis i can see specifically how many passed, failed or deadlocked per area.
I can sent this sample AS project with mdb file, but i have not yet found how to attach binaries to posts.