dimensions in pivot table disappear

[#1]

I have a pivot table where the only expression is

if(IsNull(sum(SALES.SaleAmount),0,sum(SALES.SaleAmount)

, and the pivot dimension is the "Year-Month" (e.g. 2014-01). There are 9 other dimensions, starting with the Sales Rep, and including such information as the company name, where the lead came from, etc.

We have 13 sales reps, and for most of the people, it shows correctly. However, when I select either of two specific reps, only the first 3 dimensions appear, and all the others disappear.

I have NO dimension where "Suppress when value is Null" is checked, or where "Show all values" is checked. In the presentation tab, "Suppress zero values" is NOT checked. I can't for the life of me figure out what would cause all the other dimensions to disappear.

I thought that perhaps it was because the 3rd dimension (the last one that shows up for everybody) might be Null for the reps in question, so I put in an "IsNull" condition as I did in the expression. I also did this for the last dimension (the pivot dimension), but to no avail.

Any thoughts?

[#2]

Could you please attach the sample qvw to check?

[jonathandienst]

Hi

I suspect that the dimensions that 'disappear' are not on the same table as the transactions. For the 'problem' reps, there are no links to those dimension tables, so the data is not null, its missing. For missing data it really doesn't matter what you put in the expression, because it is never evaluated.

If the dimension tables contain little information (eg and ID and a name), then you could just flatten your structure by joining or mapping your dimension into the fact table. Now you will have nulls for the dimension values.

HTH

Jonathan

[#4 · Accepted solution]

Hi, but I seem to have answered my own question.

Two of the dimensions were, for certain sales reps, always evaluating to "NULL" in the SCRIPT. Apparently, trying to deal with these in the table by "IsNull" conditions isn't enough.

I added "Coalesce" statements to the offending fields in the script, and voila! Everything showed up.

Whoever thought nothing could cause such problems?!

[#5]

Hi Jonathan,

I appreciate your thoughts, but all of these are drawn from the same table "SALES". I'll keep your ideas in mind

if I see similar problems in the future.

Thanks,

Kevin