Cross Tab: record sort order and new page

[TechiUser]

Hi,

I am using CR XI. I have a table containing data like

Date Amount
Jan 1990 5
Feb 1990 2
Mar 1990 3
.... ....
Jan 2006 6

The requirement is to create a triangle report like

Jan1990 Feb1990 Mar1990. .. Jan 2006
Jan1990 5 F(5,2) F(5,2,3) F(5,2,3,..,6)Feb1990 2 F(2,3) F(2,3,..,6)
Mar1990 3 F(3,..,6)
.... .....
Jan2006 6

Where F() is a function created as formula field in CR. To get the correct results from the formula, records have to be read from database in the order of dates.

I perform a self join on the table, with the condition that {table_row.date} <= {table_column.date}. My result set from the database (not in the same order) is

Dt_row Dt_clmn Amount
Jan1990 Jan1990 5
Jan1990 Feb1990 2
Jan1990 Mar1990 3
...
Jan1990 Jan2006 6
Feb1990 Feb1990 2
Feb1990 Mar1990 3
...
Feb1990 Jan2006 6
Mar1990 Mar1990 3
...
Mar1990 Jan2006 6
..
..
Jan2006 Jan2006 6

I then created a cross-tab, with Dt_row as the row value and Dt_clmn as column value. Apply formula on the amount and use it as summary field having Max as the summary.

I also created a dummy page group, inorder to break the cross-tab and display only as many columns on a single page as can fit.(found the solution to do this in the forum). It all worked like a dream, but I now have two problems

1. To get correct results from F(), the records have to be ordered by Dt_row then Dt_clmn. But since I have to create a pagegroup, CR automatically creates the first sort by the pagegroup.

2. The formula used in pagegroup is:
(if {table_column.DATE} in {?@ReportPeriodStartDate} to dateadd("m",24,{?@ReportPeriodStartDate}-1) then
1
else if {table_column.DATE} in dateadd("m",25,{?@ReportPeriodStartDate}) to dateadd("m",48,{?@ReportPeriodStartDate}-1) then
2
else if {table_column.DATE} in dateadd("m",49,{?@ReportPeriodStartDate}) to dateadd("m",72,{?@ReportPeriodStartDate}-1) then
3)

It is possible that not each month exist in the table, in that case I endup with fewer number of column on some pages (it can be as low as just two columns!)

I am unable to get around the above two issues.

My first question is, is it possible to fulfill these requirements in CR? If yes, then any help will be much appreciated.

 

[TechiUser]

Hi,

I solved my problem last week... a very simple solution provided by Crystal Reports. I wanted to share it with other users incase someone else encounters the same problem.

I had created the fake pagegroup to work around the 'Virtual or horizontal pages' created by Cross tab. In the Format editor for each object there is an option of 'Repeat on horizontal pages'. By selecting this option for each object in the page excepting the crosstab, I can get the page header to be printed on each virtual/ horizontal page. Great!

Then by selecting the 'Repeat Row Labels' in the Cross-Tab Expert, I get the row labels also repeated on each virtual/ horizontal page. Problem solved! No need to create a fake pagegroup and I can have the records in whatever order I wanted them.

The Article c2013892: "How to use the 'Repeat on Horizontal Pages' option in Crystal Reports 10" on the business objects website showed me the way.

Also there is a FAQ in this forum on rolling over the cross tab columns that should be updated.

Thanks