Dynamic columns vs Using Calculations in Crosstabs

[scb1776]

Hi
I am currently using Crystal XI

I am having problems trying to format my data correctly.
Ultimately I need the reprot to look like this:

start date - Feb 1 2008
end date - Nov 10 2008
month interval - 2

2M 4M 6M 8M Current
Home1 x1 x2 x3 x4 x5
Home2 y1 y2 y3 y4 y5
Home3 z1 z2 NA z4 z5
.
.
Average a1 a2 a3 a4 a5


In this case Current is the end date, 8M is current - 2 months Sept 10 2008, the date buckets calculate backwards until you hit or pass the start date. How do I put a calculated value for the header?

The values in the crosstab must have a date that equals the date of the date bucket for that column. The rows have many additional records for dates that should not be displayed. How do I filter to get the correct column data?

If a row does not have a value for a particular date bucket then that bucket should display the words "Not Available". How do I force a string to be used for a blank value?

The Average row needs to be added to the bottom of the crosstab. The value should be the average of all entries in the column that contain data. I do not want to use 0 for the entries that say Not Available, I want those values left out of the equation altogether.


To do this without a crosstab I can determine how to use arrays to create the data, however I do not know how I would print the information since I would require a dynamic number of columns to be printed.

The number of columns required can be known as soon as the user enters the parameters, however there is no restriction on what dates can be entered for the start date and end date (other than that the start date must be before the end date) so I could have 1 column or 30 columns depending upon what they want to see.

An additional issue is that, per company guidelines, I cannot use command objects. (in case that matters)

Any suggestions would be greatly appreciated.

 

[lbass]

Create a formula {@Intervals} and use it as your column field in an inserted crosstab:

numbervar i;
numbervar j := -int(-datediff("m",{?start},{?end})/2);
stringvar y;

for i := 1 to j do (
if {{table.date} in dateadd("m",-i*2,{?end})+1 to dateadd("m",(-i*2)+2,{?end}) then
y := totext(i*2-2,"00")+" to "+totext(i*2,"00")+ " Months Before"
);
y

Use {table.home} as the row field, and then add the sum of {table.amt} as your summary field.

Go to report->selection formula->record and enter:

{Table.date} in {?start} to {?end}

In the crosstab right click on the summary in an inner cell->Format field->suppress->x+2 and enter:

whileprintingrecords;
currencyvar x := x + currentfieldvalue;
numbervar cnt;
if currentfieldvalue <> 0 then
cnt := cnt + 1;
false

Then in the same common tab->display string->x+2 and enter:

if currentfieldvalue = 0 then
"Not Available" else
totext(currentfieldvalue,2)//2 for two decimals

Next select the column total->right click->format field->display string->X+2 and enter:

whileprintingrecords;
currencyvar x;
numbervar cnt;
if cnt > 0 then
totext(x/cnt,2)//2 for two decimals

Then with the same total selected, go to the border tab->color->background->x+2 and enter:

whileprintingrecords;
currencyvar x := 0;
numbervar cnt := 0;
crnocolor

This will not, however, result in a row for the home field if there no values in any interval for that home and it won't show a specific interval if there are no homes with values in that interval.

-LB