Cross Tab and Dates 2

[idehen]

Hello,

Need some help please.

I am using CR 10 and not very familiar with cross tab, however i know it should help with this problem.

Problem:
I have a table called participant_awards (PA) and a second called award_dates(AD).

Each PA is linked to one or more dates in the AD table.

I want to display for each PA (unique ID) the number list of dates associated with them. But i want this created at the same row as the unique IDs.

Any ideas please cos i tried this and had the dates at the top column of the cross tab and total numbers for each unique ID. What i rather want is something like

12345 : 01/01/2005 : 01/03/2008 : 02/7/2008
67845 : 01/03/2006 : 01/05/2007 : 02/10/2007

Hope this makes sense.

Thanks people

 

[idehen]

Any advise please or is this imposible to do.

 

[IanWaterman]

Group report by your unique Id. Place date field in details
Go to format section on details, near bottom you will see format with multipl columns. Check that.

New tab layout will appear, set up as required.
By trial and error you should be able to get layout as you want.

Ian

 

[idehen]

Many thanks Ian.

As instructed i have placed the date in the details and grouped the report by Unique id and checked the Multiple columns option in section expert.

My report is showingfor example

Unique Id date
123456 31/05/2007
123456 31/05/2008
123456 31/05/2009

I'd rather it was showing as

Unique id date 1 date 2 date 3
123456 31/05/2007 31/05/2008 31/05/2009

Any advise will be appreciated.

 

[IanWaterman]

In the layout tab did you select Horizontal?

 

[idehen]

I am not sure where to find this option as it's not there in my layout.

 

[lbass]

The layout tab is in the upper right of the section expert after you highlight details and check "format with multiple columns".

An alternative would be to collect the dates in a string. Insert a group on unique ID and then create three formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + totext({table.date},"dd/MM/yyyy")+ " ";

//{@display} to be placed in the group footer to the right of the unique ID:
whileprintingrecords;
stringvar x;
trim(x)

-LB

 

[idehen]

Ian and Ibass thank you loads for your help. I tried your formula Ibass and it works perfect!

Ian, as you suggested, play about with the layout did get me very close to what i wanted,however i have also found other use for the advise you provided.

I have given you both a star as you have no idea how much help you just provided.

Appreciate it guys.