Sage 100

Sage Intelligence v 4.50.7107.015 - template Excel 2007 xltx Created connector AP Invoice History Header and Detail to GL Account, link by AP History Detail AcctKey. I can create a report with the pivot with fields: acct, acct desc, vendor name, ext amt and transaction date. No problems. When I try to group the columns (transaction) by Month and Year I get ""Cannot Group by Selection"". Finally got it to group by month and year, created and relinked template and the dang thing reverts to columns for each posting date when re-run. Checked the transaction date is a date on the source data.

I think there is a bad date somewhere in the detail data which is why the pivot table is burping on that group selection.

I looked for blank dates but not bad, I'll give that a try Jeff, thanks.

No bad dates, no blank dates. Killed the pivot, created and linked again. Used the Report Tools Create pivot and was able to group the dates by month and year...create linked again. Next run, the dates were all mm/dd/yy again. My head hurts from pounding it against the wall....

Resolved - added Excel formula to the connector Text(TransactionDate, ""YYYY-MM"") and then used this for the columns. This avoided grouping on transaction dates all together.

@MoiraGoggin excellent solution! Thanks for sharing.