Bizzare Bex Report: Concatenating multiple rows in a single cell
How hard is it for Wally to abstain himself from igniting a new pandemonium every day? An arsonist would be less of a pain to bear- at least he will kill us all in one single day.
What does he want this time? He wants us to group all the plant inventory, contract price of a particular material in a single row.
i.e the report layout should look like this:
Material |
Plant |
Price |
10098 |
1002;1003;2009;3008;3009 |
100$,97$,95$,101$,99$ |
What we get from a bex report is
Material |
Plant |
Price |
10098 |
1002 |
100$ |
|
1003 |
97$ |
|
2009 |
95$ |
|
3008 |
101$ |
|
3009 |
99$ |
Here is how to do it:
Write an APD> extract this query> select the fields from the query object to the source fields of the routine object (it_source+ is the internal table that will hold the data from the query),+ then write a logic that groups the Plant field and The price field in a single variable of type string, separated by “;”, and put it in a new internal table where the concatenated fields are defined as a String–> Then use a Function module to extract this new internal table to the presentation server as a xls file.
(note: you will have to use a terminating/target block in your APD after the routine block; I suggest you use a dummy data target block for writing to a file)
If you don’t want to use an APD- just extract the query to a transparent table using RSCRM_BAPI.
Then write a ABAP report with select statement on this table and construct the internal table according to the your logic.
The table name will be generated with the following convention /BIC/0C<technical name of your extract>
(PS: Be careful when you are doing typecasting; you will basically be concatenating all values in a string variable inside each loop)
Cheers,
- Ali Q.
Thanks 🙂