Block output

[Former Member]

Hi all,

I have 2 dimensions: [Title] and [Category]

Title	Category
A	Cat01
A	Cat02
B	Cat01
B	Cat03

i want to output as follow:

Title	Category
A	Cat01, Cat02
B	Cat01, Cat03

How can i achieve this?

Thanks in advance!

[Former Member]

Hi,

I come up with another solution.

I made this at universe's level (data foundation).

I created derived table List of categories on data foundation.

SELECT TITLES.TITLE, LISTAGG(CATEGORIES.CATEGORY, ',') WITHIN GROUP (ORDER BY CATEGORIES.CATEGORY)  as Categories

FROM CATEGORIES LEFT JOIN TITLES

ON TITLES.MAT_ID = CATEGORIES.MAT_ID

GROUP BY TITLES.TITLE

However, it caused an error "ORA-01489: result of string concatenation is too long"

Anyone know how to solve this error?

[Former Member]

anyone help me please!

[Former Member]

Hi Danh Chau,

Please find the formula below

Create a variable   VAR Max Category =Max([catg])In ([title])

Create a variable [VAR Concat Category] =[catg] +", "+ Previous(Self), to concatenate the category value with it's previous row value

VAR Category==If(IsNull(Previous([VAR Max Concat Category]));Substr([VAR Max Concat Category];1;Length([VAR Max Concat Category])-2);Substr([VAR Max Concat Category];1;Pos([VAR Max Concat Category];Previous([VAR Max Concat Category]))-3))

it worked for me.

even u can follow the below link

bi.scribe: Converting rows into a single cell (comma separated list) in WebI

hope this helps you out!!!

[Former Member]

Hi Sushma,

I already tried it a few days ago. It worked fine for the first 15 pages and got #OVERFLOW for the the rest of page!

[Former Member]

Hi Danh,

I think the size limitation of the string is set which one cannot change.

[Former Member]

Hi Danh,

Just to know the length of the string could you please try this

=Length(Replace(ReportFilter([VAR Category]);";";""))

[sateesh_kumar1]

Hi,

I know it is not good idea,have a look if you are interested.

Ex : I have to show plant wise materials in row.

1.Create a cross tab .plant in rows 2 times (1 for section .1 to show) , Material in column and in body

=max([Material] for each([Plant])) .

using any aggregate functions makes your dimension data fit to rows.

Hide top header

2 .Apply section on plant

Hide Material header row

3.delete plant cell .

4.adjust section rows (cell any row ,format table : vertical 0 to top of section)

5.Format cell . make left and right padding's to 0 . Remove borders .you can append ";" if you need.

Final result

[Former Member]

Hi Sushma,

i inserted a column on the right and create a formula

=Length(Replace(ReportFilter([VAR Category]);",";""))

but i just got #OVERFLOW only.

[Former Member]

Hi ,

It means that you data is beyond the limit.

its unable to display large data string

[Former Member]

Hi ,

I placed just 500 rows of data ,then its able to display.

[Former Member]

Hi Sateesh Kumar,

your solution aims to display 2 columns/dimensions.

In my requirement, I actually have to display more besides the 2 columns above.

[Former Member]

Hi,

You can do it like this;

=[Category]+", "+RelativeValue([Category];([Category]);+1)

Regards,

Utku

[Former Member]

your formula does not populate the right result.

There is no concatenation between the different categories of the same title.

[Former Member]

This message was moderated.

[Former Member]

Thanks for the solution.Until couple of rows the results look good and after that i get #OVERFLOW error for that specific column.

help me!

[Former Member]

This message was moderated.

[former_member183424]

Dear

Posting only links is not a good notion. Have a look into the blog post and read all related comment . Instead of posting only links, please encourage people to search for these links.

[Former Member]

This solution got an #OVERFLOW and there seems to have no way to get rid of this!

Is there anyone having another solution to achieve this?

Thanks.