Toad Workbook Pivots Columns dont update

We just finished a very large conversion of Hyperion Brio Workbooks to Toad Workbooks. End users are now using them. When they add a new column to a query the existing pivot does not pick up this new column.

It acts like the workbook saves the initial pivot layout and then won't update the columns unless it thinks the query has changed. When it thinks it has changed it will ADD a new column but keep an OLD column. This can be very confusing.

For Example: If the column was Trandate and was added to the pivot but the user changed the alias to TranDate the new alias would not be added. If you jiggle the sql a bit and somehow get the pivot to understand the sql changed then it will add TrandDate but Trandate will still be there and if used in the pivot all values will show "Error".

The stickiness of the pivot layout is getting in the way of production. The Error message also is not helpful enough for the end user to fix. If it said "Error, this column does not exist in the result set" then it would make sense.

We are using Toad Data Point version 5.3 and cannot upgrade. Is there some way to "trick" the SQL editor or Pivot to update? There doesn't seem to be any manual refresh option.

Hey there - So when you add a new column to the query you are not re-running the query?

yes, I am rerunning the query and can see the new column in the result set. But it doesn't always update the pivot. Sometimes it does and most times it does not. I can't figure out what triggers the modified relationship between sql execution and existing pivot.

Sounds like something that we fixed in TDP 6.0, but since you can't upgrade to the latest version, can you do something like add a quick comment, or add a silly filter to the existing WHERE clause (e.g. like 'AND 1=1'?) Just trying to think of some work-arounds for ya.