Keep line breaks when exporting to CSV/XLSX when using carriage returns via Oracle function LISTAGG

Diwanshu Khatri shared this idea 48 days ago
Idea Logged

Hello Team,

We would like to know how Yellowfin BI in the excel export can understand <br /> as a carriage return? When it's viewed in the UI the behaviour is how you would expect, however, when exporting to CSV and XLSX the formatting does not carry across.

With the Text Replacement function, it doesn't work for our customers. It seems they are using LISTAGG oracle function to create these fields.

When exporting to CSV or XLSX the line break is lost.

Best Regards,

Diwanshu K

/e4c626cbb679b790623255af0623b40e


/91cb03369bb3d9d8b20e6d8f5b3d6701

Comments (13)

photo
1

LISTAGG oracle function is used to create these fields.


For Linked Requests

(SELECT Request_ID02,

LISTAGG(Request_Description01,'<br />') WITHIN GROUP (ORDER BY Request_ID02) AS description

from ( select Request_ID02, Request_ID01, Request_Description01,

CAST (DBMS_LOB.SUBSTR(Request_Description01,1500,1) AS VARCHAR2 (3900 BYTE))as Description

from WOI_Associations where Request_Type01 in ( 9000, 12000, 22500, 33000))GROUP BY Request_ID02)


For Request Activity Log

(SELECT Request_Number,

LISTAGG(to_char(EIB_UNIX_TS_TO_DATE(WorkInfoSubmitDate),'DD/MM/YYYY') || ' - ' || coalesce(Full_Name, ' ') || ' - ' || coalesce(Notes, ' '),'<br />') WITHIN GROUP (ORDER BY Request_Number) AS description

from ( select Request_Number,Full_Name, CAST (DBMS_LOB.SUBSTR(Notes,1500,1) AS VARCHAR2 (3900 BYTE))as Notes, WorkInfoSubmitDate from SRM_WorkInfo where View_Access = 1)


GROUP BY Request_Number)


How Yellowfin in the excel export can understand <br /> as a carriage return ?


Thanks,

Diwanshu

photo
1

Hello Team,

Any update on this please?

As per our understanding, the export functionality is as per design of Microsoft office format. Yellowfin sends HTML rendered data to MS office. However, MS office resets some of the formatting values and so the sizing is not retained.

Having said that, multiple lines data too works as per the underlying export functionality of MS Office.

Appreciate your quick response!


Thanks,

Diwanshu K

photo
1

HI DIwanshu,

Thanks for reaching out to support.

The mentioned task has been completed and is set to be included in 8.0.10 and 9.6 releases.

However it sounds like you are requesting an additional option to CR and LF, in this case <br / >, is that correct?

And the desired output is to MS Excel as opposed to CSV?

If so I can create a developer task to look into potentially including this option in a future release.

Thanks,

Eric

photo
1

Hello Eric,

Thanks much for your update.

Please be infirmed that we were able to achieve this from application console using LISTAGG function as mentioned above and yellow displays the expected data on console, however the issue is while exporting the same to Excel.

I would request you to review the email attached to this ticket earliest.

We just need to understand how Yellowfin BI in the excel export can understand <br /> as a carriage return ?

Please feel free to contact me in case of any concern. Thank you!


Best Regards,

Diwanshu K

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hi Diwanshu,

There is no email attached to this ticket, could you upload to our ftp https://ftp.yellowfin.bi using the "send files" option?

Are you able to provide a source DB with dummy data for this query to work? Or something similar?

Thanks, Eric

photo
1

Hello Eric,

I can see that a zip file attached to the case wherin you have the attached email from end Customer, I am not sure why you were not able to see it-

Also, I have uploaded the zip file on the FTP site- (xxx.7z)


Are you able to provide a source DB with dummy data for this query to work? Or something similar?-- Sorry but we won't be able to provide source DB with dummy data, but attached files has email and excel wherein you will get more details about the issue and use-case.

Please feel free to contact in case of any query.


Thanks,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hello Eric,

We would just like to know how Yellowfin BI in the excel export can understand <br /> as a carriage return ?

Its working fine using LISTAGG function on application console, the problem is export the data to Excel.

Please refer attachments uploaded to FTP and update us at the earliest.


Thanks,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hello Eric,

Any updates on this topic please. Its just a confirmation in regards to the export functionality with respect to carriage return ?

We would just like to know how Yellowfin BI in the excel export can understand <br /> as a carriage return ? Its working fine using LISTAGG function on application console, the problem is export the data to Excel.


Thanks,

Diwanshu

photo
1

Hello Eric,

Any further updates on this topic please?

Customer mentioned that this issue is observed in CSV and in PDF as well. We are still awaiting for your feedback here.


If possible, please assign this to someone working as per our working hours- 12.30 PM - 9.30 PM IST.

Thanks,

Diwanshu

photo
photo
1

Hi Diwanshu,

I have created a Developer task to look into this issue. Updates to the task will be provided as they are available.

Thanks, Eric

photo
1

Hello Eric,

Thanks much for creatig a developer task. May I please request you o share the details or Taskid for reference.


Best Regards,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
photo
1

Hi Diwanshu,

I will also pass this to the appropriate region for a call.

Thanks,

Eric

photo
1

Hello Team

Any news on this please. May I please request you o share the details or Taskid for reference.


Best Regards,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hi Diwanshu,


Thanks for your message. While the Developers look into this, I've had a look around and it seems that the HTML code <br> is not synonymous in Excel. The solution to this issue might be to replace the <br> with 'CHAR(10)'.


Let me know if this helps.


Kind regards,

Simon

photo
1

Hi Simon,

The replacement by CHAR(10) doesn’t work. I have tested before to open the ticket.

I tested again to be sure.

/0683n00000CNnkWAAT

It doesn’t take into account the carriage return.


Request you to share the details or developer taskid for reference and help us to address this issue asap.


Best Regards,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
photo
1

Hi Diwanshu,


This task has been raised with Developers as YFN-22574, where we have confirmed replication steps and passed it onto Developers for assessment as an Enhancement Request.


Before becoming an Enhancement, Ideas will be reviewed by our Product Team and chosen for Enhancements based on feasibility, level of positive impact to the user experience, and votes from the community.


I will reach out to you once I hear any updates.


Kind regards,

Simon

photo
1

Hello Simon,

Thanks much for your update. Glad you were able to replicate the issue.


Please keep us posted with the Next plan of action or solution for this issue. Thank you!

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hello Simon,

It seems that a Defect is been logged for this issue. Could you please share the Defect Id along with the details and also confirm when this is planned/ targeted to be fixed ?

Please let us know if their is any workaround for this issue currently. Awaiting for your feedback. Thank you!


Best Regards,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hi Diwanshu,


The task ID is listed above, YFN-22574.


I will reach out when I hear more from the Developers. At this stage the issue is yet to be assessed.


Kind regards,

Simon

photo
1

Hi Simon,


Thanks for your update, however I can't access this task.

https://community.yellowfinbi.com/ticket/22574

404 Error - Page not found or moved!


I will wait for your feedback once you get response from Developers. Thank you!


Best Regards,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hello Simon,

Hope you are doing well.


Can you help me with the further updates on this issue please?


Thanks,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hi Diwanshu,

Simon is out today but I wanted to jump in to let you know that the task ID YFN-22574 is an internal reference ID only and there is not a public facing version of this. Any updates on the task will be posted here as it is assessed. At this point in time it is still under review.

Cheers,

Neal

photo
1

Hello Neal,

Thanks much for the update. May I please request to expedite on it with your internal team as Customer is still waiting for technical feedback from yellowfin end.

Thank you!


Best Regards,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hi Diwanshu,


Just jumping back in here.


In order to escalate an issue like this, you will need to raise this with Manish from your side at BMC who will then discuss it with stakeholders from Yellowfin, amongst other BMC priorities.


To add to this, Developers have assessed this issue and determined it to be an Enhancement Request rather than a Defect, as it is a request for an extension of existing functionality and not defective behaviour.


In the meantime, I will keep you updated on this post with any updates on the progress on this matter.


Kind regards,

Simon

photo
1

Hello Simon,

Thanks much for your update and response on this topic.

Sure, if Customer escaltest this further, will raise it with Manish and who can take it ahead with stakeholders from Yellowfin as per the process.


As of now, will inform Customer that this is determined to be an Enhancement request and we are awaiting further updates form yellowfin developers and shall update you as soon as we get a response and confirmation here.

I hope Developers were able to replicate this the similar as below-

These 2 fields Request Activity log and Linked requests are a result of sql command.

We used the LISTAGG oracle function to create these fields.

f56ba041366db81297084585f847d9ae

For Linked Requests

(SELECT Request_ID02,

LISTAGG(Request_Description01,'<br />') WITHIN GROUP (ORDER BY Request_ID02) AS description

from ( select Request_ID02, Request_ID01, Request_Description01,

CAST (DBMS_LOB.SUBSTR(Request_Description01,1500,1) AS VARCHAR2 (3900 BYTE))as Description

from WOI_Associations where Request_Type01 in ( 9000, 12000, 22500, 33000))GROUP BY Request_ID02)


For Request Activity Log

(SELECT Request_Number,

LISTAGG(to_char(EIB_UNIX_TS_TO_DATE(WorkInfoSubmitDate),'DD/MM/YYYY') || ' - ' || coalesce(Full_Name, ' ') || ' - ' || coalesce(Notes, ' '),'<br />') WITHIN GROUP (ORDER BY Request_Number) AS description

from ( select Request_Number,Full_Name, CAST (DBMS_LOB.SUBSTR(Notes,1500,1) AS VARCHAR2 (3900 BYTE))as Notes, WorkInfoSubmitDate from SRM_WorkInfo where View_Access = 1)

GROUP BY Request_Number)


How Yellowfin BI in the excel export can understand <br /> as a carriage return ?


Thanks,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hello Simon,

It seems an Idea has been logged for this issue. May I please request you share more updates if any.


Thanks,

Diwanshu

photo
1

Hi Diwanshu,


There are no further updates at this stage.


I will reach out when there are any changes.


Kind regards,

Simon

photo
1

Hello Simon/ Team,

May I please request you to share the ticket https://community.yellowfinbi.com/ticket/22574 with Tarun Pandey xxx@bmc.com and xxx@bmc.com


Our RnD team needs access for tracking purpose from BMC end. Thank you!


Best Regards,

Diwanshu K

photo
1

Hi Diwanshu,


I have added Tarun to this ticket. Please note that YFN-22574 is an internal task ID.


Kind regards,

Simon

photo
1

Hello Simon,


Thanks for the update. Any further news on this from developers?


Best Regards,

Diwanshu

photo
1

Hi Simon,

It seems Tarun still don't have access to the ticket

https://community.yellowfinbi.com/ticket/22574


Can you please check and confirm. Thank you!


Best Regards,

Diwanshu

photo
1

Hi Diwanshu,


When there are updates from the Developers, I will update this post appropriately.


The URL for this ticket is https://community.yellowfinbi.com/topic/export-a-report-in-excel-doesnt-keep-the-data-format-in-the-column-as-a-carriage-return, and it is a public post. As we mentioned earlier, the internal ID used by Yellowfin and our Developers is YFN-22574, this is for your reference, however, you are unable to access it.


Kind regards,

Simon

photo