Hello,

I like pivot tables and use them very often.
However, I recently found a difficulty for an application involving a rather large amount of data residing on a sqlserver database.

The structure of the table to be analysed by pivoting is very simple:

key1 | key2| key3 | key4 | .... | quantity1 | quantity2 | quantity3| ...

The different keys (key1, key2, ...) can be looked up in several small tables to get their meaning in full text:

key1 -> txt1
key2 -> txt2
....

The problem is related to these keys:
The end-user typically doesn't remember the keys to be used because there are many of them.
Therefore, I need to provide both in the pivot table record source ...
... and this make the amount of data transferred much larger and unacceptable.
The view prepared on the server for this purpose looks like this:

key1 | txt1 | key2| txt2 | key3 | txt3 | .... | quantity1 | quantity2 | quantity3| ...

The repetition of the additional texts (txt1, ...) grow the size of the data transferred enormously and I cannot continue with pivottables unfortunately.

I know that keys can be "replaced" dynamically in an excel table (just by overwriting them).
But since this is a totally manual procedure, it is impractical. This would mean too much work and it would be difficult to keep this configuration up-to-date.

How could I reduce the amount of data transferred, and still offer the keys and their meaning to the end-user?

Thanks a lot for any suggestion.