I have loaded a product detail reference table which contains productcode, productdesc and productmastercode as well as a field I made in the load from productdisplay which is the productcode & '-' & productdesc. There can be a one to many relationship between productmastercode and productcode.
I have built a pivot table in which I have as dimensions product hierarchy then productmastercode then productdisplay and this works fine. However what I would like is that the dimension for productmastercode be a calculated one that is the productmastercode & ' - ' & 'one of the productdesc'
similar to the way in TSQL you can do max(productdesc). I tried max() and maxstring() but both gave a Error in calculation error in the pivot table
That is placing a desc beside each line but it is not maintaining the master code piece....
eg
Master Code Product Code Product Desc aggr()
111111 222222 Test Line 1 111111 - Test Line 1
333333 444444 Test Line 2 333333 - Test Line 2
333333 555555 Test Line 3 333333 - Test Line 3
I would like the aggr dimension to in the above display 333333 - Test Line 2 for both lines so that then in the pivot table setup it would allow for a group view of the two product that i can then plus out to. the aggr as suggested by whiteline shows the mastercode with a description but a new line for each desc so no chance to group on the mastercode.
