This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
So we recently upgraded to 7.6 and found an issue with the QBR data export function. We use QBRs heavily and analyze the data in csv / xls formats. We found that when we select a date column in the QBR and export it to a spreadsheet, the date format is Mmm DD,YYYY. This format cannot be sorted chronologically. We have tried everything we know to get it to work. And no, you cannot even change the data format of the excel column to fix it, for some strange reason. This is very frustrating!
We have found one workaround that we have been using - the calculation function of QBR allows you to select the data column and re-format it as MM/DD/YYYY. However, this only works if the originator knows ahead of time that date sorting is needed, knows this workaround, and takes the extra time to do it!
Does anyone have another way to overcome this QBR data format export problem? This issue did not exist in our previous version, 7.1.1.6.
Any help is appreciated!
Bill Murphy
MAM v7.6.0.6 / Weblogic app server / Oracle 12.1 DB / Linux server OS
If you can't get it to export in a format that works for you then create a
new column and use this function to convert MMM dD,YYYY to an Excel date:
=DATEVALUE(TRIM(SUBSTITUTE(MID(A1,4,3),","," "))&LEFT(A1,3)&RIGHT(A1,4))
This assumes your date is in cell A1.
On Thu, Mar 22, 2018 at 9:36 AM, bill.e.murphy@grifols.com [MAXIMO] <
MAXIMO@yahoogroups.com> wrote:
>
>
> So we recently upgraded to 7.6 and found an issue with the QBR data export
> function. We use QBRs heavily and analyze the data in csv / xls formats. We
> found that when we select a date column in the QBR and export it to a
> spreadsheet, the date format is Mmm DD,YYYY. This format cannot be sorted
> chronologically. We have tried everything we know to get it to work. And
> no, you cannot even change the data format of the excel column to fix it,
> for some strange reason. This is very frustrating!
>
>
> We have found one workaround that we have been using - the calculation
> function of QBR allows you to select the data column and re-format it as
> MM/DD/YYYY. However, this only works if the originator knows ahead of time
> that date sorting is needed, knows this workaround, and takes the extra
> time to do it!
>
>
> Does anyone have another way to overcome this QBR data format export
> problem? This issue did not exist in our previous version, 7.1.1.6.
>
>
> Any help is appreciated!
>
>
> Bill Murphy
> MAM v7.6.0.6 / Weblogic app server / Oracle 12.1 DB / Linux server OS
>
>
>
>
>
IF the user has the option on their pc (or whatever they are downloading to), have then choose the option 'Microsoft Excel 97 - 2003 Worksheet' for SAVE AS TYPE and the dates come across OK.
Pat Morrow
pmorrow8@yahoo.com
On Thursday, March 22, 2018, 8:40:06 AM MDT, bill.e.murphy@grifols.com [MAXIMO] <MAXIMO@yahoogroups.com> wrote:
So we recently upgraded to 7.6 and found an issue with the QBR data export function. We use QBRs heavily and analyze the data in csv / xls formats. We found that when we select a date column in the QBR and export it to a spreadsheet, the date format is Mmm DD,YYYY. This format cannot be sorted chronologically. We have tried everything we know to get it to work. And no, you cannot even change the data format of the excel column to fix it, for some strange reason. This is very frustrating!
We have found one workaround that we have been using - the calculation function of QBR allows you to select the data column and re-format it as MM/DD/YYYY. However, this only works if the originator knows ahead of time that date sorting is needed, knows this workaround, and takes the extra time to do it!
Does anyone have another way to overcome this QBR data format export problem? This issue did not exist in our previous version, 7.1.1.6.
Any help is appreciated!
Bill Murphy
MAM v7.6.0.6 / Weblogic app server / Oracle 12.1 DB / Linux server OS
Thanks for the quick response. I would love for a solution like this to work! Most users are still on Win7 / IE / Office 2010 but we are migrating to Win10 / IE / Office 2016.
That being said, this use case did not work:
1. Generate QBR and select "Export Data"
2. Only csv export format is offered in Maximo Export Data dialog box, click OK
3. When the IE Open / Save / Save As box appears, I choose Save As, but only csv offered again
4. Whether before or after I save the csv as Excel 97-2003, the column is still not sortable in chrono order
I have tried a few other save / save as options involving Excel and Notepad, to no avail. Can I contact you directly to make sure I am not doing something out of sequence (my email is bill.e.murphy@grifols.com)? Although most steps are set by dialog box pop-ups. I also had a colleague try on his Win10 / IE / Office 2016 box with no success.
Thanks.
SAVE the report and then Schedule the report to run in 5 minutes and email it to yourself. It comes in as an email attachment and then you can open in Excel and sort.
See screen shot that is attached for parameters in scheduling report. You can send to multiple people - email list is comma separated.This also works for sending reports on a regular basis (daily or weekly or even hourly - depends on you business processes), just choose the Recurring option and schedule it. (Of course, that is assuming that report is set up properly and picks records based on changing criteria.)
Pat Morrow
pmorrow8@yahoo.com
On Thursday, March 22, 2018, 1:06:23 PM MDT, bill.e.murphy@grifols.com [MAXIMO] <MAXIMO@yahoogroups.com> wrote:
Thanks for the quick response. I would love for a solution like this to work! Most users are still on Win7 / IE / Office 2010 but we are migrating to Win10 / IE / Office 2016.
That being said, this use case did not work:
1. Generate QBR and select "Export Data"
2. Only csv export format is offered in Maximo Export Data dialog box, click OK
3. When the IE Open / Save / Save As box appears, I choose Save As, but only csv offered again
4. Whether before or after I save the csv as Excel 97-2003, the column is still not sortable in chrono order
I have tried a few other save / save as options involving Excel and Notepad, to no avail. Can I contact you directly to make sure I am not doing something out of sequence (my email is bill.e.murphy@grifols.com)? Although most steps are set by dialog box pop-ups. I also had a colleague try on his Win10 / IE / Office 2016 box with no success.
Thanks.
I just came across this technote with a solution for this issue. Disclaimer - I have not tried it.
IBM Issue with Date Format in Excel when you use the download option from the List tab. - United States
|
|
| |
IBM Issue with Date Format in Excel when you use the download option fro...
Why when I use the download option from the list tab in my application the format displays different in Excel?
|
|
|
To solve this issue you need to change the following system property -
webclient.downloaddatetimeastext
If your property is set to 0, Then go ahead and change it to 1.
1) Go to System Configuration > Platform Configuration > System Properties
2) In the "Global Properties" find for webclient.downloaddatetimeastext
3) Expand the row and into the "Global Value" field change the value to 1
4) Save
5) Select the row and click "Live Refresh" action
Pat Morrow
pmorrow8@yahoo.com
On Thursday, March 22, 2018, 1:06:23 PM MDT, bill.e.murphy@grifols.com [MAXIMO] <MAXIMO@yahoogroups.com> wrote:
Thanks for the quick response. I would love for a solution like this to work! Most users are still on Win7 / IE / Office 2010 but we are migrating to Win10 / IE / Office 2016.
That being said, this use case did not work:
1. Generate QBR and select "Export Data"
2. Only csv export format is offered in Maximo Export Data dialog box, click OK
3. When the IE Open / Save / Save As box appears, I choose Save As, but only csv offered again
4. Whether before or after I save the csv as Excel 97-2003, the column is still not sortable in chrono order
I have tried a few other save / save as options involving Excel and Notepad, to no avail. Can I contact you directly to make sure I am not doing something out of sequence (my email is bill.e.murphy@grifols.com)? Although most steps are set by dialog box pop-ups. I also had a colleague try on his Win10 / IE / Office 2016 box with no success.
Thanks.