Skip to content
+1-888-319-3663

COMMUNITY FORUM

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
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
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.


If you would like to submit an answer or comment, please sign in to the eOne portal.