Cross Tab Help

[shobia]

Hi,
I am using CR 9. I would like my cross tab to appear in the below format

Name ID AUGUST SEPTEMBER OCTOBER NOVEMBER

JOHN 34 17,18,19 1,5,6 1
7,8,9 2 4

MATHEW 37 1 7,8,9 3

TOM 23 2


I have Name and ID and days as rows and the months as columns. But the days which is (3,4,5 etc), i do not want to do a summarised field which forces me to do it like (count,mode,avg and on). How do i display the days without doing a summarised column. I some how manipulated it to do using 'mode of', which doesn't work quite right. I get a display like below which orders by days.

Name ID AUGUST SEPTEMBER OCTOBER NOVEMBER

JOHN 34 1
17,18,19
1,5,6

2
4

MATHEW 37 1 7,8,9
3

TOM 23 2

It's pretty much like a single value in a row. I would like to display the values as such without doing any summarised field and display it in the order which it was retrieved. Any help is really appreciated.

Thanks in Advance
Shobia

 

[WarrenRoss]

There is no way that I can think of to do what you want using a cross-tab because when using cross-tabs you are effectively grouping your data and are forced to use summaries of some sort.

The report is fairly easy to create, however, using regular old groups on "name" and "month" (date). You'll need a few formulae:

{@AddDays} - for storing up the different days per month. This goes in the details section and would look something like:
whileprintingrecords;
global stringvar sDays := sDays + totext(day(YOURDATEFIELD),0) + ", "

{@ResetDays} - for resetting the above formula for each month group. This goes in group header 2 (month group):
whileprintingrecords;
global stringvar sDays := '';

{@DisplayDays} - for displaying the final collection of days for that month. This goes in group footer 2 (month group):
whileprintingrecords;
global stringvar sDays;
//Add following line to get rid of the last comma
left(sDays, length(sDays) - 1);


Once your formulae are placed, you will be able to suppress the details section, move the group 2 (month group) name field from group header into the group footer (to line up nicely with your DisplayDays formula), and then suppress group header 2.

This should present a reasonable looking summary of the sort of data you've got, although it won't look much like the cross-tab you're trying to create. With a bit more work, you could create a "manual" sort of cross-tab using the same idea above.

 

[shobia]

WarrenRoss,

Thanks for your reply!
I realised that i would not be able to do a cross tab. So i followed your directions and acheived it. Not exact but similar. Thanks For your valuable help.

-Shobia