How to get item descriptions with line breaks into a neat report?

[UweStieglitz]

When you pull an LMS standard report or PRD custom report which includes item descriptions and audience descriptions, then the result in csv is distorted, if the descriptions contain line breaks or line feeds. 

Do you have any suggestions how to fix this? 

Thanks, Uwe

[UweStieglitz · Accepted solution]

Dear All,

I think I can offer the solution now.

"pa_cpnt.cpnt_desc" returns the ID of the description.

The locale value of this ID is stored in table "PA_I18N_LOCALIZED_LABEL". It can be retrieved like this:

------------------------------------------

SELECT DISTINCT 

--- the following looks for normal cpnt_desc ID in pa_cpnt table

-- to my surprise it is necessary to include this in the select, if this call is missing, the scripts looking for the localized values return empty

pv.CPNT_DESC,

-- the following looks for the english localization of the description

LocDescEn.label_value

FROM 

pa_cpnt pv

    LEFT OUTER JOIN PA_I18N_LOCALIZED_LABEL LocDescEn
       ON LocDescEn.LABEL_ID = pv.CPNT_DESC AND LocDescEn.LOCALE_ID = 'English' 

--------------------------

Once the localized value is retrieved, it can be easily cleaned with sql:

-- the following looks for a cleaned version of english localization of the description
REPLACE(REPLACE(LocDescEn.label_value, CHAR(10), ''), CHAR(13), '')

Same logic works for item title and item target audience fields.

Below you find a case, where I first look for the english description and if this is null, then I use the german one:

-------------------

CASE
WHEN LocDescEn.label_value is null
THEN
CASE
WHEN LocDescDe.label_value is null
THEN 'Currently no description available'
Else REPLACE(REPLACE(LocDescDe.label_value, CHAR(10), ''), CHAR(13), '')
End
ELSE REPLACE(REPLACE(LocDescEn.label_value, CHAR(10), ''), CHAR(13), '')
END as "Localized Description Primary English"

Hope this helps...

Thanks, Uwe

[guidoklinger]

Hi Uwe,

there is no solution in SFL. We have the same problems with all textfields which are used in SFL. Not only the textfields in elements and classes. Also the comment-fields during the registration process. The only way at the moment is that admins follow the rules: not to use any character like * "" ; , | and not to make a break with "enter". But admins forget often the rules and then reports are for the bin again. 

Regards
Guido

[nishadsthakur]

line breaks caused by special characters, usually item descriptions are copied from microsoft word it will create those type line breaks, I also ran into this situation and tried everything to eliminate it from sql-code but it wont work
only way is to rework on description inside LMS.
another workaround involves background elements.
for my scenario, i want to send custom report to linkedin sftp, to achieve that i created custom prd which sends item data(id,type,dte,desc) along with format of live data import templates(^userid,^assigmentid,lms_background_element)

then i created integration centre report which removes multiple special characters and again sends the data to new background element

now I schedule report for linkedin using newly created background element using integration centre
if you want to only create report for user/admin consumption then you can report using stories or ord

[UweStieglitz]

Hi Nishadsthakur, Thanks for this long explanation. Today I tried again to find a solution for this problem. I asked ChatGPT for ideas. And it suggested several ways to work with JavaScript in BIRT. I tried them all and they all failed. :-(. Now I will take up your hint and talk to some of my colleagues, if the integreation center might be able to help here. Or - next idea triggered by your reply - I will ask the middleware colleagues (we use CPI), if CPI can pick up my report and remove any special characters...