Change from straight table to pivot table creates 'null' values

[#1]

This is very curious to me. I have a straight table that shows integer values >= 0 for all entries. Some of the entries, but not all, have sub-entries. eg I

have Aroute with two subsidiaries, Aiut Montreal and Aiut Toronto. In the straight table, it shows positive values for three fields, Capital, Customers, and Exmployees for both subsidiaries. It shows 0 - number - for for the four other fields in the table for these two companies.

If I change the table into a pivot table, and show it fully expanded, the values for Cap, Cust, and Emp show up exactly the same as they do for the straight table. However, if I collapse the pivot table to show only the parent company, these three values suddenly become 'null'. This is highly confusing - can anyone suggest why? Thanks.

[swuehl]

Maybe your expression can't be evaluated to an unambiguous value when collapsed? What are you using as expressions? Do you use aggregation functions in your expressions?

[#3]

That's what's so confusing - they are not expressions. I do all that work in my SQL statement, e.g.:

(SELECT

     COALESCE(COUNT(EMPLOYEES.ID),0)

     FROM dbo.Employees

          WHERE

          Employees.StartDate >= '$(vReportStart)'

          AND

          Employees.StartDate <= '$(vReportEnd)'

) as EMP

So the value should always be an integer; if there were no employees added, the COALESCE function converts the null value to 0. How could I use an aggr expression on what should just be an integer? If you have a suggestion, I'll try it!

[swuehl · Accepted solution]

Not sure if I understand.

Well, you've got two values e.g. for expression Cust, 51 and 32, for dimension value AIROUTE.

If collapsed, what do you expect to see then? The sum of both values? Then use

=sum(YOURFIELDUSED)

Or avg(), max(), min() etc.

[#5]

Thank you, my friend, you are correct as always.