nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Crosstab - concatenate string

jbaron

Hi everybody,
 
I have a crosstab group by user. One user can have mutiple tasks. Each task contains a company name.
I need to create a new column which contains the concatenation of all companies. Is it possible ?
I can display only the first or last company name.
 
Thanks in advance,

Find more posts tagged with

Comments

pricher

Hi,
 
Maybe this recent <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/39430-concatenating-multiple-string-values-for-a-summary-field-in-cross-tab/'>post </a>will help. It was for a case where strings needed to be concatenated in a crosstab cell. 
 
If that doesn't help, please provide some sample data and a mock up of the expected result.
 
regards,
 
P.

jbaron

Hi Pierre,
 
many thanks for your reply. I fell I'm on the right track.
I have still an issue. I need to create a company name by user and by months. I have a strange result with this expression :
<div>if (vars["groupCol"] != row["nom_prenom"]) {</div>
<div>vars["measure"] = row["company_name"]</div>
<div>vars["groupCol"] = row["nom_prenom"]</div>
<div>} else {</div>
<div>vars["measure"] = vars["measure"] + ", " + row["company_name"]</div>
<div>}</div>
vars["measure"]
 
I attached my designed screenshot, maybe you can help me again :-)
 
Thanks in advance,

pricher

Hello,
 
What is the "strange result" you are getting? Can you attach your report design?
 
P.

jbaron

Hello, 
 
I said strange result, because client don't have the correct value. For example, for the user "Arnaud Demu" for the august month (2016), I need to see CLIENT29 and CLIENT30. Something is wrong with my code. 
 
Many thanks for your help.
 
Julien

pricher

Can you also send the data file?

jbaron

Of course, sorry for this oversight.

pricher

In my example, I tested only on COUNTRY to determine if I had to concatenate the CITY value. In your case, there is also a date dimension, so you need to test as well on username and date.
 
Because I couldn't use directly your data file (the dated in French did not load as dates in my English version of BIRT..), I created a new example based on Classic Models. This time, the concatenation happens whenever COUNTRY or DATE changes. Two caveats: 1) you need to have a "date" column that computes the lowest date level you need in your crosstab (e.g. Year + month); 2) the data needs to be sorted by Username and date.
 
Hope this helps,
 
P.

jbaron

Hello,
 
I have still an issue. I create a new excel file with some sample data in english format for you.
Maybe you can help me again ;-) 
Fo exemple, for this month, clients should be CLIENT29 & CLIENT30, I see only CLIENT30. I don't know why. Do you have an idea ? 
 
Thanks in advance,
 
 
Julien

pricher

Hi,
 
As I mentioned in my previous post, the data needs to be sorted correctly for the computed column to be created properly. In your case, the data needs to be sorted by "nom-prenom" and "YM". If you look at these 4 rows of the excel spreadsheet you sent me, the data is not sorted correctly for YM; therefore, the variable "groupCol" will be reset before it could concatenate CLIENT29 to the string.

jbaron

you're right, but now for YM I change the process. It's a calculated field (BirtDateTime.year(row["YMD"])+BirtDateTime.month(row["YMD"])), integer type. The column is sorted. But for the client the result is the same.

pricher

You don't need to create the YM_11 computed column. The data you have in the Excel file is not sorted correctly to work with the logic needed to create the Clients computed column. If the data in column YM is not sorted, you will not be able to concatenate the strings for each occurrence of group Username/YM. 
 
I am sending you a new Excel file. On the second sheet, I have copied and pasted the data for user Arnaud Demu and resorted the set on column YM. When I use the data from that sheet in the report you sent me, it works!
 
P.

jbaron

haaaaa, I understand now! We don't talk about the same coulmn. It's YMD not YM, which need to be sorted. 
So it's working perfect now if I sort the column in excel file. Do you know if it's possible to sort the input data in birt ? 
I will use a webservice to get the data sort by id.
Thank you very much.
 
Julien

pricher

The data needs to be sorted by the web service. In BIRT, you can sort at the table level, but not at the dataset level. Since the computed column is created at the dataset level and requires data to be sorted by username and date, your web service will need to output the rows in the correct order.
 
P.

jbaron

Thank you for all your assistance ;-)

jbaron

Hello Pierre,
 
I have two news blocking points in this report. Maybe you can help me.
- I would like to hide a colum in the cross tab. With javascript I can do it (display none on th and td) but there is a lag in the table. The first column to the next month is the current month. 
 
- I would like to add a grand total row, but not an all columns, for the customer I don't want to apply the total (and it's a string). The grand total function apply automatically the total for all fields even I revome some total fields.
 
Do you have an idea for this points ?
 
Thank in advance,
 
Julien