Concatenate all records in a DataSet containing HTML

[SandGuru]

I need to concatenate the records of a DataSet containing HTML tags so that they display correctly when generating the PDF. Currently the records are like this by rows:

1. <p style="text-align: center;"><span style="font-family: mceinline;"><strong><span style="font-family: mceinline;"><span style="font-family: mceinline
2. ;"><span style="font-family: mceinline;"><span style="font-family: mceinline;"><span style="font-family: mceinline;">INCAPACIDAD TOTAL O PERMANENTE:</

To just have everything grouped together in a single row like this:

<p style="text-align: center;"><span style="font-family: mceinline;"><strong><span style="font-family: mceinline;"><span style="font-family: mceinline ;"><span style="font-family: mceinline;"><span style="font-family: mceinline;"><span style="font-family: mceinline;">INCAPACIDAD TOTAL O PERMANENTE:</

This needs to be done as the records do not open and close the tags as they should. I tried many ways from DB but had no results so it is a lot of data, can DataCube achieve the result?. I appreciate the help, thank you.

[hvbtup]

You should try BIRT's CONCATENATE aggregate function.

[SandGuru]

Hello, thanks for answering.
I've already used it but it didn't work for me, or maybe I'm using it wrong (?), there are 2000 HTML tag records with long texts

[hvbtup]

There was an issue with concatenate when used with reports created with older BIRT releases, see #838. Maybe you are hitting this bug?

[SandGuru]

Hi, thanks for replying, hmmm I think not, I haven't used that function so I don't think I have that bug yet.

[speckyspooky]

Additional to the suggeston of hvbtup 2 further options:

Option 01 - Loop concat:
You can bind your dataset to an Text-element an concat in the dataset-fetch the text to a standard BIRT-variable. The fetch will be your loop and at the end the variable includes your full xml-stream

Option 02 - MS SQL STRING_AGG:
MS SQL 2017 and higher you can use "STRING_AGG" to goup the data, the column should be use the type VARCHAR(MAX) (=2GB content), e.g.:
SELECT STRING_AGG(xmlstr, '') AS xml_stream FROM ( SELECT '<p style="text-align: center;"><span style="font-family: mceinline;"><strong><span style="font-family: mceinline;"><span style="font-family: mceinline' AS xmlstr union all SELECT ';"><span style="font-family: mceinline;"><span style="font-family: mceinline;"><span style="font-family: mceinline;">INCAPACIDAD TOTAL O PERMANENTE:</' AS xmlstr ) xml_data ;

Result:
<p style="text-align: center;"><span style="font-family: mceinline;"><strong><span style="font-family: mceinline;"><span style="font-family: mceinline;"><span style="font-family: mceinline;"><span style="font-family: mceinline;"><span style="font-family: mceinline;">INCAPACIDAD TOTAL O PERMANENTE:</

With ORACLE you could use "LISTAGG" but it is a little bit tricky due to the char size of 4000 characters etc.

I hope you will find the perfect option for you.

[SandGuru]

Hello, thanks for answering.
What do you mean by the standard birt variable?
I am using DB2, and the STRING_AGG function does not exist. I also tried with LISTAGG and it works perfectly with little data, but I am handling a field with 2000 records.

With XMLAGG it concatenates everything well, but it changes the format (<> to < & >)

[hvbtup]

What he means is a report variable (not a page variable).
You can set and get it in Javascript with the syntax var["var_name"].
Javascript strings are practically not length-limited. So you can initialize the variable to an empty string in the afterOpen event of the data set and append to it in the onFetch event of the data set.

[SandGuru]

Hi, thanks for replying, I have other tasks and was not aware of this issue.
I have already initialized an empty variable, in the OnFetch event of DataSet, I set the column to the variable and tried to used it with an aggregation function using concatenate and it doesn't work.

If you know how to do it, can you help me via Discord or Telegram for faster responses please? I appreciate it!

[hvbtup]

Discord or Telegram
Surely not! That way others would not benefit from the discussion.

[SandGuru]

Surely not! That way others would not benefit from the discussion.
Oh yep, you are right lol

[hvbtup]

You have to be much more precise in your error descriptions. "Does not work" is meaningless.

[SandGuru]

You have to be much more precise in your error descriptions. "Does not work" is meaningless.

Hello. When I refer to "Does not work" basiclly is that I don't get the expected result using the variable, I got no errors.

I started the variable empty, then in the OnFetch event of the DataSet I put the following line:
detailText = row._outer["DETAIL"].concat().

I executed it but no change came up. I also ran it on the cell's OnCreate event but that didn't work either. Maybe the syntax is wrong.

The data is being displayed like this:

And this is how they should look like interpreting HTML tags:

[speckyspooky]

There are 2 point wich should be changed.
At first the concat isn't correct because you overwrite every loop your fetched and store value of the variable.
original:
detailText = row._outer["DETAIL"].concat().

new - long notation:
detailText = detailText + row._outer["DETAIL"]

new - long notation:
detailText += row._outer["DETAIL"]

The second point ist the display in the report should be done with the element of "Dynamic Text".
Because BIRT parse the whole data of standard data elements or "Text" so your e.g. <> will be replace through HTML-entities < >
and the HTML will not displayed in your way..

The dynamic text will avoid the parsing.

[SandGuru]

Hello, I changed it and now it display this:

It is concatenating, displaying and then concatenating, displaying the concatenated and so on, repeating the same information and adding new information.

Yes, I was using Dynamic Text to interpret HTML tags.

[speckyspooky]

It looks much better and you get all information like HTML-Text.
The reason why you see the text repeated is that your dynamic text will be repeated through the loop-detail-row which works like a loop.

Now you have 2 options, A) usage table footer B) full usage of "Dynamic Text" with direct binding.

Option A) set the "Dynamic Text" to the footer line and remove the detail rows. In this case you get the text only one times.

Option B) Set the "Dynamic Text" outside of the tabel and add the "data set" directly on the "Dynamic Text"-element.
The binding is the trigger to execute the data set and the string concatination and the dynamic text display only one times your value.

Very small (simulated) example on my screen:

[wimjongman]

@speckyspooky Thanks!! Can you put this in #1040 (reply in thread) in a separate answer (not reply)? Then we can accept it as the answer.

[speckyspooky]

It looks much better and you get all information like HTML-Text.
The reason why you see the text repeated is that your dynamic text will be repeated through the loop-detail-row which works like a loop.

Now you have 2 options, A) usage table footer B) full usage of "Dynamic Text" with direct binding.

Option A) set the "Dynamic Text" to the footer line and remove the detail rows. In this case you get the text only one times.

Option B) Set the "Dynamic Text" outside of the tabel and add the "data set" directly on the "Dynamic Text"-element.
The binding is the trigger to execute the data set and the string concatination and the dynamic text display only one times your value.

Very small (simulated) example on my screen:

[SandGuru]

Definitely not, it does not work.

If I use option A, at the time of putting the dynamic text in the table footer, it generates all blank pages.

If I use option B, the same thing happens (all blank pages), and I have always had the dataset binded.

I have also tried with the for loop in the OnFetch event to concatenate in different ways and all pages are generated blank.

[hvbtup]

Both solutions described by @speckyspooky should work (as well as the most straightforward solution to use the CONCATENATE funtion I described).
Obviously your report contains some errors you don't understand.
You should upload the rprtdesign file (or a simplified version of it) here to let us see what you tried.

[speckyspooky]

Attached you will find an example report based on the ClassicCarModel.
I have added 2 examples the variable-concat-version and also the option of @hvbtup with the aggregation function.

I hope it will help you. If not we would need your example mentioned like before on the comments.

concat_report.zip

string-HTML-concat-example.pdf

[SandGuru]

Hello, this afternoon I was able to solve my problem. Thank you very much for the support you have given me, maybe it can work for others, but not for me, but I have learned some new things.

The solution I did was in database, directly from the Stored Procedure. I had to use a loop to go through the records and concatenate them in a variable also I had to use the CLOB data type to store < 32767 bytes. Here I will leave a SQL similar to what I did if it helps anyone:

CREATE PROCEDURE P_SP_LOOP(
    OUT P_CONCAT VARCHAR(500)
)
SPECIFIC P_SP_LOOP
LANGUAGE SQL

P1: BEGIN

    DECLARE LN_IDX DECIMAL(10,0);
    DECLARE LN_COUNT DECIMAL(10,0);
    
    SET LN_IDX = 1;
    
    SELECT COUNT(1)
    INTO LN_COUNT
    FROM C_CITY;
     
    INSERT INTO WFSWOG.TEMP_LOOP
    SELECT ROW_NUMBER() OVER(ORDER BY NAME ASC), NAME 
    FROM C_CITY
    ORDER BY NAME ASC;
    
           
    SET P_CONCAT = ''; 
             
    WHILE(LN_IDX <= LN_COUNT) DO
        
        SET P_CONCAT = P_CONCAT || (SELECT CIUDADES
                                    FROM TEMP_LOOP
                                    WHERE TEMP_CICLOS_ID = LN_IDX);
        
        SET LN_IDX = LN_IDX + 1 ;

    END WHILE;

END P1

[speckyspooky]

Great to see your reply and that you used the PL/SQL option ;o)
One problem and multiple ways to solve it.

I think you know it in addition to your comment, your example the string maximum of the example is 500,
the maximum of PL-variables is length 32767 and afterwards CLOB is necessary.
The handling of CLOB needs somtimes a little bit more handling of correct work but it works.
(I use it in other cases too.)

Have a nice day!

[TheLaserNinja]

Little late to this party, but if the CONCAT aggregation doesn't work, then declare a global variable in the initialize or beforeRender event as a Packages.java.util.StringBuilder, then in the onFetch event for the data set, append your string. Once the data set has been iterated over completely, call the StringBuilder.toString() method.