Cannot group by Date in Pivot Table
Bruce Lane asked 7 years ago
The view that is created in the Excel Report Builder uses the varchar format for dates. Excel will not allow grouping on the dates in the Pivot Table with that format even if it is changed in the Sheet. Can the view be changed in Excel Report Builder to modify the format of a date column, or must it be made in SQL Server?
Answers
Best Answer
Jared answered 7 years ago
Bruce Lane replied 7 years ago
Jared replied 7 years ago
Bruce Lane replied 7 years ago
Jared answered 7 years ago
You can add the following line in your dex.ini to remove all formatting from Excel Report Builder. This will bring in the date as a serialized number. Excel understands this as date when formatted as a date.
EXCEL_REPORT_BUILDER_REMOVE_DATE_FORMATS=TRUE
EXCEL_REPORT_BUILDER_REMOVE_DATE_FORMATS=TRUE
Bruce Lane replied 7 years ago Jared,
That did not appear to resolve the issue. I even created a new Excel Report and I am not still not able to group on a date. The table is the CM Transaction and the Field is TRX Date.
Thanks
Jared replied 7 years ago I should have mentioned in my previous post that this dex.ini line will only work on Build 67 and 68. What build of SmartList Builder are you using? The only other option would be to convert the text date to a date within Excel.
Bruce Lane replied 7 years ago We are on build 58.