cells not diplaying

I have 2 dimensions dimA and dimB. Dim A has members Aa and Ab while dimB has members Ba, Bb and Bc. The fact table has columns dimAKey, DimBkey, CountofX.

I need to display a result that shows counts for all members for dimA (whether there is a value in CountofX or not) but for dimB I only want to see the members that have values. eg.

DimA DimB CountofX
-
Aa Ba 3
Aa Bb 4
Aa Bc 2
Ab Ba 7
Ab <blank> <blank>
Ab Bc 3

Can someone help me?

Thanks

Your example does not really make sense, you might have simplified it a bit too much, you could get something like the following, but it does not sound like this is what you are after.

DimA DimB CountofX
-
Aa Ba 3
Aa Bb 4
Aa Bc 2
Ab Ba 7
Ab Bb <blank>
Ab Bc 3

Assuming that you had a value of Ac another alternative would be something like the following, where effectively you get every value of DimA against DimB.All and also all the members of DimB that have values.

DimA DimB CountofX
-
Aa Ba 3
Aa Bb 4
Aa Bc 2
Aa All 9
Ab Ba 7
Ab Bc 3
Ab All 10
Ac All <blank>

Would something like this suit your requirements?

|||I'll try it. Please post the syntax|||

Basically I would create two sets on the row axis, union them and then put them back in their hierarchy order. I don't know what your cube is called or what sort of hierarchy attribute structure you have, but in psuedo code, it would be something like the following (which is SSAS 2005 syntax).

SELECT
{Measures.CountofX} ON COLUMNS
Hierarchize(UNION({DimA.<Hierarchy>.Members} * {DimB.<Hierarchy>.[All]}
, NONEMPTY({DimA.<Hierarchy>.Members} * DimB.<Hierarchy>.Members)),POST) ON ROWS
FROM <Cube>

|||

I could not get the result of

Ac All <blank>

to show up. If CountofX is null for all members of dimB against value Ac, that whole line does not even show up. I need to be able to see all members of dimA at all times regardless of whether intersection with dimB members has values or not. However, for dimB I want to see only those members that have values at intersection with dimA. I hope I have conveyed my problem well enough for you to help me. I am using SSAS 2005

Thanks,

newtomdx

|||

I can't think how any of the tuples involving dimA and the all member of dimB could be excluded, unless your query tool is doing some extra filtering.

What are you using to run the query?

Can you try running it using SQL Server Management Studio?

Can you post the exact query you are running?

|||Yes, there was a filter. It does work correctly once I removed it. Is there any way I can just have all the members of dimA and show members of dimB that have values in countofX without the "All"? Thanks for your help.|||No, in multi-dimensional space (at least in SSAS 2005) there is no such thing as a null value in a dimension. Even if you were not displaying dimB at all, technically the cube is still returning the value that relates to the default member in dimB (which in this case is "All"). If you are using some sort of reporting tool like SSRS, you might be able to surpress the output of the all label.