XMII 12.1 - Grid Behaviour - Date format Issue.

[Former Member]

Hi,

In our application query is returning data in the format 'dd-mm-yyyy'. In display template also we've set the Date format as 'dd-mm-yyyy-.

This is working fine on display. But when I export the grid data to excel, if the date is greater than 9 then its displaying it in 'dd-mm-yyyy' format otherwise it is displaying the date in 'dd/mm/yyyy' format.

I am not sure from where it is taking the 'dd/mm/yyyy' format.

Any help is appreciated.

Thanks,

Nishad.

[sidnooradarsh]

Hello,

What MII version are you running? I mean the patch level?

Try passing the Date as string from MII and check how it behaves after exporting in CSV.

Regards,

Adarsh

[Former Member]

Hi Adarsh,

We are passing date as char using

to_char(colname, 'dd-mm-yyyy hh24:mi:ss').

The results of query are proper.. And the date format in the display grid should not matter, because we are treating even the date as string. It should display the string as it is coming.

MII 12.1. Not sure about the patch.

Thanks,

Nishad .

[Former Member]

May be its not an MII Issue..May be excel is formating the cells based on the data.

Is it possible ? Because On Grid the data is displayed properly. Also, saveAsCSVFile() exports the data as it is without manipulations ?

Thanks,

Nishad.

[sidnooradarsh]

Hello,

Yes excel does some kind of formatting.

Check this thread, this is related to data formatting after exporting to CSV file.

[Thread|]

Hope this helps!!

Regards,

[Former Member]

Hi Adarsh,

Trick is good, but how do i do it from grid export ? Grid is displaying proper data. If I do ="string" then it will also show that in the grid which the business wont accept.

Thanks,

Nishad.

[sidnooradarsh]

How about create a Hidden column which you don't show on Grid?

[Former Member]

Hi Aadarsh,

We already have around 50 columns on the screen. And also facilitated the used to Show/Hide the columns from the display grid. These hidden columns are saved in the database.i.e. each user have his own customized view on the grid.

Also we have around 20 date columns in the grid. So we will have to create 20 hidden columns again which would complicate the things even more.

Thanks,

Nishad.

[sidnooradarsh]

How are users generating CSV file? I mean are they doing right click on Grid and then click "Save as CSV file"?

I am just trying to think of other options to generate csv....

Say that if you have provided an external option like button to generate CSV then in that case you can call another Query template which will generate resultset in the format required for csv.

[Former Member]

Yes Adarsh. We are giving a button for users to export the data in the grid using document.iGrid.saveAsCSVFile();.

Is it possible to save the output of query to save directly as CSV/Excel file ?

Nishad.

[sidnooradarsh]

Ok, in that case on click of button why not trigger another hidden grid applet to bring data in your desired format then do saveAsCSVFile() on hidden grid.

I am not sure about whether you can save directly the CSV/excel file. I think its not possible we get an option for save or cancel.

Regards,

Adarsh

[Former Member]

Adarsh,

The query is very complicated. We are generating the query first, by getting the hidden columns for the current user, column sequences, and filter conditions if any are specified by the current user. This query is then fired on the database.

Also, data is huge around 2000 records with 50 columns each. Its taking almost 1-2 minutes to fully load the data in the grid.

Population of same data in the hidden grid will again add to the total time for page load and user wont accept it.

Nishad.

[agentry_src]

Hi Nishad,

Does your complicated query involve multiple joins of database tables? If so, you may be better off bulding a custom view/user function/stored procedure (I usually stick with the custom view. It is easier to get them built or get permission to build it yourself.) in the database and posting your queries against that. You should see a significant performance improvement. If that is still too slow, have a table populated with your query data may give you another boost in performance. It depends upon your database, query structure, and amount of data. Also review the number of columns in your result set.

If you are displaying the data in a grid and have 2000 records and 50 columns in your query, try to eliminate as many of those hidden (and/or unused) columns. And I would also make sure you really need all 2000 records from your initial query. You may want to limit your query to a smaller dataset intially and then allow the user to decide whether they need a more complete list (give them additionaly filtering options on date or some other criteria).

In general, push as much of the processing back to the database. It is better setup to do it efficiently.

Another possibilty is to have a scheduled job run to make a MII formatted copy of the query data in a file in MII on a frequent basis. Displaying the data would be much simpler and faster.

Good luck,

Mike