Display multi row values in a single row...

[k2]

Hi All,

I have a peculiar request. wondering how to display the below data in one single row, where as Cust ID is duplicate.

Cust ID   Cust Name
007          James
007          Bond

Proposed solution:
Cust ID   Cust Name
007          James Bond

SQL Server is the data base which FM is pointing to. Any ideas would be highly appreciated.

Cheers

[MFGF]

Hi All,

I have a peculiar request. wondering how to display the below data in one single row, where as Cust ID is duplicate.

Cust ID   Cust Name
007          James
007          Bond

Proposed solution:
Cust ID   Cust Name
007          James Bond

SQL Server is the data base which FM is pointing to. Any ideas would be highly appreciated.

Cheers

Sounds like a perfect use-case for a repeater object.

Within your list, drag in Cust ID as a column. Then from the toolbox, pick up a Repeater object and drag this into the list as the second column. Change the repeater Query property so it uses the same query as the list. Drag Cust Name into the repeater, then drag a text item from the toolbox into the repeater and drop it after Cust Name. Type a space into the text item.

Cheers!

MF.

[k2]

Thanks for the prompt response MF. Unfortunately, I cannot use repeater in my case as I have to use the Cust Name field to create a join in Report Studio to fetch data from various queries.

I need the concatenated Cust Name field in my report query. Hope this explains.

Cheers

[BigChris]

Hi K2 - are there always going to be two records for a [Cust Id]? And how will you determine which name comes first? There's nothing in your data that would stop you ending up with Bond James for example.

[MFGF]

Thanks for the prompt response MF. Unfortunately, I cannot use repeater in my case as I have to use the Cust Name field to create a join in Report Studio to fetch data from various queries.

I need the concatenated Cust Name field in my report query. Hope this explains.

Cheers

Hi,

I'm not sure I see how using Cust Name as a join item precludes you from using it in a repeater? Your list is based on a single query ultimately, even if other queries are joined to feed data items into it. Or are you saying you need to use the concatenated value as a join item? If so, you have a major problem in your data here...

MF.

[k2]

Hi Chris,

A Cust ID have one or more than two Cust Name. Order of displaying the Cust Name can be handled by Line number field. That shouldn't be an issue.

I need to display all Cust Name's for the corresponding Cust ID.

[BigChris]

Ok - I can think of a way that you might do it with two names, but not if you've got more than two...sorry.

[MFGF]

Can you advise on the requirement here. Are you saying you need to use the concatenated value to perform a join? Can't you use the Cust ID to join?

MF.

[k2]

It is one of the weird requirement, as result of unclean/friendly data. Cust ID cannot be used to create a join as it is not available in the other side of the query :(

No worries. Luckily the DB team managed to fix this by writing a Stored Proc in the data base. which will be used in the report.

Thanks for your thoughts guys. Much appreciated :)

Cheers