Reports - data in spreadsheet needs to be sortable according to data type

[davroth]

Data types in report need to be written to spreadsheets in a way that allows them to be sorted by type of data:

Voltage should be a number (it is currently written to Excel as text) Signal Strength should be a number (currently written to Excel as text) Date needs to be sorted as date/time (currently is sortable, but a sort warning dialog is displayed that will confuse some of our users)

[peters95]

+1

[davroth]

Repro:

1. Browse to Omnistaging, IE, Windows 8.1, login in as Dave, select Atlantic Mech, set time filter to last 3 days, then ran a report
2. I opened the report in Excel, then clicked Excel's data tab
3. I selected all data in the spreadsheet.(clicking upper left box) and then clicked Sort in the ribbon bar
4. I selected "My data has headers" box and set sorting to be on Voltage

Actual - a "Sort Warning" popup as in the picture below

Expected - Excel to sort the numeric data. This is the DMResolved.BatteryLevel property which is defined as an int, so the property is being converted to text unnecessarily and customer is more likely to be confused.

[peters95]

I added this code into Report.cs:

                    if (column.DataType == System.Type.GetType("System.String"))
                    {
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    }
                    else if (column.DataType == System.Type.GetType("System.Int32"))
                    {
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                    }
                    else if (column.DataType == System.Type.GetType("System.Boolean"))
                    {
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean;
                    }
                    else if (column.DataType == System.Type.GetType("System.DateTime"))
                    {
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Date;
                    }
                    else
                    {
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Error;
                    }

which should have set the datatype in the excel spreadsheet based upon the datatype of the column from the database result set. however it appears when i debug that all items are coming back as "system.string" from the database which is resulting in the same behavior as before.

[peters95]

I've tried now to code this up a code different ways everything has an issue. By forcing it to be a number it actually is causing issues in the excel sheet. Attaching example of when "str signal" is marked number and how excel opens with errors now. OmniReport (5).xlsx

[peters95]

my recommendation is not to move forward on this item especially w/ Gary's item of revamping the reports. this doesnt seem to be easy to obtain w/ limited gain in my opinion. If a power excel user really wants it to be a number of a chart or graph they would know how to click on the column right click and change the format to number but i can continue on this item if it is deemed important enough just let me know.